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
=========================