Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > pgsql.general > #6 > unrolled thread

slow query with count(*)

Back to article view | Back to pgsql.general

Contents

  slow query with count(*) Vishnu R Krishnan <vgrkrishnan@gmail.com> - 2016-03-22 00:19 -0700
    Re: slow query with count(*) mkremer@trialfire.com - 2016-06-07 12:53 -0700

#6 — slow query with count(*)

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?

[toc] | [next] | [standalone]

#8

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

[toc] | [prev] | [standalone]

Back to top | Article view | pgsql.general

csiph-web