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):
[EMAIL PROTECTED]> 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));
[EMAIL PROTECTED]> 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)
[EMAIL PROTECTED]> 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;
[EMAIL PROTECTED]> 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)
[EMAIL PROTECTED]> 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:
[EMAIL PROTECTED]> 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));
[EMAIL PROTECTED]> 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)
[EMAIL PROTECTED]> 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
