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:[EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq