Re: seq scan instead of index scan

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.

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com