pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Nik Putnam <nputnam@spinon.berkeley.edu>
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] bad select performance fixed by forbidding hash joins
Date: Thu, 21 Oct 1999 00:22:13 -0700
Message-ID: <380EBF24.3CCDBC1B@spinon.berkeley.edu> (raw)

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




view thread (7+ messages)  latest in thread

Message-ID: <380EBF24.3CCDBC1B@spinon.berkeley.edu>
Permalink:  ../380EBF24.3CCDBC1B@spinon.berkeley.edu/
Also on:    postgresql.org/message-id/380EBF24.3CCDBC1B@spinon.berkeley.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: nputnam@spinon.berkeley.edu
  Subject: Re: [SQL] bad select performance fixed by forbidding hash joins
  In-Reply-To: <380EBF24.3CCDBC1B@spinon.berkeley.edu>
  References: <380EBF24.3CCDBC1B@spinon.berkeley.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox