Re: Index scan plan estimates way off.

Список
Период
Сортировка
От Tom Lane
Тема Re: Index scan plan estimates way off.
Дата
Msg-id 8053.1236277851@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Index scan plan estimates way off.  (Jonathan Hseu)
Ответы Re: Index scan plan estimates way off.  (Jonathan Hseu)
Re: Index scan plan estimates way off.  (Robert Haas)
Список pgsql-performance
Дерево обсуждения
Index scan plan estimates way off.  (Jonathan Hseu, )
 Re: Index scan plan estimates way off.  (Tom Lane, )
  Re: Index scan plan estimates way off.  (Jonathan Hseu, )
  Re: Index scan plan estimates way off.  (Robert Haas, )
Jonathan Hseu <> writes:
>  Sort  (cost=11684028.44..11761274.94 rows=30898601 width=40)
>    Sort Key: "time"
>    ->  Bitmap Heap Scan on ticks  (cost=715657.57..6995196.08 rows=30898601
> width=40)
>          Recheck Cond: (contract_id = 1)
>          ->  Bitmap Index Scan on contract_id_time_idx
> (cost=0.00..707932.92 rows=30898601 width=0)
>                Index Cond: (contract_id = 1)
> (6 rows)

> This plan doesn't complete in a reasonable amount of time.  I end up having
> to kill the query after it's been running for over an hour.

The bitmap scan should be at least as efficient as the plain indexscan,
so I suppose the problem is that the sort is slow.  What's the datatype
of "time"?  Can this machine actually support 256MB+ work_mem, or is that
likely to be driving it into swapping?

You might learn more from enabling trace_sort and watching the
postmaster log entries it generates.  On the whole I think the planner
isn't making a stupid choice here: sorting a large number of rows
usually *is* preferable to making an indexscan over them, unless the
table is remarkably close to being in physical order for the index.
So it would be worth trying to figure out what the problem with the
sort is.

            regards, tom lane

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

Предыдущее
От: Aaron Guyon
Дата:
Сообщение: Re: Postgres 8.3, four times slower queries?
Следующее
От: Jonathan Hseu
Дата:
Сообщение: Re: Index scan plan estimates way off.