On Thu, Dec 17, 2009 at 6:10 PM, Karl Larsson <karl.larsson47@xxxxxxxxx> wrote: > On Fri, Dec 18, 2009 at 1:10 AM, Greg Smith <greg@xxxxxxxxxxxxxxx> 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. > > 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. Run it with explain analyze on the real table / SQL query and if it doesn't run well, post it here. Note you can do a lot to tune the query planner, with things like random_page_cost, cpu_* cost parameters, effective_cache_size and so on. For troubleshooting purposes you can use set enable_method=off where method can be things like indexscan, nestloop, and so on. Use show all to see them. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
