From: | Steve <cheetah(at)tanabi(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Strangely Variable Query Performance |
Date: | 2007-04-13 00:34:18 |
Message-ID: | Pine.GSO.4.64.0704122025450.17955@kittyhawk.tanabi.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches pgsql-performance |
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 <cheetah(at)tanabi(dot)org> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2007-04-13 00:39:31 | Re: What tools do people use to hack on PostgreSQL? |
Previous Message | Florian G. Pflug | 2007-04-13 00:30:30 | Re: What tools do people use to hack on PostgreSQL? |
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2007-04-13 00:58:03 | Re: Minor changes to Recovery related code |
Previous Message | Tom Lane | 2007-04-13 00:20:06 | Re: Strangely Variable Query Performance |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-04-13 02:40:14 | Re: Strangely Variable Query Performance |
Previous Message | Tom Lane | 2007-04-13 00:20:06 | Re: Strangely Variable Query Performance |