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: | 2007-10-30 20:42:18 |
Message-ID: | 069c01c81b35$5d2dfa10$8f01010a@iptel.com.ar |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Gerardo Herzig | 2007-10-30 20:43:03 | puzzled by SELECT INTO |
Previous Message | Tom Lane | 2007-10-29 18:42:57 | Re: update from select |