Hello,
I've noticed some posts on hanging queries but haven't seen any
solutions yet so far.
Our problem is that about a week and a half ago we started to get some
queries that would (seemingly) never return (e.g., normally run in a
couple minutes, but after 2.5 hours, they were still running, the
process pushing the processor up to 99.9% active).
We are running Postgres 8.1.1 on Redhat 7.3 using Dell poweredge quad
processor boxes with 4 GB of memory. We have a main database that is
replicated via Sloney to a identical system.
Things we've tried so far:
We've stopped and restarted postgres and that didn't seem to help, we've
rebuilt all the indexes and that didn't seem to help either. We've
stopped replication between the boxes and that didn't do anything.
We've tried the queries on both the production and the replicated box,
and there is no difference in the queries (or query plans)
We do have another identical system that is a backup box (same type of
box, Postgres 8.1.1, Redhat 7.3, etc), and there, the query does
complete executing in a short time. We loaded up a current copy of the
production database and it still responded quickly.
Generally these queries, although not complicated, are on the more
complex side of our application. Second, they have been running up
until a few weeks ago.
Attached are an example query plan: Query.sql
The query plan from our production sever: QueryPlanBroke.txt
The working query plan from our backup server: QueryPlanWork.txt
What we found that has worked so far is to remove all the outer joins,
put the results into a temp table and then left join from the temp table
to get our results. Certainly this isn't a solution, but rather
something we have resorted to in a place or to as we limp along.
Any help would be greatly appreciated.
Thanks,
Chris Beecroft
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=129334.05..129334.06 rows=1 width=305)
Sort Key: (((uw.name_last)::text || ', '::text) || (uw.name_first)::text), o.job_title
-> Nested Loop (cost=30150.77..129334.04 rows=1 width=305)
-> Nested Loop (cost=30150.77..129329.51 rows=1 width=301)
-> Nested Loop (cost=30150.77..129325.02 rows=1 width=280)
-> Nested Loop (cost=30150.77..129320.54 rows=1 width=266)
-> Nested Loop (cost=30150.77..129316.64 rows=1 width=237)
Join Filter: ("outer".keyf_orderid = "inner".keyp_orderid)
-> Seq Scan on timecard t (cost=0.00..95010.50 rows=1 width=108)
Filter: ((week_ending >= '04/02/2006'::date) AND (week_ending <= '04/30/2006'::date))
-> Merge Right Join (cost=30150.77..34168.90 rows=10979 width=133)
Merge Cond: ("outer".keyp_departmentid = "inner".keyf_parentid)
-> Index Scan using department_pkey on department d2 (cost=0.00..3775.63 rows=84962 width=22)
-> Sort (cost=30150.77..30178.22 rows=10979 width=119)
Sort Key: d1.keyf_parentid
-> Merge Right Join (cost=25261.41..29413.95 rows=10979 width=119)
Merge Cond: ("outer".keyp_departmentid = "inner".keyf_departmentid)
-> Index Scan using department_pkey on department d1 (cost=0.00..3775.63 rows=84962 width=36)
-> Sort (cost=25261.41..25288.85 rows=10979 width=91)
Sort Key: o.keyf_departmentid
-> Merge Right Join (cost=21271.06..24524.58 rows=10979 width=91)
Merge Cond: ("outer".keyf_managerid = "inner".keyf_managerid)
-> Index Scan using users_managerid on users um (cost=0.00..10381.20 rows=141125 width=23) -> Sort (cost=21271.06..21298.50 rows=10979 width=76)
Sort Key: o.keyf_managerid
-> Merge Right Join (cost=14303.57..20534.23 rows=10979 width=76)
Merge Cond: ("outer".keyf_workerid = "inner".keyf_workerid)
-> Index Scan using users_workerid on users uw (cost=0.00..11586.52 rows=141125 width=23)
-> Sort (cost=14303.57..14331.02 rows=10979 width=57)
Sort Key: o.keyf_workerid
-> Bitmap Heap Scan on orders o (cost=82.43..13566.75 rows=10979 width=57)
Recheck Cond: (keyf_clientid = 8)
-> Bitmap Index Scan on orders_clientid_idx (cost=0.00..82.43 rows=10979 width=0)
Index Cond: (keyf_clientid = 8)
-> Index Scan using address_building_pkey on address_building ab (cost=0.00..3.89 rows=1 width=37)
Index Cond: ("outer".keyf_address_buildingid = ab.keyp_address_buildingid)
-> Index Scan using location_pkey on "location" l (cost=0.00..4.46 rows=1 width=22)
Index Cond: ("outer".keyf_locationid = l.keyp_locationid)
-> Index Scan using supplier_pkey on supplier s (cost=0.00..4.48 rows=1 width=29)
Index Cond: ("outer".keyf_supplierid = s.keyp_supplierid)
-> Index Scan using invoice_pkey on invoice i (cost=0.00..4.51 rows=1 width=8)
Index Cond: ("outer".keyf_invoiceid = i.keyp_invoiceid)
(42 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=125493.83..125498.35 rows=1810 width=306)
Sort Key: (((uw.name_last)::text || ', '::text) || (uw.name_first)::text), o.job_title
-> Hash Join (cost=30904.77..125395.89 rows=1810 width=306)
Hash Cond: ("outer".keyf_locationid = "inner".keyp_locationid)
-> Hash Join (cost=30827.73..125263.35 rows=1929 width=292)
Hash Cond: ("outer".keyf_invoiceid = "inner".keyp_invoiceid)
-> Hash Join (cost=30362.29..123695.58 rows=2001 width=288)
Hash Cond: ("outer".keyf_orderid = "inner".keyp_orderid)
-> Seq Scan on timecard t (cost=0.00..92465.02 rows=42412 width=108)
Filter: ((week_ending >= '2006-04-02'::date) AND (week_ending <= '2006-04-30'::date))
-> Hash (cost=30344.41..30344.41 rows=7154 width=184)
-> Hash Join (cost=25801.65..30344.41 rows=7154 width=184)
Hash Cond: ("outer".keyf_address_buildingid = "inner".keyp_address_buildingid)
-> Hash Join (cost=25572.65..29853.27 rows=7624 width=155)
Hash Cond: ("outer".keyf_supplierid = "inner".keyp_supplierid)
-> Merge Right Join (cost=25091.38..29151.62 rows=8236 width=133)
Merge Cond: ("outer".keyp_departmentid = "inner".keyf_parentid)
-> Index Scan using department_pkey on department d2 (cost=0.00..3725.11 rows=84901 width=22)
-> Sort (cost=25091.38..25111.97 rows=8236 width=119)
Sort Key: d1.keyf_parentid
-> Merge Right Join (cost=20494.95..24555.72 rows=8236 width=119)
Merge Cond: ("outer".keyp_departmentid = "inner".keyf_departmentid)
-> Index Scan using department_pkey on department d1 (cost=0.00..3725.11 rows=84901 width=36)
-> Sort (cost=20494.95..20515.54 rows=8236 width=91)
Sort Key: o.keyf_departmentid
-> Merge Right Join (cost=16935.57..19959.30 rows=8236 width=91)
Merge Cond: ("outer".keyf_managerid = "inner".keyf_managerid)
-> Index Scan using users_managerid on users um (cost=0.00..9993.43 rows=126903 width=23)
-> Sort (cost=16935.57..16956.16 rows=8236 width=76)
Sort Key: o.keyf_managerid
-> Merge Right Join (cost=11006.83..16399.91 rows=8236 width=76)
Merge Cond: ("outer".keyf_workerid = "inner".keyf_workerid)
-> Index Scan using users_workerid on users uw (cost=0.00..10067.22 rows=126903 width=23)
-> Sort (cost=11006.83..11027.42 rows=8236 width=57)
Sort Key: o.keyf_workerid
-> Bitmap Heap Scan on orders o (cost=48.83..10471.17 rows=8236 width=57)
Recheck Cond: (keyf_clientid = 8)
-> Bitmap Index Scan on orders_clientid_idx (cost=0.00..48.83 rows=8236 width=0)
Index Cond: (keyf_clientid = 8)
-> Hash (cost=449.62..449.62 rows=12662 width=30)
-> Seq Scan on supplier s (cost=0.00..449.62 rows=12662 width=30)
-> Hash (cost=205.40..205.40 rows=9440 width=37)
-> Seq Scan on address_building ab (cost=0.00..205.40 rows=9440 width=37)
-> Hash (cost=420.55..420.55 rows=17955 width=8)
-> Seq Scan on invoice i (cost=0.00..420.55 rows=17955 width=8)
-> Hash (cost=68.03..68.03 rows=3603 width=22)
-> Seq Scan on "location" l (cost=0.00..68.03 rows=3603 width=22)
(47 rows)
select
uw.keyf_workerid,
uw.name_last || ', ' || uw.name_first as worker,
um.name_last || ', ' || um.name_first as manager,
o.keyp_orderid as orderid,
o.job_title,
s.name_supplier,
l.city,
l.state,
ab.address1,
ab.address2,
ab.zip,
t.keyp_timecardid,
t.status,
t.week_ending as tc_we,
t.hours_regular,
t.hours_ot,
t.hours_dt,
t.hours_holiday,
t.expenses,
t.sales_tax,
t.adjustment,
t.amount_bill_client,
t.amount_pay_supplier,
i.keyp_invoiceid,
i.week_ending as invoice_we,
o.custom_field01 as "Business Area",
d1.department_number,
d1.name_department as "Home Cost Center",
d2.name_department as "Company Code"
from timecard t
left join invoice i on t.keyf_invoiceid = i.keyp_invoiceid,
supplier s, location l, address_building ab,
orders o
left join users uw on o.keyf_workerid = uw.keyf_workerid
left join users um on o.keyf_managerid = um.keyf_managerid
left join department d1 on o.keyf_departmentid = d1.keyp_departmentid
left join department d2 on d1.keyf_parentid = d2.keyp_departmentid
where
t.keyf_invoiceid = i.keyp_invoiceid and
t.keyf_orderid = o.keyp_orderid and
o.keyf_supplierid = s.keyp_supplierid and
o.keyf_locationid = l.keyp_locationid and
o.keyf_address_buildingid = ab.keyp_address_buildingid and
o.keyf_clientid = 8 and
t.week_ending >= '04/02/2006' and
t.week_ending <='04/30/2006'
order by 2, 5;
