Received: from nak.berkeley.edu (nak.Berkeley.EDU [128.32.206.21]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA07812 for ; Thu, 21 Oct 1999 03:29:59 -0400 (EDT) (envelope-from nputnam@spinon.berkeley.edu) Received: from spinon.berkeley.edu (spinon.Berkeley.EDU [169.229.15.136]) by nak.berkeley.edu (8.8.5/8.8.5) with ESMTP id AAA14608 for ; Thu, 21 Oct 1999 00:29:58 -0700 (PDT) Sender: nputnam@nak.berkeley.edu Message-ID: <380EBF24.3CCDBC1B@spinon.berkeley.edu> Date: Thu, 21 Oct 1999 00:22:13 -0700 From: Nik Putnam X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.2.12 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: [SQL] bad select performance fixed by forbidding hash joins Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I tried this out to see if it would speed up *my* query too. The query went much faster, but gave different results! [nputnam@spinon ~]$ setenv PGOPTIONS "-fh" [nputnam@spinon ~]$ psql -e expression < query.sql gives 15 rows of output in 1s. [nputnam@spinon ~]$ unsetenv PGOPTIONS [nputnam@spinon ~]$ psql -e expression < query.sql gives 289 rows of output in 51s The slow one is giving the right answer. Why might they be different? Anyone know where there's documentation for -fh? I'm using postgres 6.5.2 on linux 2.2.12 Thanks, Nik > George Young writes: > > Yes! PGOPTIONS="-fh" made the query time go from 16 seconds to 2 seconds! > > Is this a safe thing to leave on permanently, or is there some way to set > > PGOPTIONS for just this query? > > I wouldn't recommend leaving it on as a long-term solution, because > you're hobbling the system for cases where hashjoin *is* the best > method. AFAIK there is not a SET VARIABLE method for enabling/disabling > plan types on-the-fly, though perhaps one should be added. > > The right long-term solution is to figure out why the system is > misestimating the relative costs of the two plans, and fix the cost > estimates. (The system is estimating that the mergejoin is about 4x > slower than hash; if it's really 8x faster, there is something pretty > broken about the estimate...) > > I am interested in looking into this. If your data is not proprietary, > perhaps you would be willing to send me a database dump so that I can > reproduce the problem exactly? (If the dump is no more than a few > megabytes, emailing it should be OK.) No big hurry, since I probably > won't be able to get to it for a week or so anyway. > > regards, tom lane > > >