Re: optimising UNION performance

Rafal Pietrak wrote:
> 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)
> -----------------------------------------

Just to make sure: You do have an appropriate index over the tables in
that UNION?

From experience, it seems that PostgreSQL chooses a sequential scan
over unioned sets instead of an index scan - the details escape me, but
there is a good reason for that. I'm sure it's not for performance
reasons, though.

There have been some discussions about inheritance performance, which
boils down to exactly this problem (inheritance basically is a UNION
over all the tables involved). You may want to check the archives.

Regards,
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //