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(at)2ndQuadrant(dot)com www.2ndQuadrant.com