On Fri, Sep 26, 2014 at 11:02 AM, <marko@joh.to> wrote:
> The statistics say that
> there are no rows where processed=0 (and it's not far from the truth), but
> it's still a risky plan compared to the PK lookup.
> Any thoughts?
PostgreSQL 9.0 introduced this optimization for greater/less operators:
> When looking up statistics for greater/less-than comparisons, if the
> comparison value is in the first or last histogram bucket, use an index
> (if available) to fetch the current actual column minimum or maximum.
> This greatly improves the accuracy of estimates for comparison values
> near the ends of the data range, particularly if the range is constantly
> changing due to addition of new data.
Not sure whether it's a good idea a bad idea, but perhaps a solution
is to expand this to equality lookups too?
Does using "WHERE processed <= 0" work around the problem? (Assuming
you don't have any negative numbers in this column).
> The index
> index_events_processed is an index on events(processed), which should
> probably be a partial index on WHERE processed = 0, but I thought I'd
> report this plan anyway.
I guess you would still have this problem, unless your new index
contains the eventid column.
Regards,
Marti