Query running slow but was running fine before

[Keaton Adams <kadams@xxxxxxxxxxx>]

Title: Query running slow but was running fine before

PG: PostgreSQL 8.1.4
OS: RHEL 4.x

I have a set of queries on a production server that have been running fine for the past few months but as of last Friday started performing poorly.  I have isolated the problem down to a particular part that is common to all queries involved and have provided an example here of what the issue is.  There are multiple tables where this is a problem, not just one.

An analyze is run on the tables every day (even several times a day because they are updated very frequently) and a vacuum analyze is run on the weekends.  I also tried to run an analyze specifically on the customer_id column and then the product_id column but that didn’t help.

This one table listed is part of a UNION ALL that joins ten separate tables.  The “real” query is against the view name of kda_log_info.  I am working with just one of the tables within the view to help narrow down where the problem might be.

There may be an occasion when the product_id value is null, so this is how the query is written.  It uses the index on customer_id and does a filter on product_id.  The cost is high and the time it takes to scan the 964 rows is about one minute on average (where before it would return in sub-second time):

kadams@hostserver> more t4.sql
select count(*) 
from kda_log_info_2008w24 
where customer_id = 7767664 
AND (created >= '2008-06-01 00:00:00-06'::timestamp with time zone) 
AND (created < '2008-07-01 00:00:00-06'::timestamp with time zone) 
and ((product_id IS NULL) OR (product_id = 2070101833));


kadams@hostserver> psql -Upostgres -dkda_log -ft4.sql
                                                                                      QUERY PLAN                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=426537.34..426537.35 rows=1 width=0)
   ->  Index Scan using kda_log_info_cid_cre_dom_2008w24_idx on kda_log_info_2008w24  (cost=0.00..426535.75 rows=635 width=0)
         Index Cond: ((customer_id = 7767664) AND (created >= '2008-06-01 00:00:00-06'::timestamp with time zone) AND (created < '2008-07-01 00:00:00-06'::timestamp with time zone))
         Filter: ((product_id IS NULL) OR (product_id = 2070101833))
(4 rows)


kadams@hostserver> time psql -Upostgres -dkda_log -ft4.sql
 count 
-------
   964
(1 row)


real    0m54.810s
user    0m0.002s
sys    0m0.001s


kda_log=# select count(*) from kda_log_info_2008w24;
  count   
----------
 16356303
(1 row)


Index:
kda_log_info_cid_cre_dom_2008w24_idx btree (customer_id, created, "domain")



On the same dataset, if I eliminate the IS NULL and look for just the specific product_id the optimizer picks the index on the product_id column and filters on customer_id and scans the same 964 rows in 2 milliseconds:


select count(*)
from kda_log_info_2008w24
where customer_id = 7767664
AND (created >= '2008-06-01 00:00:00-06'::timestamp with time zone)
AND (created < '2008-07-01 00:00:00-06'::timestamp with time zone)
--and ((product_id IS NULL) OR (product_id = 2070101833))
and product_id = 2070101833;

kadams@hostserver> time psql -Upostgres -dkda_log -ft4.sql
                                                                                      QUERY PLAN                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1649.41..1649.42 rows=1 width=0)
   ->  Index Scan using kda_log_info_did_cre_2008w24_idx on kda_log_info_2008w24  (cost=0.00..1647.82 rows=635 width=0)
         Index Cond: ((product_id = 2070101833) AND (created >= '2008-06-01 00:00:00-06'::timestamp with time zone) AND (created < '2008-07-01 00:00:00-06'::timestamp with time zone))
         Filter: (customer_id = 7767664)
(4 rows)


kadams@hostserver> time psql -Upostgres -dkda_log -ft4.sql
 count 
-------
   964
(1 row)


real    0m0.207s
user    0m0.004s
sys    0m0.002s



There are currently no rows in the table where product_id is NULL:


mxl_log=# select count(*) from kda_log_info_2008w24 where product_id IS NULL;  
-------
     0
(1 row)


Index:
kda_log_info_did_cre_2008w24_idx" btree (domain_id, created)




As another comparison I ran this same query on a different database server with the same database layout (different data set, of course) which returns the same approximate number of rows for the given customer_id / product_id and it returns in sub-second time. This table has even more rows in it than on the server where performance has tanked:


kadams@hostserver2> more t2.sql
select count(*) 
from kda_log_info_2008w24 
where customer_id = 907 
AND (created >= '2008-06-01 00:00:00-06'::timestamp with time zone) 
AND (created < '2008-07-01 00:00:00-06'::timestamp with time zone) 
and ((product_id IS NULL) OR (product_id = 573351));


kadams@hostserver2> psql -Upostgres -dkda_log -ft2.sql
                                                                                    QUERY PLAN                                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2626.36..2626.37 rows=1 width=0)
   ->  Index Scan using kda_log_info_cid_cre_dom_2008w24_idx on kda_log_info_2008w24  (cost=0.00..2626.35 rows=1 width=0)
         Index Cond: ((customer_id = 907) AND (created >= '2008-06-01 00:00:00-06'::timestamp with time zone) AND (created < '2008-07-01 00:00:00-06'::timestamp with time zone))
         Filter: ((product_id IS NULL) OR (product_id = 573351))
(4 rows)


kadams@hostserver2> time psql -Upostgres -dmxl_log -ft2.sql
 count 
-------
   992
(1 row)


real    0m0.011s
user    0m0.002s
sys    0m0.002s


kda_log=# select count(*) from kda_log_info_2008w24;
  count   
----------
 21777364
(1 row)



I compared postgresql.conf file settings between the servers and they are identical.  

The physical servers are also the same configuration (8 CPU, 8 GB RAM, local fast-SCSI disk).

So, I have no idea why the query in the top of this email on this particular database server has gone from sub-second response to over a minute on average.  As only part of the overall query, times have gone from a few seconds to 15-20 minutes each, which is causing major problems for our users.

Any idea what else I can look at or do to resolve this problem?  Any additional information I can provide to help you guys figure this out?


PS: Yes, I know..... 8.1 is old..... We are migrating to 8.3.x in the fall.


Thanks for your help,

Keaton