Re: Weird issue with planner choosing seq scan

On Sun, 24 Feb 2008, Tom Lane wrote:
> Sean Leach <sleach(at)wiggum(dot)com> 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