Skip site navigation (1) Skip section navigation (2)

Peripheral Links

Header And Logo

PostgreSQL
| The world's most advanced open source database.

Site Navigation

Search archives
  Advanced Search

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


  • From: "Fernando Hevia" <fhevia(at)ip-tel(dot)com(dot)ar>
  • To: <pgsql-sql(at)postgresql(dot)org>
  • Subject: Perfomance benefit using Min() against order by & limit 1?
  • Date: Tue, 30 Oct 2007 17:42:18 -0300
  • Message-id: <069c01c81b35$5d2dfa10$8f01010a@iptel.com.ar> <text/plain>

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.



Home | Main Index | Thread Index

Privacy Policy | About PostgreSQL
Copyright © 1996 – 2012 PostgreSQL Global Development Group