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