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@magproductions.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 //