Re: Performance on inserts

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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