Re: 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: Re: Perfomance benefit using Min() against order by & limit 1?
Date: 2007-10-30 21:00:42
Message-ID: 06a401c81b37$eebb4260$8f01010a@iptel.com.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Gerardo Herzig 2007-10-31 12:28:16 Re: puzzled by SELECT INTO
Previous Message Rodrigo De León 2007-10-30 20:57:21 Re: puzzled by SELECT INTO