From: philb@vodafone.ie
To: pgsql-performance@postgresql.org
Subject: Query plan for very large number of joins
Date: Thu, 2 Jun 2005 16:25:25 +0100 (IST)
Message-ID: <17165144.1117725925709.JavaMail.tomcat@iecsai19> (raw)
Hi,
I am using PostgreSQL (7.4) with a schema that was generated automatically (using hibernate).
The schema consists of about 650 relations. One particular query (also generated
automatically) consists of left joining approximately 350 tables. At this
stage, most tables are empty and those with values have less than 50 entries.
The query takes about 90 seconds to execute (on a P4, 2.6Ghz).
All of the relations have a primary key which is indexed and all of the joins
are on foreign keys which are explicitly declared. I've checked the obvious
tunables (effective_cache_size, shared_memory and sort_buffer) but changing
these has had no effect. The system has a total of 750MB RAM, I've varied
the shared memory up to 256MB and the sort buffer up to 128MB without affecting
the performance.
Running the query as a JDBC prepared statement indicates that the query optimiser
is spending a negligable amount of time on the task (~ 36 ms) compared to
the executor (~ 90 seconds). The output of EXPLAIN indicates (AFAICT) that
all of the joins are of type "Nested Loop Left Join" and all of the scans
are of type "Seq Scan". I have refrained from posting the query and the
query plan since these are 80K and 100K apiece but if anyone wants to see
them I can certainly forward them on.
My (uninformed) suspicion is that the optimiser has failed over to the default
plan on the basis of the number of tables in the join. My question is, is
there anyone out there using PostgreSQL with this size of schema? Is there
anything that can be done to bring about the order of magnitude increase
in speed that I need?
Thanks for your help,
-phil
I'm using Vodafone Mail - to get your free mobile email account go to http://www.vodafone.ie
Use of Vodafone Mail is subject to Terms and Conditions http://www.vodafone.ie/terms/website
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: philb@vodafone.ie
Subject: Re: Query plan for very large number of joins
In-Reply-To: <17165144.1117725925709.JavaMail.tomcat@iecsai19>
* 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