optimization downgrade perfomance?

From: eVl <evl(dot)evl(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: optimization downgrade perfomance?
Date: 2005-09-22 22:27:16
Message-ID: 133614678.20050923012716@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(dot)evl(at)gmail(dot)com

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jean-Pierre Pelletier 2005-09-22 22:28:29 Re: Queries 15 times slower on 8.1 beta 2 than on 8.0
Previous Message Josh Berkus 2005-09-22 22:19:05 Re: Queries 15 times slower on 8.1 beta 2 than on 8.0