Re: Strangely Variable Query Performance

От: Tom Lane
Тема: Re: Strangely Variable Query Performance
Дата: ,
Msg-id: 10355.1176422445@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Strangely Variable Query Performance  (Steve)
Ответы: Re: Strangely Variable Query Performance  (Steve)
Список: pgsql-performance

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

Slow Postgresql server  (Jason Lustig, )
 Re: Slow Postgresql server  (Dennis Bjorklund, )
 Re: Slow Postgresql server  (Jeff Frost, )
 Re: Slow Postgresql server  (Ron, )
  Re: Slow Postgresql server  (Guido Neitzer, )
   Re: Slow Postgresql server  (Ron, )
    Re: Slow Postgresql server  (Guido Neitzer, )
     Re: Slow Postgresql server  (Scott Marlowe, )
      Re: Slow Postgresql server  (Jeff Frost, )
       Re: Slow Postgresql server  (Carlos Moreno, )
       Strangely Variable Query Performance  (Steve, )
        Re: Strangely Variable Query Performance  (Tom Lane, )
         Re: Strangely Variable Query Performance  (Steve, )
          Re: Strangely Variable Query Performance  (Tom Lane, )
           Re: Strangely Variable Query Performance  (Steve, )
            Re: Strangely Variable Query Performance  (Tom Lane, )
             Re: Strangely Variable Query Performance  (Steve, )
        Re: Strangely Variable Query Performance  (Scott Marlowe, )
         Re: Strangely Variable Query Performance  (Steve, )
          Re: Strangely Variable Query Performance  (Scott Marlowe, )
           Re: Strangely Variable Query Performance  (Steve, )
           Re: Strangely Variable Query Performance  (Tom Lane, )
            Re: Strangely Variable Query Performance  (Steve, )
             Re: Strangely Variable Query Performance  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
             Re: Strangely Variable Query Performance  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
               Re: Strangely Variable Query Performance  (Tom Lane, )
                Re: Strangely Variable Query Performance  (Steve, )
                 Re: Strangely Variable Query Performance  (Tom Lane, )
                  Re: Strangely Variable Query Performance  (Steve, )
                   Re: Strangely Variable Query Performance  (Tom Lane, )
                   Re: Strangely Variable Query Performance  (Tom Lane, )
                    Re: Strangely Variable Query Performance  (Steve, )
                     Re: Strangely Variable Query Performance  (Tom Lane, )
                      Fwd: Strangely Variable Query Performance  ("Robins Tharakan", )
                     choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
                      Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Alvaro Herrera, )
                       Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
                       Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
              Re: Strangely Variable Query Performance  (Steve, )
             Re: [HACKERS] choose_bitmap_and again (was Re: Strangely Variable Query Performance)  (Tom Lane, )
 Re: Slow Postgresql server  (Jeff Frost, )
  Re: Slow Postgresql server  (Jason Lustig, )
   Re: Slow Postgresql server  (Guido Neitzer, )
 Fwd: Strangely Variable Query Performance  (Robins, )
  Re: Fwd: Strangely Variable Query Performance  (Tom Lane, )

Steve <> writes:
> With enable_seqscan=off I get:

>     ->  Bitmap Index Scan on detail_summary_receipt_encounter_idx
> (cost=0.00..4211395.17 rows=1099 width=0)
>           Index Cond: ((receipt >= '1998-12-30'::date) AND (encounter_id =
> ANY ...

> The explain analyze is pending, running it now (however it doens't really
> appear to be any faster using this plan).

Yeah, that index is nearly useless for this query --- since the receipt
condition isn't really eliminating anything, it'll have to look at every
index entry :-( ... in fact, do so again for each of the IN arms :-( :-(
So it's definitely right not to want to use that plan.  Question is, why
is it seemingly failing to consider the "right" index?

I'm busy setting up my test case on an x86_64 machine right now, but
I rather fear it'll still work just fine for me.  Have you got any
nondefault parameter settings besides the ones you already mentioned?

Another thing that might be interesting, if you haven't got a problem
with exclusive-locking the table for a little bit, is

    BEGIN;
    DROP INDEX each index except detail_summary_encounter_id_idx
    EXPLAIN the problem query
    ROLLBACK;

just to see if it does the right thing when it's not distracted by
all the "wrong" indexes (yeah, I'm grasping at straws here).  If you
set up the above as a SQL script it should only take a second to run.
Please try this with both settings of enable_seqscan --- you don't need
to do "explain analyze" though, we just want to know which plan it picks
and what the cost estimate is.

            regards, tom lane


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

От: Tom Lane
Дата:
Сообщение: Re: Strangely Variable Query Performance
От: Andrew McMillan
Дата:
Сообщение: Re: Question about memory allocations