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@xxxxxxxxxx> 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
