From: Frank Dekervel <frank.dekervel@smartlounge.be>
To: pgsql-performance@postgresql.org
Subject: "append" takes a lot of time in a query
Date: Fri, 9 May 2008 10:09:46 +0200
Message-ID: <200805091009.46893.frank.dekervel@smartlounge.be> (raw)
Hello,
I have a strange performance problem with postgresql 8.3 (shipped with ubuntu
hardy) and a query that seems very simple:
explain analyze
SELECT * FROM (part LEFT OUTER JOIN part_lang ON part.id = part_lang.id)
WHERE part.parent = 49110;
query plan here: http://front7.smartlounge.be/~kervel/queryplan.txt
the query does not return a single row
the table "part" and "part_lang" have a whole lot of tables inheriting them,
most of the inheriting tables only contain a few rows.
this turns this query in an append of a whole lot of seq scan/ index scan's.
These scans are predictably quick, but the "append" takes 5 seconds (and the
numbers of the scans do not add up to the append actual time)
if i leave out the "outer join" performance is okay:
SELECT * FROM part WHERE part.parent = 49110;
if i then add a "order by sequence number" the performance is bad again:
SELECT * FROM part WHERE part.parent = 49110 order by sequencenumber;
I'm a bit stuck with this problem, and i don't know how to continue finding
out why.
Does someone have an explanation / possible solution for this performance ?
We use a similar scheme on a lot of other projects without problems (but this
time, the number of tables inheriting from part is a bit bigger).
Thanks a lot in advance,
greetings,
Frank
--
=========================
Frank Dekervel
frank.dekervel@smartlounge.be
=========================
Smartlounge
JP Minckelersstraat 78
3000 Leuven
phone:+32 16 311 413
fax:+32 16 311 410
mobile:+32 473 943 421
=========================
http://www.smartlounge.be
=========================
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: frank.dekervel@smartlounge.be
Subject: Re: "append" takes a lot of time in a query
In-Reply-To: <200805091009.46893.frank.dekervel@smartlounge.be>
* 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