On 6 November 2012 14:50, Gunnar "Nick" Bluth <gunnar.bl...@pro-open.de>wrote:
> Am 06.11.2012 21:24, schrieb Petr Praus: > > On 6 November 2012 14:17, Gunnar "Nick" Bluth <gunnar.bl...@pro-open.de>wrote: > >> Am 06.11.2012 21:08, schrieb Petr Praus: >> >> >>> 2MB: http://explain.depesz.com/s/ul1 >>> 4MB: http://explain.depesz.com/s/IlVu >>> 10MB: http://explain.depesz.com/s/afx3 >>> 12MB: http://explain.depesz.com/s/i0vQ >>> >>> 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.bl...@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 > >
