Re: Performance on inserts

Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>>> * Prevent index lookups (or index entries using partial index) on most
>>>> common values; instead use sequential scan 
>> 
>> This behavior already exists for the most common value, and would
>> exist for any additional values that we had stats for.  Don't see
>> why you think a separate TODO item is needed.

> You mean the optimizer already skips an index lookup for the most common
> value, and instead does a sequential scan?

No, it goes for the sequential scan if it estimates the cost of the
indexscan as more than sequential.  Indexscan cost depends on estimated
number of retrieved rows --- which it can estimate from pg_statistic
if the query is WHERE column = mostcommonvalue.  So which plan you get
depends on just how common the most common value is.

Hard-wiring either choice of plan for the most common value would be
inferior to what the code already does, AFAICS.  But for values other
than the-most-common, we don't have adequate stats in pg_statistic,
and so you may or may not get a good estimated row count and hence
a good choice of plan.  That's what needs to be fixed.
        regards, tom lane