Message-ID: <4DA451FD.7050900@peak6.com>
Date: Tue, 12 Apr 2011 08:22:05 -0500
From: Shaun Thomas <sthomas@peak6.com>
Reply-To: <sthomas@peak6.com>
Organization: Peak6
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.7) Gecko/20100720 Lightning/1.0b2 Thunderbird/3.1.1
MIME-Version: 1.0
To: Lucas Madar <madar@samsix.com>
CC: <pgsql-performance@postgresql.org>
Subject: Re: Poor performance when joining against inherited tables
References: <sig.50826e4e2f.4DA3608E.9090906@samsix.com>
In-Reply-To: <sig.50826e4e2f.4DA3608E.9090906@samsix.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
content-transfer-encoding: 7bit

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