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 по дате отправления:

Предыдущее
От: "Ahmad Fajar"
Дата:
Сообщение: tsearch2 seem very slow
Следующее
От: Joost Kraaijeveld
Дата:
Сообщение: How to determine cause of performance problem?