Re: seq scan instead of index scan

On Thu, Dec 17, 2009 at 6:10 PM, Karl Larsson <karl(dot)larsson47(at)gmail(dot)com> wrote:
> On Fri, Dec 18, 2009 at 1:10 AM, Greg Smith <greg(at)2ndquadrant(dot)com> 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.