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@xxxxxxxxxxxxxxx www.2ndQuadrant.com
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
