slow query with count(*)

[Vishnu R Krishnan]

explain analyze SELECT COUNT(*) FROM "customers" WHERE (TRIM(telephone) = '06868787878' AND check_id = 41);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=12982.58..12982.59 rows=1 width=0) (actual time=200.452..200.453 rows=1 loops=1)
-> Bitmap Heap Scan on customers (cost=544.59..12982.21 rows=147 width=0) (actual time=14.555..200.447 rows=1 loops=1)
Recheck Cond: (check_id = 41)
Filter: (btrim((telephone)::text) = '06868787878'::text)
Rows Removed by Filter: 29394
-> Bitmap Index Scan on idx_customers_check_id (cost=0.00..544.55 rows=29350 width=0) (actual time=9.669..9.669 rows=29395 loops=1)
Index Cond: (check_id = 41)
Total runtime: 200.750 ms
(8 rows)

also sometimes its taking (293.6ms), is there any way to avoid this?

[m***@trialfire.com]

postgresql sucks at counting. See here https://wiki.postgresql.org/wiki/Slow_Counting

Running analyze on customers may speed things up if your stats are not upto date