Re: optimising UNION performance

Thank you All for explanations. Looks loke that's what I was looking
for.

UNION ALL is quite satisfactory (830ms).

And yet, somwhere I loose c.a. 600ms (as compared to 120ms+80ms of each
respective 'raw' subquery).... which as percentage seem signifficant.
Does anybody know where the processing goes now?

Currently, the ANALYSE looks like this:
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------
Subquery Scan comlog (cost=0.00..2269.71 rows=51400 width=0) (actual
time=0.053..755.649 rows=51400 loops=1)
-> Append (cost=0.00..1755.71 rows=51400 width=59) (actual
time=0.048..607.437 rows=51400 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..1084.61 rows=30916 width=59)
(actual time=0.046..278.802 rows=30916 loops=1)
-> Seq Scan on log1 c (cost=0.00..775.45 rows=30916 width=59) (actual
time=0.042..170.193 rows=30916 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..671.10 rows=20484 width=26)
(actual time=0.055..200.223 rows=20484 loops=1)
-> Seq Scan on log2 s (cost=0.00..466.26 rows=20484 width=26) (actual
time=0.044..127.301 rows=20484 loops=1)
Total runtime: 822.901 ms
(7 rows)
-----------------------------------------

On Mon, 2006-08-28 at 09:11 +0000, Ragnar wrote:
> On mán, 2006-08-28 at 10:23 +0200, Rafal Pietrak wrote:
> > Hi all,
> >
> > Is there a way to speed up the query to my 'grand total' logfile,
> > constructed as a UNION of smaller (specialised) logfiles?
> >
>
> I do not know if this is relevant to your case, but
> possibly you can use a UNION ALL instead of a UNION.
>
> In many cases the UNION ALL gives petter performance,
> as the unique step can be skipped, as well as a sort
> needed by the unique.
>
> gnari
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
-R