On Fri, Dec 18, 2009 at 1:10 AM, Greg Smith <[email protected]> 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
