To: eVl <evl.evl@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: optimization downgrade perfomance? 
In-reply-to: <652316563.20050923212351@gmail.com> 
References: <133614678.20050923012716@gmail.com>
	<3192.1127482808@sss.pgh.pa.us>
	<652316563.20050923212351@gmail.com>
Comments: In-reply-to eVl <evl.evl@gmail.com>
	message dated "Fri, 23 Sep 2005 21:23:51 +0300"
Date: Fri, 23 Sep 2005 23:09:09 -0400
Message-ID: <8872.1127531349@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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