Re: query against large table not using sensible index to find very small amount of data

"Andrew W. Gibbs" <awgibbs@awgibbs.com> writes:
> A very common query against this table is of the form...

> SELECT * FROM events WHERE entity_type_id = XXX ORDER BY published_at DESC LIMIT 25;

> ... to get the most recent 25 events from the table for a given type
> of entity, and generally the query planner does the expected thing of
> using the two-part index on (entity_type_id, published_at).  Every now
> and again, though, I have found the query planner deciding that it
> ought use the single column (published_at) index.

What is the estimated rows count according to EXPLAIN when it does that,
versus when it chooses the better plan?

> FWIW, we're running on 8.4.X and using the out-of-the-box
> default_statistics_target setting and haven't dabbled with setting
> table level statistics configurations.

8.4.X is due to reach EOL in July, so you really ought to be thinking
about an upgrade.  It's not clear from the given info whether this issue
is fixable with stats configuration adjustments, is a bug already fixed
in later versions, or neither, but we're unlikely to make any significant
changes in the 8.4 planner code at this point...

            regards, tom lane