Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 8FD329FC040
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 30 Oct 2007 18:01:19 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 71620-10 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 30 Oct 2007 18:01:00 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from pyxis.powervt.com.ar (host6.powervt.com.ar [200.43.63.66])
	by postgresql.org (Postfix) with ESMTP id 8D9F99FBFC9
	for <pgsql-sql@postgresql.org>; Tue, 30 Oct 2007 18:01:13 -0300 (ADT)
Received: from pcfhevia ([201.216.249.98])
	by pyxis.powervt.com.ar (IPGroup Mail Server) with ESMTP id 1OT48953
	for <pgsql-sql@postgresql.org>; Tue, 30 Oct 2007 18:01:12 -0300
From: "Fernando Hevia" <fhevia@ip-tel.com.ar>
To: <pgsql-sql@postgresql.org>
Subject: Re: Perfomance benefit using Min() against order by & limit 1?
Date: Tue, 30 Oct 2007 18:00:42 -0300
Organization: IP-TEL
Message-ID: <06a401c81b37$eebb4260$8f01010a@iptel.com.ar>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook 11
Thread-Index: AcgbNVyYOcm+FgcISlWa2SRdPZPXcgAAgegw
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
In-Reply-To: <069c01c81b35$5d2dfa10$8f01010a@iptel.com.ar>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200710/152
X-Sequence-Number: 29524

Oops. Previous message went in HMTL. Sorry for that.
Text-only version follows.

---
Hi guys. Is there any difference between these two queries regarding
performance?
Table stopvoip has several million records.=20
I suspect using the aggregate function would be best, but benchmarking
doesn=92t seem to confirm it. Both queries take around 150 - 175 ms once =
data
has been cached.=20
Any hindsights?

SELECT min(h323setuptime::date)
FROM stopvoip=20
WHERE callingstationid =3D '2941605118'=A0=A0=20
AND h323setuptime >=3D '2007.07.01'=A0=A0=20
AND h323disconnectcause =3D '10'=A0=A0=20
AND acctsessiontime > 0=A0=A0=20
AND NOT calledstationid ~ '^99[89]#'=A0=A0=20

Aggregate=A0 (cost=3D11151.25..11151.27 rows=3D1 width=3D8)"
=A0 ->=A0 Bitmap Heap Scan on stopvoip=A0 (cost=3D29.29..11149.98 =
rows=3D507 width=3D8)
=A0=A0=A0=A0=A0=A0=A0 Recheck Cond: ((callingstationid)::text =3D =
'2941605118'::text)
=A0=A0=A0=A0=A0=A0=A0 Filter: ((h323setuptime >=3D '2007-07-01 =
00:00:00-03'::timestamp with
time zone) AND ((h323disconnectcause)::text =3D '10'::text) AND
(acctsessiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text))
=A0=A0=A0=A0=A0=A0=A0 ->=A0 Bitmap Index Scan on =
idx_stopvoip_callingid2=A0 (cost=3D0.00..29.29
rows=3D2939 width=3D0)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: =
((callingstationid)::text =3D '2941605118'::text)


SELECT h323setuptime::date=20
FROM stopvoip=20
WHERE callingstationid =3D '2941605118'=A0=A0=20
AND h323setuptime >=3D '2007.07.01'=A0=A0=20
AND h323disconnectcause =3D '10'=A0=A0=20
AND acctsessiontime > 0=A0=A0=20
AND NOT calledstationid ~ '^99[89]#'=A0=A0=20
ORDER BY 1=20
LIMIT 1

Limit=A0 (cost=3D11174.03..11174.03 rows=3D1 width=3D8)
=A0 ->=A0 Sort=A0 (cost=3D11174.03..11175.30 rows=3D507 width=3D8)
=A0=A0=A0=A0=A0=A0=A0 Sort Key: (h323setuptime)::date
=A0=A0=A0=A0=A0=A0=A0 ->=A0 Bitmap Heap Scan on stopvoip=A0 =
(cost=3D29.29..11151.25 rows=3D507
width=3D8)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Recheck Cond: =
((callingstationid)::text =3D '2941605118'::text)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Filter: ((h323setuptime >=3D =
'2007-07-01 00:00:00-03'::timestamp
with time zone) AND ((h323disconnectcause)::text =3D '10'::text) AND
(acctsessiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text))
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Bitmap Index Scan on =
idx_stopvoip_callingid2=A0
(cost=3D0.00..29.29 rows=3D2939 width=3D0)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: =
((callingstationid)::text =3D
'2941605118'::text)


Thanks,
Fernando.