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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
