Re: Re: understanding why two nearly identical queries take two different planner routes, one 5s and one 2hr

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Re: understanding why two nearly identical queries take two different planner routes, one 5s and one 2hr
Дата
Msg-id CAMkU=1zmbs9YV+8fn0Jag4hgrJGmGpyPGnkeSTspa4j-mEYwXQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: understanding why two nearly identical queries take two different planner routes, one 5s and one 2hr  (David G Johnston <david.g.johnston@gmail.com>)
Ответы Re: Re: understanding why two nearly identical queries take two different planner routes, one 5s and one 2hr  (David Johnston <david.g.johnston@gmail.com>)
Список pgsql-general
Дерево обсуждения
On Tue, Aug 5, 2014 at 4:30 PM, David G Johnston <david.g.johnston@gmail.com> wrote:

NOTE: I am confused by this line:
->  BitmapAnd  (cost=291564.31..291564.31 rows=28273 width=0) (actual
time=23843.870..23843.870 rows=0 loops=1)

How did actual match zero rows?  It should be something like 2.2M

The accounting for bitmap operations seems to be a bit of a mess.  In some cases, it reports the number of rows represented in the bitmap.  Sometimes it counts a bitmap itself as a row, and so there is just one of them no matter how many rows it represents.  In this case, it seems to consider a bitmap not to be a row at all.   The problem with counting the number of rows represented by the bitmap is that that value is unknown if either if the input bitmaps has gone lossy.



Anyway, you should probably experiment with creating a multi-column index
instead of allowing PostgreSQL to BitmapAnd them together.  Likely the
timestamp will have higher cardinality and so should be listed first in the
index.  

No, the timestamp should almost certainly come second because it is used with inequality operators.

Cheers,

Jeff

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

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: understanding why two nearly identical queries take two different planner routes, one 5s and one 2hr
Следующее
От: Ray Stell
Дата:
Сообщение: Re: Questions on dynamic execution and sqlca