pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedoptimization downgrade perfomance?
3+ messages / 2 participants
[nested] [flat]
* optimization downgrade perfomance?
@ 2005-09-22 22:27 eVl <evl.evl@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: eVl @ 2005-09-22 22:27 UTC (permalink / raw)
To: pgsql-performance
Hello!
Got a DB with traffic statictics stored. And a SELECT statement which shows traffic volume per days also divided by regions - local traffic and global.
Thus SELECT statement returns about some (in about 10-20) rows paired like this:
ttype (text)| volume (int)| tdate (date)
----------------------------------------
local | xxxxx | some-date
global | xxxxx | some-date
When executing this SELECT (see SELECT.A above) it executes in about 700 ms, but when I want wipe out all info about local traffic, with query like this:
SELECT * FROM ( SELECT.A ) a WHERE type = 'global';
It executes about 10000 ms - more then 10 TIMES SLOWER!
Why this could be?
-------------------------------------------------
Initial Query - SELECT.A (executes about 700 ms)
SELECT
CASE is_local(aa.uaix) WHEN true THEN 'local'
ELSE 'global' END AS TType,
aa.cDate AS TDate,
SUM(aa.data) AS Value
FROM (
SELECT
a.uaix AS uaix,
cDate AS cDate,
SUM(a.data) AS data
FROM (
(
SELECT toIP AS uaix,
cDate AS cDate,
SUM(packetSize) AS data
FROM vw_stats
WHERE interface <> 'inet'
AND cdate = '01.09.2005'
AND fromIP << '192.168.0.0/16'
AND NOT (toIP << '192.168.0.0/16')
GROUP BY 1,2
)
UNION
(
SELECT fromIP AS uaix,
cDate AS cDate,
SUM(packetSize) AS data
FROM vw_stats
WHERE interface <> 'inet'
AND cdate = '01.09.2005'
AND toIP << '192.168.0.0/16'
AND NOT (fromIP << '192.168.0.0/16')
GROUP BY 1,2
)
) a
GROUP BY 1,2
) aa
GROUP BY 1,2
ORDER BY 1,2
-----------------------------------------------------------
Query with local info filtered (executes about 10000 ms)
SELECT * FROM (
<HERE PLACED SELECT.A>
) aaa WHERE aaa.TType = 'global';
-----------------------------------------------------------
Running Postgresql 8.0.3 on FreeBSD 5.3
--
Best regards,
eVl mailto:evl.evl@gmail.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: optimization downgrade perfomance?
@ 2005-09-23 13:40 Tom Lane <tgl@sss.pgh.pa.us>
parent: eVl <evl.evl@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2005-09-23 13:40 UTC (permalink / raw)
To: eVl <evl.evl@gmail.com>; +Cc: pgsql-performance
eVl <evl.evl@gmail.com> writes:
> When executing this SELECT (see SELECT.A above) it executes in about 700 ms, but when I want wipe out all info about local traffic, with query like this:
> SELECT * FROM ( SELECT.A ) a WHERE type = 'global';
> It executes about 10000 ms - more then 10 TIMES SLOWER!
> Why this could be?
You tell us --- let's see EXPLAIN ANALYZE results for both cases.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: optimization downgrade perfomance?
@ 2005-09-24 03:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-09-24 03:09 UTC (permalink / raw)
To: eVl <evl.evl@gmail.com>; +Cc: pgsql-performance
eVl <evl.evl@gmail.com> writes:
>> You tell us --- let's see EXPLAIN ANALYZE results for both cases.
> Here EXPLAIN ANALYZE results for both queries attached.
The problem seems to be that the is_uaix() function is really slow
(somewhere around 4 msec per call it looks like). Look at the
first scan over stats:
-> Index Scan using cdate_cluster on stats s (cost=0.00..201.51 rows=6 width=25) (actual time=5.231..2165.145 rows=418 loops=1)
Index Cond: (cdate = '2005-09-01'::date)
Filter: ((fromip << '192.168.0.0/16'::inet) AND (NOT (toip << '192.168.0.0/16'::inet)) AND (CASE is_uaix(toip) WHEN true THEN 'local'::text ELSE 'global'::text END = 'global'::text))
versus
-> Index Scan using cdate_cluster on stats s (cost=0.00..165.94 rows=1186 width=25) (actual time=0.131..43.258 rows=578 loops=1)
Index Cond: (cdate = '2005-09-01'::date)
Filter: ((fromip << '192.168.0.0/16'::inet) AND (NOT (toip << '192.168.0.0/16'::inet)))
The 578 evaluations of the CASE are adding over 2100msec. There's
another 1600 evaluations needed in the other arm of the UNION...
Better look at exactly what is_uaix() is doing, because the CASE structure
is surely not that slow.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-24 03:09 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-22 22:27 optimization downgrade perfomance? eVl <evl.evl@gmail.com>
2005-09-23 13:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-24 03:09 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox