Re: seq scan instead of index scan

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