Re: Possible to improve query plan?

От: Kevin Grittner
Тема: Re: Possible to improve query plan?
Дата: ,
Msg-id: 4D341E6802000025000396AF@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: Possible to improve query plan?  (Jeremy Palmer)
Ответы: Re: Possible to improve query plan?  (Robert Haas)
Список: pgsql-performance

Скрыть дерево обсуждения

Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  (Andy Colson, )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Ing. Marcos Ortiz Valmaseda", )
    Re: Possible to improve query plan?  (Jeremy Palmer, )
   Re: Possible to improve query plan?  ("Kevin Grittner", )
    Re: Possible to improve query plan?  (Robert Haas, )
     Re: Possible to improve query plan?  ("Kevin Grittner", )
      Re: Possible to improve query plan?  (Tom Lane, )
       Re: Possible to improve query plan?  ("Kevin Grittner", )
        Re: Possible to improve query plan?  (Jeremy Palmer, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Cédric Villemain, )
         Re: Possible to improve query plan?  ("Kevin Grittner", )
          Re: Possible to improve query plan?  (Jeremy Palmer, )
     Re: Possible to improve query plan?  (Tom Lane, )

Jeremy Palmer  wrote:

>   WHERE (
>       (_revision_created <= 16
>        AND _revision_expired > 16
>        AND _revision_expired <= 40)
>    OR (_revision_created > 16
>        AND _revision_created <= 40))

> -> Bitmap Heap Scan on version_crs_coordinate_revision
>      (actual time=70.925..13531.720 rows=149557 loops=1)

> -> BitmapOr (actual time=53.650..53.650 rows=0 loops=1)

This plan actually looks pretty good for what you're doing.  The
Bitmap Index Scans and BitmapOr determine which tuples in the heap
need to be visited.  The Bitmap Heap Scan then visits the heap pages
in physical order (to avoid repeated fetches of the same page and to
possibly edge toward sequential access speeds).  You don't seem to
have a lot of bloat, which could be a killer on this type of query,
since the rowcounts from the index scans aren't that much higher than
the counts after you check the heap.

The only thing I can think of which might help is to CLUSTER the
table on whichever of the two indexes used in the plan which is
typically more selective for such queries.  (In the example query
that seems to be idx_crs_coordinate_revision_created.)  That might
reduce the number of heap pages which need to be accessed and/or put
place them close enough that you'll get some sequential readahead.

I guess you could also try adjusting effective_io_concurrency upward
to see if that helps.

-Kevin


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

От: Mladen Gogala
Дата:
Сообщение: Re: Possible to improve query plan?
От: Fernando Hevia
Дата:
Сообщение: Re: Problem with query