Perfomance benefit using Min() against order by & limit 1? - Mailing list pgsql-sql

<p dir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">Hi
guys.Is there any</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font
face="Arial"size="2"> difference between these two queries regarding performance?</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"></span><p dir="LTR"><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">Table stopvoip has several million
records.</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial"
size="2"></font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> </span><p dir="LTR"><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">I</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"> <font face="Arial" size="2">suspect</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"><font face="Arial" size="2"> using the aggregate
functionwould be best, but benchmarking doesn</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">’</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">t seem to</font></span><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"> <font face="Arial" size="2">confirm it.</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"> <font face="Arial" size="2">Both queries
take</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"> <font face="Arial"
size="2">around</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial"
size="2">150 - 175 ms once data has been cached.</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"></span><p dir="LTR"><span lang="en-us"><font face="Arial" size="2">Any hindsights?</font></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p dir="LTR"><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"></span><p dir="LTR"><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">SELECT min(h323setuptime::date)</font></span><p
dir="LTR"><spanlang="en-us"><font face="Arial" size="2">FROM stopvoip </font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">WHERE callingstationid = '2941605118'   </font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">AND h323setuptime >= '2007.07.01'   </font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">AND h323disconnectcause = '10'   </font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">AND acctsessiontime > 0   </font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">AND NOT calledstationid ~ '^99[89]#'   </font></span><p dir="LTR"><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"></span><p dir="LTR"><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">"Aggregate  (cost=11151.25..11151.27 rows=1
width=8)"</font></span><pdir="LTR"><span lang="en-us"><font face="Arial" size="2">"  ->  Bitmap Heap Scan on
stopvoip (cost=29.29..11149.98 rows=507 width=8)"</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">"       Recheck Cond: ((callingstationid)::text = '2941605118'::text)"</font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">"        Filter: ((h323setuptime >= '2007-07-01 00:00:00-03'::timestamp with
timezone) AND ((h323disconnectcause)::text = '10'::text) AND (acctsessiontime > 0) AND ((calledstationid)::text !~
'^99[89]#'::text))"</font></span><pdir="LTR"><span lang="en-us"><font face="Arial" size="2">"        ->  Bitmap
IndexScan on idx_stopvoip_callingid2  (cost=0.00..29.29 rows=2939 width=0)"</font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">"              Index Cond: ((callingstationid)::text =
'2941605118'::text)"</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p dir="LTR"><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"></span><p dir="LTR"><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"><font face="Arial" size="2">SELECT h323setuptime::date </font></span><p
dir="LTR"><spanlang="en-us"><font face="Arial" size="2">FROM stopvoip </font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">WHERE callingstationid = '2941605118'   </font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">AND h323setuptime >= '2007.07.01'   </font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">AND h323disconnectcause = '10'   </font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">AND acctsessiontime > 0   </font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">AND NOT calledstationid ~ '^99[89]#'   </font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">ORDER BY 1 </font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">LIMIT1</font></span><span lang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"><font face="Arial" size="2">"Limit 
(cost=11174.03..11174.03rows=1 width=8)"</font></span><p dir="LTR"><span lang="en-us"><font face="Arial" size="2">" 
-> Sort  (cost=11174.03..11175.30 rows=507 width=8)"</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">"       Sort Key: (h323setuptime)::date"</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">"       ->  Bitmap Heap Scan on stopvoip  (cost=29.29..11151.25 rows=507 width=8)"</font></span><p
dir="LTR"><spanlang="en-us"><font face="Arial" size="2">"              Recheck Cond: ((callingstationid)::text =
'2941605118'::text)"</font></span><pdir="LTR"><span lang="en-us"><font face="Arial" size="2">"              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))"</font></span><p dir="LTR"><span
lang="en-us"><fontface="Arial" size="2">"              ->  Bitmap Index Scan on idx_stopvoip_callingid2 
(cost=0.00..29.29rows=2939 width=0)"</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">"                   Index Cond: ((callingstationid)::text = '2941605118'::text)"</font></span><span
lang="es-ar"></span><spanlang="es-ar"></span><span lang="en-us"></span><p dir="LTR"><span lang="es-ar"></span><span
lang="es-ar"></span><spanlang="en-us"></span><p dir="LTR"><span lang="es-ar"></span><span lang="es-ar"></span><span
lang="en-us"><fontface="Arial" size="2">Thanks,</font></span><p dir="LTR"><span lang="en-us"><font face="Arial"
size="2">Fernando.</font></span><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span><p
dir="LTR"><spanlang="es-ar"></span><span lang="es-ar"></span><span lang="en-us"></span>