X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4C0F8D922B
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Sep 2005 19:27:05 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 37059-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 22 Sep 2005 22:27:00 +0000 (GMT)
Received: from el.volia.net (el.volia.net [82.144.192.38])
	by svr1.postgresql.org (Postfix) with ESMTP id 39A64D6EB0
	for <pgsql-performance@postgresql.org>;
	Thu, 22 Sep 2005 19:27:00 -0300 (ADT)
Received: from ip.85.202.198.223.dyn.pool-4.broadband.voliacable.com
	([85.202.198.223] helo=evl)
	by el.volia.net with esmtp (Exim 4.43 (FreeBSD)) id 1EIZWw-0003At-6t
	for pgsql-performance@postgresql.org; Fri, 23 Sep 2005 01:27:02 +0300
Date: Fri, 23 Sep 2005 01:27:16 +0300
From: eVl <evl.evl@gmail.com>
X-Mailer: The Bat! (v3.0.1.33) Professional
Reply-To: eVl <evl.evl@gmail.com>
Organization: eVl inc.
X-Priority: 3 (Normal)
Message-ID: <133614678.20050923012716@gmail.com>
To: pgsql-performance@postgresql.org
Subject: optimization downgrade perfomance?
MIME-Version: 1.0
Content-Type: text/plain; charset=Windows-1251
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.207 required=5 tests=[UPPERCASE_25_50=0.207]
X-Spam-Level: 
X-Archive-Number: 200509/274
X-Sequence-Number: 14582

Hello!

=09Got a DB with traffic statictics stored. And a SELECT statement which sh=
ows traffic volume per days also divided by regions - local traffic and glo=
bal.
       Thus SELECT statement returns about some (in about 10-20) rows paire=
d like this:

ttype (text)| volume (int)| tdate (date)
----------------------------------------
local       | xxxxx       | some-date
global      | xxxxx       | some-date

=09When 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 =3D 'global';
It executes about 10000 ms - more then 10 TIMES SLOWER!

 Why this could be?



-------------------------------------------------
Initial Query - SELECT.A (executes about 700 ms)

SELECT=20
      CASE is_local(aa.uaix) WHEN true THEN 'local'=20
                             ELSE 'global' END AS TType,=20
      aa.cDate AS TDate,
      SUM(aa.data) AS Value=20
FROM (
      SELECT=20
            a.uaix AS uaix,=20
            cDate AS cDate,=20
            SUM(a.data) AS data=20
      FROM (
=09     (
             SELECT toIP AS uaix,=20
                    cDate AS cDate,=20
                    SUM(packetSize) AS data
=09      FROM vw_stats
             WHERE interface <> 'inet'
                  AND cdate =3D '01.09.2005'
                  AND fromIP << '192.168.0.0/16'
                  AND NOT (toIP << '192.168.0.0/16')
             GROUP BY 1,2
=09    )
           UNION=20
           (
            SELECT fromIP AS uaix,=20
                   cDate AS cDate,=20
                   SUM(packetSize) AS data
             FROM vw_stats
             WHERE interface <> 'inet'
                   AND cdate =3D '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 =3D 'global';


-----------------------------------------------------------

Running Postgresql 8.0.3 on FreeBSD 5.3

 =20

--=20
Best regards,
 eVl                          mailto:evl.evl@gmail.com