Re: Poor performance when joining against inherited tables

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(at)peak6(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email