Re: Strangely Variable Query Performance

From: Steve
Subject: Re: Strangely Variable Query Performance
Date: ,
Msg-id: Pine.GSO.4.64.0704122025450.17955@kittyhawk.tanabi.org
(view: Whole thread, Raw)
In response to: Re: Strangely Variable Query Performance  (Tom Lane)
Responses: Re: Strangely Variable Query Performance  (Tom Lane)
Re: Strangely Variable Query Performance  (Tom Lane)
List: pgsql-performance

Tree view

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, )

Okay -- I started leaving indexes on one by one.

The explain broke when the detail_summary_receipt_encounter_idx index was
left on (receipt, encounter_id).

Just dropping that index had no effect, but there's a LOT of indexes that
refer to receipt.  So on a hunch I tried dropping all indexes that refer
to receipt date and that worked -- so it's the indexes that contain
receipt date that are teh problem.

For more fun, I tried leaving the index that's just receipt date alone
(index detail_summary_receipt_id_idx) and THAT produced the correct query;
it's all these multi-column queries that are fouling things up, it would
seem!


.... So does this mean I should experiment with dropping those indexes?
I'm not sure if that will result in 'bad things' as there are other
complicated actions like sorts that may go real slow if I drop those
indexes.  BUT I think it'll be easy to convince the customer to drop their
absurdly complicated sorts if I can come back with serious results like
what we've worked out here.


And thanks again -- have a good dinner! :)

Steve


On Thu, 12 Apr 2007, Tom Lane wrote:

> Steve <> writes:
>> Either way, it runs perfectly fast.  So it looks like the indexes are
>> confusing this query like you suspected.  Any advise?
>
> Wow --- sometimes grasping at straws pays off.  I was testing here with
> just a subset of the indexes to save build time, but I bet that one of
> the "irrelevant" ones is affecting this somehow.  Time to re-test.
>
> If you have some time to kill, it might be interesting to vary that
> begin/rollback test script to leave one or two other indexes in place,
> and see if you can identify exactly which other index(es) get it
> confused.
>
> I'm about to go out to dinner with the wife, but will have a closer
> look when I get back, or tomorrow morning.  We'll figure this out.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                http://www.postgresql.org/about/donate
>


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: Strangely Variable Query Performance
From: Andrew McMillan
Date:
Subject: Re: Question about memory allocations