Re: plan question - query with order by and limit not choosing index depends on size of limit, table

Mike Broers <mbroers@gmail.com> wrote:

> Hello performance, I need help explaining the performance of a
> particular query

You provided some of the information needed, but you should review
this page and post a bit more:

http://wiki.postgresql.org/wiki/SlowQueryQuestions

In particular, post the result of EXPLAIN ANALYZE, not just EXPLAIN.
Also, showing all overrides in your postgresql.conf file is
important, and some information about your hardware.  How big is the
active portion of your database (the frequently read portion)?

> Why does the smaller limit cause it to skip the index?

Because the optimizer thinks the query will return rows sooner that
way.

> Is there a way to help the planner choose the better plan?

You might get there by adjusting your memory settings and/or costing
settings, but we need to see more information to know that.

-Kevin