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@xxxxxxxxxxxxxxxxx
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 //
