pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Perfomance benefit using Min() against order by & limit 1? 2+ messages / 1 participants [nested] [flat]
* Perfomance benefit using Min() against order by & limit 1? @ 2007-10-30 20:42 Fernando Hevia <fhevia@ip-tel.com.ar> 2007-10-30 21:00 ` Re: Perfomance benefit using Min() against order by & limit 1? Fernando Hevia <fhevia@ip-tel.com.ar> 0 siblings, 1 reply; 2+ messages in thread From: Fernando Hevia @ 2007-10-30 20:42 UTC (permalink / raw) To: 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Perfomance benefit using Min() against order by & limit 1? 2007-10-30 20:42 Perfomance benefit using Min() against order by & limit 1? Fernando Hevia <fhevia@ip-tel.com.ar> @ 2007-10-30 21:00 ` Fernando Hevia <fhevia@ip-tel.com.ar> 0 siblings, 0 replies; 2+ messages in thread From: Fernando Hevia @ 2007-10-30 21:00 UTC (permalink / raw) To: 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-10-30 21:00 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-10-30 20:42 Perfomance benefit using Min() against order by & limit 1? Fernando Hevia <fhevia@ip-tel.com.ar> 2007-10-30 21:00 ` Fernando Hevia <fhevia@ip-tel.com.ar>
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