pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
=========================



view thread (4+ messages)  latest in thread

Message-ID: <200805091009.46893.frank.dekervel@smartlounge.be>
Permalink:  ../200805091009.46893.frank.dekervel@smartlounge.be/
Also on:    postgresql.org/message-id/200805091009.46893.frank.dekervel@smartlounge.be

 · 

reply

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