Perfomance benefit using Min() against order by & limit = 1?

Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7DFCB9F98B0 for ; Tue, 30 Oct 2007 17:42:59 -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 26972-04 for ; Tue, 30 Oct 2007 17:42:37 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from pyxis.powervt.com.ar (host3.powervt.com.ar [200.43.63.66]) by postgresql.org (Postfix) with ESMTP id 7EF9E9F98BE for ; Tue, 30 Oct 2007 17:42:49 -0300 (ADT) Received: from pcfhevia ([201.216.249.98]) by pyxis.powervt.com.ar (IPGroup Mail Server) with ESMTP id 1OT48953 for ; Tue, 30 Oct 2007 17:42:46 -0300 From: "Fernando Hevia" To: Subject: Perfomance benefit using Min() against order by & limit 1? Date: Tue, 30 Oct 2007 17:42:18 -0300 Organization: IP-TEL Message-ID: <069c01c81b35$5d2dfa10$8f01010a@iptel.com.ar> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_069D_01C81B1C.37E0C210" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcgbNVyYOcm+FgcISlWa2SRdPZPXcg== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/150 X-Sequence-Number: 29522 This is a multi-part message in MIME format. ------=_NextPart_000_069D_01C81B1C.37E0C210 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi guys. Is there any difference between these two queries regarding performance? Table stopvoip has several million records. I suspect using the aggregate function would be best, but benchmarking doesn't seem to confirm it. Both queries take around 150 - 175 ms once data has been cached. Any hindsights? SELECT min(h323setuptime::date) FROM stopvoip WHERE callingstationid = '2941605118' AND h323setuptime >= '2007.07.01' AND h323disconnectcause = '10' AND acctsessiontime > 0 AND NOT calledstationid ~ '^99[89]#' "Aggregate (cost=11151.25..11151.27 rows=1 width=8)" " -> Bitmap Heap Scan on stopvoip (cost=29.29..11149.98 rows=507 width=8)" " Recheck Cond: ((callingstationid)::text = '2941605118'::text)" " Filter: ((h323setuptime >= '2007-07-01 00:00:00-03'::timestamp with time zone) AND ((h323disconnectcause)::text = '10'::text) AND (acctsessiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text))" " -> Bitmap Index Scan on idx_stopvoip_callingid2 (cost=0.00..29.29 rows=2939 width=0)" " Index Cond: ((callingstationid)::text = '2941605118'::text)" SELECT h323setuptime::date FROM stopvoip WHERE callingstationid = '2941605118' AND h323setuptime >= '2007.07.01' AND h323disconnectcause = '10' AND acctsessiontime > 0 AND NOT calledstationid ~ '^99[89]#' ORDER BY 1 LIMIT 1 "Limit (cost=11174.03..11174.03 rows=1 width=8)" " -> Sort (cost=11174.03..11175.30 rows=507 width=8)" " Sort Key: (h323setuptime)::date" " -> Bitmap Heap Scan on stopvoip (cost=29.29..11151.25 rows=507 width=8)" " Recheck Cond: ((callingstationid)::text = '2941605118'::text)" " Filter: ((h323setuptime >= '2007-07-01 00:00:00-03'::timestamp with time zone) AND ((h323disconnectcause)::text = '10'::text) AND (acctsessiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text))" " -> Bitmap Index Scan on idx_stopvoip_callingid2 (cost=0.00..29.29 rows=2939 width=0)" " Index Cond: ((callingstationid)::text = '2941605118'::text)" Thanks, Fernando. ------=_NextPart_000_069D_01C81B1C.37E0C210 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Perfomance benefit using Min() against order by & limit = 1?

Hi guys. Is there any difference between these = two queries regarding performance?

Table stopvoip has several million = records.

I suspect using the aggregate function would be best, but = benchmarking doesn’t seem to confirm it. = Both queries take = around 150 - 175 ms once data has = been cached.

Any = hindsights?

SELECT min(h323setuptime::date)

FROM = stopvoip

WHERE = callingstationid =3D '2941605118'

AND = h323setuptime >=3D '2007.07.01'

AND = h323disconnectcause =3D '10'

AND = acctsessiontime > 0

AND NOT = calledstationid ~ '^99[89]#'

"Aggregate (cost=3D11151.25..11151.27 rows=3D1 = width=3D8)"

" -> Bitmap Heap Scan on = stopvoip (cost=3D29.29..11149.98 rows=3D507 = width=3D8)"

" Recheck = Cond: ((callingstationid)::text =3D = '2941605118'::text)"

" 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))"

" = -> Bitmap Index Scan on idx_stopvoip_callingid2 = (cost=3D0.00..29.29 rows=3D2939 width=3D0)"

" &nbs= p; Index Cond: ((callingstationid)::text =3D = '2941605118'::text)"

SELECT h323setuptime::date

FROM = stopvoip

WHERE = callingstationid =3D '2941605118'

AND = h323setuptime >=3D '2007.07.01'

AND = h323disconnectcause =3D '10'

AND = acctsessiontime > 0

AND NOT = calledstationid ~ '^99[89]#'

ORDER BY = 1

LIMIT = 1

"Limit (cost=3D11174.03..11174.03 rows=3D1 = width=3D8)"

" -> Sort = (cost=3D11174.03..11175.30 rows=3D507 width=3D8)"

" Sort = Key: (h323setuptime)::date"

" = -> Bitmap Heap Scan on stopvoip (cost=3D29.29..11151.25 = rows=3D507 width=3D8)"

" &nbs= p; Recheck Cond: ((callingstationid)::text =3D = '2941605118'::text)"

" &nbs= p; 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))"

" &nbs= p; -> Bitmap Index Scan on = idx_stopvoip_callingid2 (cost=3D0.00..29.29 rows=3D2939 = width=3D0)"

" &nbs= p; Index = Cond: ((callingstationid)::text =3D = '2941605118'::text)"

Thanks,

Fernando.

------=_NextPart_000_069D_01C81B1C.37E0C210--