Re: Weird issue with planner choosing seq scan

On Sun, 24 Feb 2008, Tom Lane wrote:
> Sean Leach  writes:
>> I have a table, that in production, currently has a little over 3
>> million records in production.  In development, the same table has
>> about 10 million records (we have cleaned production a few weeks
>> ago).
>
> You mean the other way around, to judge by the rowcounts from EXPLAIN.
>
>>           ->  Index Scan using u_counts_i2 on u_counts c
>> (cost=0.00..53.53 rows=1082 width=4) (actual time=0.277..1224.582
>> rows=392173 loops=1)
>
> I kinda think the devel system wouldn't be using an indexscan either
> if it had up-to-date ANALYZE statistics.  But even with the 1082 row
> estimate that seems a remarkably low cost estimate.

Seems pretty obvious to me. The table is obviously going to be well 
ordered by the timestamp, if that's the time that the entries are inserted 
into the table. So the index is going to have a very good correlation with 
the order of the table, which is why the estimated cost for the index scan 
is so low. The production table will be more active than the development 
table, so the entries in it will be more recent. The entries that were 
cleaned out a while ago are all irrelevant, because they will be old ones, 
and we are specifically searching for new entries. Because the production 
table is more active, even though it is smaller, the results of the search 
will be bigger (as seen in the explain analyse results), pushing it over 
the limit and making a sequential scan more attractive.

Matthew

-- 
Failure is not an option. It comes bundled with your Microsoft product. 
                                                 -- Ferenc Mantfeld