slow query with count(*)

Newsgroups	pgsql.general
Date	2016-03-22 00:19 -0700
Message-ID	<1bf81bb7-4d60-45d6-9b73-2e8aff34efd1@googlegroups.com> (permalink)
Subject	slow query with count(*)
From	Vishnu R Krishnan <vgrkrishnan@gmail.com>

Show all headers | View raw

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?

Back to pgsql.general | Previous | Next — Next in thread | Find similar

Thread

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

csiph-web