pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: secret <secret@kearneydev.com>
Cc: pgsql-sql@hub.org
Subject: Re: How to optimize a query...
Date: Wed, 10 Mar 1999 10:27:31 -0500
Message-ID: <17563.921079651@sss.pgh.pa.us> (raw)
In-Reply-To: <199903100400.XAA42129@hub.org>

>     I originally had this query with an OR clause instead of the UNION,
> which made it amazingly impossibly slow, with the UNION it's far faster
> however it's still very slow,(10,000+ rows in both stables), is there a
> way to speed this up?

Hmm.  What does EXPLAIN give as the query plan if you use the OR
approach?  The UNION method requires much more work than a decent OR
plan would need; as you can see from the query plan, it has to sort and
unique-ify the results of the two sub-selects in order to discard
duplicate tuples.  If you don't mind seeing the same tuple twice when
it matches on both PO fields, you could use UNION ALL rather than UNION
to avoid the sort step.  But that's just a hack...

I think the problem might be that Postgres doesn't know how to handle
ORed join clauses very efficiently.  Something to work on in the future.

>     I have another query that joins the result of this with 5 other
> tables, unfortunately that one takes like 10 minutes...

How long does it take to EXPLAIN that query?  Postgres' optimizer has
some bugs that cause it to take an unreasonable amount of time to plan
a query that joins more than a few tables.  You can tell whether it's
the planning or execution time that's the problem by comparing EXPLAIN
runtime to the actual query.  (The bugs are fixed in 6.5, btw.  In the
meantime a workaround is to reduce the GEQO threshold to less than the
number of tables in the query that's giving you trouble.)

			regards, tom lane



view thread (4+ messages)  latest in thread

Message-ID: <17563.921079651@sss.pgh.pa.us>
Permalink:  ../17563.921079651@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/17563.921079651@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, secret@kearneydev.com, pgsql-sql@hub.org
  Subject: Re: How to optimize a query...
  In-Reply-To: <17563.921079651@sss.pgh.pa.us>
  References: <199903100400.XAA42129@hub.org> <17563.921079651@sss.pgh.pa.us>

* 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