Re: seq scan instead of index scan

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux