optimising UNION performance

From: Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: optimising UNION performance
Date: 2006-08-28 08:23:26
Message-ID: 1156753406.20343.22.camel@zorro.isa-geek.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

Is there a way to speed up the query to my 'grand total' logfile,
constructed as a UNION of smaller (specialised) logfiles?

Access to log1/log2 is quick (If I'm reading ANALYSE log correctly, it's
c.a. 100ms each - and it feels like that, so presumebly I'm reading
ANALYSE just OK), but the UNION is quite slow (3.5 sec, which I can
confirm - it's the real response time of the server in that case)

Again, if I'm reading the ANALYSE correctly (and I'm not really sure of
that), it looks like the performence is hit by the sort performed by the
UNION. The sort is not really needed in this case and don't really know
why it's there (my comlog VIEW definition does not request one - or does
it implicitly?).

One point not shown in the attachment, is that 'brands' and 'clty' are
foreing references. Should foreign reference matter here?

Can someone give me a hint on how to write a VIEW, that returns
concatenation of both log-tables within a sum of their respective access
times (as of current, that would be c.a. 200ms)

The ANALYSE is taken from postgres v8.1.4 installed from *.deb on
Debian-sid, hosted by 800MHz/512MB Duron machine. It really strickes me,
that 20k+30k rows is returned in 3.5sec!

--
-R

Attachment Content-Type Size
sql-union-problem text/plain 2.7 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bjørn T Johansen 2006-08-28 08:48:47 Convert time to millisec?
Previous Message Harpreet Dhaliwal 2006-08-28 07:31:45 Re: Perl language creation failed