pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Neil Conway <neilc@samurai.com>
To: hlapp@gmx.net
Cc: pgsql-performance@postgresql.org
Subject: Re: join over 12 tables takes 3 secs to plan
Date: Thu, 2 Jan 2003 15:41:24 -0500 (EST)
Message-ID: <1309.24.112.166.30.1041540084.squirrel@mailbox.samurai.com> (raw)
In-Reply-To: <43D30901-1E8A-11D7-9244-000393B4BFF6@gmx.net>
References: <43D30901-1E8A-11D7-9244-000393B4BFF6@gmx.net>

Hilmar Lapp said:
> I have a query generated by an application (not mine, but there's
> nothing I can find that looks bad about the query itself) that takes an
> excessive amount of time to return even though there are almost no rows
> in the schema yet.

Yes -- an exhaustive search to determine the correct join order for a
multiple relation query is similar to solving the traveling salesman
problem (only more difficult, due to the availability of different join
algorithms, etc.). GEQO should be faster than the default optimizer for
large queries involving large numbers of joins, but it's still going to
take a fair bit of time.

In other words, it's not a surprise that a 12-relation join takes a little
while to plan.

> I'm running Postgres 7.3.1 on Mac OSX.

Tom recently checked in some optimizations for GEQO in CVS HEAD, so you
could try using that (or at least testing it, so you have an idea of what
7.4 will perform like).

You could also try using prepared queries.

Finally, there are a bunch of GEQO tuning parameters that you might want
to play with. They should allow you to reduce the planning time a bit, in
exchange for possibly generating an inferior plan.

Cheers,

Neil





view thread (22+ messages)  latest in thread

Message-ID: <1309.24.112.166.30.1041540084.squirrel@mailbox.samurai.com>
Permalink:  ../1309.24.112.166.30.1041540084.squirrel@mailbox.samurai.com/
Also on:    postgresql.org/message-id/1309.24.112.166.30.1041540084.squirrel@mailbox.samurai.com

 · 

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-performance@postgresql.org
  Cc: neilc@samurai.com, hlapp@gmx.net
  Subject: Re: join over 12 tables takes 3 secs to plan
  In-Reply-To: <1309.24.112.166.30.1041540084.squirrel@mailbox.samurai.com>
  References: <43D30901-1E8A-11D7-9244-000393B4BFF6@gmx.net> <1309.24.112.166.30.1041540084.squirrel@mailbox.samurai.com>

* 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