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

От: Kevin Grittner
Тема: Re: plan question - query with order by and limit not choosing index depends on size of limit, table
Дата: ,
Msg-id: 4D24A60C020000250003901A@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: plan question - query with order by and limit not choosing index depends on size of limit, table  (Mike Broers)
Ответы: Re: plan question - query with order by and limit not choosing index depends on size of limit, table  (Mike Broers)
Список: pgsql-performance


Mike Broers <> 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


В списке pgsql-performance по дате сообщения:

От: "Kevin Grittner"
Дата:
Сообщение: Re: plan question - query with order by and limit not choosing index depends on size of limit, table
От: Josh Berkus
Дата:
Сообщение: Re: Wrong docs on wal_buffers?