optimization downgrade perfomance?
От | eVl |
---|---|
Тема | optimization downgrade perfomance? |
Дата | |
Msg-id | 133614678.20050923012716@gmail.com обсуждение исходный текст |
Ответы |
Re: optimization downgrade perfomance?
|
Список | 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 aboutlocal 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
В списке pgsql-performance по дате отправления: