See the change in the plan between 10MB and 12MB, directly at top level? That narrows the thing down quite a bit.
Though I wonder why this didn't show in the original plans...
Yes, the 2,4 and 10 are the same, the only difference is number of buckets. But with 12, it makes completely different choices, it decides to make sequential scans and hash right joins instead of merge joins. And those sequential scans take a loong time. Could this be caused by some missing indices perhaps?
Well, you do have indices, as we can clearly see.
The original plans I posted at the start are the same as the 12MB plan, I'm not sure why is that, I really hope I didn't make some sort of mistake there.
I had been wondering why you didn't have any indices, tbth. However, the execution times still grow with work_mem, which is interesting independent of the actual plan change...
Thanks for your help by the way! :-)
Oh, no worries there... this is by far the most interesting challenge I've encountered in months ;-)
But I do admit that I've reached the end of the ladder now. No idea how you can improve your runtime yet. Probably - using full text search on "personinfo" - try different join_collapse_limit / from_collapse_limit / enable_hashjoin values
The most pragmatic approach is probably to just stick with work_mem = 1MB (or less) ;-), but that may potentially bite you later.
Yes, that's what I'm running now in production :) When I have more time I may come up with more queries to test overall system better.
We'll see if anyone else comes up with something but I am out of things to try, too. So I guess I'll put this sideways for now.
--
Gunnar "Nick" Bluth
RHCE/SCLA
Mobil +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX. Ten years later
they are choosing Windows over UNIX. What part of that message aren't you
getting? - Tom Payne