Thread: optimization downgrade perfomance?

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

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
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?

You tell us --- let's see EXPLAIN ANALYZE results for both cases.

            regards, tom lane

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=418loops=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)
WHENtrue 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=578loops=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