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
View "public.comlog"
Column | Type | Modifiers
----------+---------+-----------
opmode | boolean |
date | date |
jfinito | date |
readout | numeric |
brand1 | integer |
brand2 | integer |
clty | integer |
opis | text |
View definition:
SELECT true AS opmode, date(c.tm) AS date, c.jfinito, c.readout, c.brand1, c.brand2, c.clty, c.opis
FROM log1 c
UNION
SELECT false AS opmode, date(date_mi_interval(s.tm, '00:00:01'::interval)) AS date, NULL::"unknown" AS jfinito, s.state AS readout, s.brand1, s.brand2, s.clty, 'master' AS opis
FROM log2 s;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on log2 (cost=0.00..363.84 rows=20484 width=0) (actual time=0.022..55.879 rows=20484 loops=1)
Total runtime: 81.673 ms
(2 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on log1 (cost=0.00..698.16 rows=30916 width=0) (actual time=0.022..87.184 rows=30916 loops=1)
Total runtime: 126.834 ms
(2 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan comlog (cost=7137.30..8807.80 rows=51400 width=0) (actual time=2992.079..3507.783 rows=51400 loops=1)
-> Unique (cost=7137.30..8293.80 rows=51400 width=59) (actual time=2992.067..3359.512 rows=51400 loops=1)
-> Sort (cost=7137.30..7265.80 rows=51400 width=59) (actual time=2992.058..3123.836 rows=51400 loops=1)
Sort Key: opmode, date, jfinito, readout, brand1, brand2, clty, opis
-> Append (cost=0.00..1755.71 rows=51400 width=59) (actual time=0.048..701.949 rows=51400 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..1084.61 rows=30916 width=59) (actual time=0.045..332.276 rows=30916 loops=1)
-> Seq Scan on log1 c (cost=0.00..775.45 rows=30916 width=59) (actual time=0.041..203.392 rows=30916 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..671.10 rows=20484 width=26) (actual time=0.088..227.255 rows=20484 loops=1)
-> Seq Scan on log2 s (cost=0.00..466.26 rows=20484 width=26) (actual time=0.077..146.642 rows=20484 loops=1)
Total runtime: 3589.929 ms
(10 rows)
