From: Shaun Thomas <sthomas@peak6.com>
To: Lucas Madar <madar@samsix.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Poor performance when joining against inherited tables
Date: Tue, 12 Apr 2011 08:22:05 -0500
Message-ID: <4DA451FD.7050900@peak6.com> (raw)
In-Reply-To: <sig.50826e4e2f.4DA3608E.9090906@samsix.com>
References: <sig.50826e4e2f.4DA3608E.9090906@samsix.com>
On 04/11/2011 03:11 PM, Lucas Madar wrote:
> EXPLAIN ANALYZE SELECT * FROM objects INNER JOIN item f USING ( id );
>
> This scans everything over everything, and obviously takes forever
> (there are millions of rows in the objects table, and tens of thousands
> in each itemXX table).
What is your constraint_exclusion setting? This needs to be 'ON' for the
check constraints you use to enforce your inheritance rules to work right.
You *do* have check constraints on all your child tables, right? Just in
case, please refer to the doc on table partitioning:
http://www.postgresql.org/docs/current/static/ddl-partitioning.html
Also, your example has no where clause. Without a where clause,
constraint exclusion won't even function. How is the database supposed
to know that matching a 4M row table against several partitioned tables
will result in few matches? All it really has are stats on your joined
id for this particular query, and you're basically telling to join all
of them. That usually calls for a sequence scan, because millions of
index seeks will almost always be slower than a few sequence scans.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com
______________________________________________
See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email
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: sthomas@peak6.com, madar@samsix.com
Subject: Re: Poor performance when joining against inherited tables
In-Reply-To: <4DA451FD.7050900@peak6.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