Re: Poor performance when joining against inherited tables

From: Shaun Thomas <sthomas(at)peak6(dot)com>
To: Lucas Madar <madar(at)samsix(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Poor performance when joining against inherited tables
Date: 2011-04-12 13:22:05
Message-ID: 4DA451FD.7050900@peak6.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Merlin Moncure 2011-04-12 13:23:21 Re: Linux: more cores = less concurrency.
Previous Message Glyn Astill 2011-04-12 12:35:19 Re: Linux: more cores = less concurrency.