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.

OK, I remember now. If the most common value is used as a constant, it
uses the value from pg_statistic for most common, rather than use
the dispersion value. That is great.

What I am more concerned about is a join that uses the most common
value. We do an index scan in that case. I wonder of we could get
something into the executor that would switch to sequential scan when
the most common value is hit. Is that worth persuing?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026