Kevin Grittner wrote:


If you really are doing proper maintenance, and you don't need exact
counts, you might be able to use the approximation stored in the
system tables:
Also, if you're using count(*) as an existance test (common in Mysql
code), it's better to use exists instead. Using a table in my system, I
see:
proddb=> explain analyze select count(*) from instrument_listings
where update_date is null and delist_date is null;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=18385.45..18385.46 rows=1 width=0) (actual
time=897.799..897.801 rows=1 loops=1)
-> Seq Scan on instrument_listings (cost=0.00..17973.43
rows=164807 width=0) (actual time=0.018..634.197 rows=146122 loops=1)
Filter: ((update_date IS NULL) AND (delist_date IS NULL))
Total runtime: 897.846 ms
(4 rows)

Time: 898.478 ms
proddb=> explain analyze select true where exists(select 1 from
instrument_listings where update_date is null and delist_date is null
limit 1);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.11..0.12 rows=1 width=0) (actual time=0.026..0.028
rows=1 loops=1)
One-Time Filter: $0
InitPlan
-> Limit (cost=0.00..0.11 rows=1 width=0) (actual
time=0.022..0.022 rows=1 loops=1)
-> Seq Scan on instrument_listings (cost=0.00..17973.43
rows=164807 width=0) (actual time=0.017..0.017 rows=1 loops=1)
Filter: ((update_date IS NULL) AND (delist_date IS NULL))
Total runtime: 0.063 ms
(7 rows)

Time: 0.768 ms
proddb=>

The exists version is over 1000x faster (and the only reason it's not
more studly is that I'm working on the table as we speak, so it's all in
memory).

As a general rule in postgres, don't do count(*) unless you really mean it.

Brian

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 7 of 7 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJan 2, '08 at 3:29p
activeJan 3, '08 at 3:37p
posts7
users7
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase