From: | Nik Putnam <nputnam(at)spinon(dot)berkeley(dot)edu> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: [SQL] bad select performance fixed by forbidding hash joins |
Date: | 1999-10-21 07:22:13 |
Message-ID: | 380EBF24.3CCDBC1B@spinon.berkeley.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I tried this out to see if it would speed up *my* query too.
The query went much faster, but gave different results!
[nputnam(at)spinon ~]$ setenv PGOPTIONS "-fh"
[nputnam(at)spinon ~]$ psql -e expression < query.sql
gives 15 rows of output in 1s.
[nputnam(at)spinon ~]$ unsetenv PGOPTIONS
[nputnam(at)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
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-10-21 13:30:43 | Re: [SQL] bad select performance fixed by forbidding hash joins |
Previous Message | Eric Chow | 1999-10-21 02:55:50 | Example of embbed SQL ? |