Re: Planner doesn't chose Index - (slow select)

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Planner doesn't chose Index - (slow select)
Дата
Msg-id 20957.1145413184@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Planner doesn't chose Index - (slow select)  ("patrick keshishian" <pkeshish@gmail.com>)
Ответы Re: Planner doesn't chose Index - (slow select)  ("patrick keshishian" <pkeshish@gmail.com>)
Список pgsql-performance
"patrick keshishian" <pkeshish@gmail.com> writes:
> I've been struggling with some performance issues with certain
> SQL queries.  I was prepping a long-ish overview of my problem
> to submit, but I think I'll start out with a simple case of the
> problem first, hopefully answers I receive will help me solve
> my initial issue.

Have you ANALYZEd this table lately?

> db=# select count(*) from pk_c2 b0 where b0.offer_id=7141;
>  count
> -------
>      1
> (1 row)

The planner is evidently estimating that there are 12109 such rows,
not 1, which is the reason for its reluctance to use an indexscan.
Generally the only reason for it to be off that far on such a simple
statistical issue is if you haven't updated the stats in a long time.
(If you've got a really skewed data distribution for offer_id, you
might need to raise the statistics target for it.)

> The table has indexes for both 'offer_id' and '(pending=true)':

> Indexes:
>     "pk_boidx" btree (offer_id)
>     "pk_bpidx" btree (((pending = true)))

The expression index on (pending = true) won't do you any good,
unless you spell your query in a weird way like
    ... WHERE (pending = true) = true
I'd suggest a plain index on "pending" instead.

> db=# select version();
>  PostgreSQL 7.4.12 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.6

You might want to think about an update, too.  7.4 is pretty long in the
tooth.

            regards, tom lane

В списке pgsql-performance по дате отправления:

Предыдущее
От: "patrick keshishian"
Дата:
Сообщение: Planner doesn't chose Index - (slow select)
Следующее
От: Christopher Kings-Lynne
Дата:
Сообщение: Re: SELECT FOR UPDATE performance is bad