mardi 22 mars 2016

Single count query taking too much time postgresql

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), (1956.3ms), is there any way to avoid this?

Aucun commentaire:

Enregistrer un commentaire