From: | Karl Larsson <karl(dot)larsson47(at)gmail(dot)com> |
---|---|
To: | Greg Smith <greg(at)2ndquadrant(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: seq scan instead of index scan |
Date: | 2009-12-18 01:10:32 |
Message-ID: | d7650d320912171710u702236d8ib9ef222477222485@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Fri, Dec 18, 2009 at 1:10 AM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Karl Larsson wrote:
>
>> When I make a subquery Postgres don't care about my indexes and makes
>> a seq scan instead of a index scan. Why?
>>
> Data set is just too small for it to matter. Watch what happens if I
> continue from what you posted with much bigger tables:
>
> postgres=# truncate table table_one;
> TRUNCATE TABLE
> postgres=# truncate table table_two;
> TRUNCATE TABLE
> postgres=# insert into table_one (select generate_series(1,100000));
> INSERT 0 100000
> postgres=# insert into table_two (select generate_series(1,100000));
> INSERT 0 100000
> postgres=# analyze;
> ANALYZE
> postgres=# EXPLAIN ANALYZE
>
> SELECT t2.id
> FROM table_two AS t2, (
> SELECT id
> FROM table_one AS t1
> WHERE t1.id < 6
> ) AS foo
> WHERE t2.id = foo.id;
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.00..91.35 rows=10 width=8) (actual time=0.024..0.048
> rows=5 loops=1)
> -> Index Scan using table_one_pkey on table_one t1 (cost=0.00..8.44
> rows=10 width=8) (actual time=0.009..0.013 rows=5 loops=1)
> Index Cond: (id < 6)
> -> Index Scan using table_two_pkey on table_two t2 (cost=0.00..8.28
> rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=5)
> Index Cond: (t2.id = t1.id)
> Total runtime: 0.097 ms
> (6 rows)
>
> There's the index scan on both tables that you were expecting.<http://www.2ndQuadrant.com>
>
True. Thank you. I'll try this on my reel problem as well but I have a gut
feeling it
won't work there since those tables are bigger.
/ Karl Larsson
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2009-12-18 01:16:47 | Re: seq scan instead of index scan |
Previous Message | Scott Marlowe | 2009-12-18 00:11:19 | Re: seq scan instead of index scan |