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
Message-ID: <06a401c81b37$eebb4260$8f01010a@iptel.com.ar> (raw)
In-Reply-To: <069c01c81b35$5d2dfa10$8f01010a@iptel.com.ar>
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.
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fhevia@ip-tel.com.ar
Subject: Re: Perfomance benefit using Min() against order by & limit 1?
In-Reply-To: <06a401c81b37$eebb4260$8f01010a@iptel.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox