Hi all, I've been dealing with a problem for the past two days where a certain sql statement works 2 out of 5 times, and the other 3 times, it causes the machine (quad Xeon 2.8GHz + 792543232 bytes mem, linux kernel 2.4.26-custom, pg ver 7.3.4) to slow down, and finally grind to a halt. It looks like postgres gets itself into an insane loop, because no matter how much shared memory I give it, it uses it all, and then the kernel starts swapping.
I'm pretty sure it's not the kernel, because I've tried four different
2.4.2* stable kernels, and the same happens.
I've attached the query, and the functions used inside the query,
as well as the table structure and an explain. (I haven't been
able to get explain analyze)
It seems that when I replace the functions used in the query,
with the actual values returned by them (one date in each case),
the query runs in 10 seconds.
I did vacuum analyze, and reindex seemed to work at one
stage, but now it doesn't anymore.
Is there some limitation in using functions that I do not know
about, or is it a bug?
(It seems to be hanging on the max_fpp('''')
function call from inside the fpp_max_ms() function.)
Please help.
Kind Regards
Stefan
query.sql
Description: Binary data
=# EXPLAIN SELECT group_code::text AS group_code,
-# sku::text AS sku,
-# stktype_code::varchar(2) AS stktype_code,
-# brn_code::text AS brn_code,
-# SUM(overdue)::int4 AS overdue,
-# SUM(current)::int4 AS current,
-# SUM(future)::int4 AS future
-# FROM (
(# SELECT group_code,
(# sku,
(# stktype_code,
(# brn_code,
(# CASE WHEN to_date <= max_fpp_ms() THEN
(# SUM(out_qty)
(# ELSE 0
(# END AS overdue,
(# CASE WHEN to_date > max_fpp_ms()
(# AND to_date <= max_fpp_me() THEN
(# SUM(out_qty)
(# ELSE 0
(# END AS current,
(# CASE WHEN to_date > max_fpp_me() THEN
(# SUM(out_qty)
(# ELSE 0
(# END AS future
(# FROM gir_outstanding
(# GROUP BY group_code,
(# sku,
(# stktype_code,
(# brn_code,
(# to_date
(# ) AS sub
-# GROUP BY group_code,
-# sku,
-# stktype_code,
-# brn_code
-# ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Aggregate (cost=15880.41..16055.62 rows=876 width=44)
-> Group (cost=15880.41..15989.92 rows=8761 width=44)
-> Sort (cost=15880.41..15902.31 rows=8761 width=44)
Sort Key: group_code, sku, stktype_code, brn_code
-> Subquery Scan sub (cost=13335.57..15306.72 rows=8761 width=44)
-> Aggregate (cost=13335.57..15306.72 rows=8761 width=44)
-> Group (cost=13335.57..14649.67 rows=87607 width=44)
-> Sort (cost=13335.57..13554.58 rows=87607
width=44)
Sort Key: group_code, sku, stktype_code,
brn_code, to_date
-> Seq Scan on gir_outstanding
(cost=0.00..4687.07 rows=87607 width=44)
(10 rows)
functions.sql
Description: Binary data
=# \d gir_outstanding
Table "public.gir_outstanding"
Column | Type | Modifiers
----------------+-----------------------------+-----------
supp_code | text |
supp_name | text |
supp_brn | text |
ord_no | text |
due_date | timestamp without time zone |
to_date | timestamp without time zone |
group_code | text |
brn_code | text |
desc_short | text |
cluster_brn | text |
country_code | text |
req_doc_no | integer |
ops_code | text |
sku | text |
std_descr | text |
acde_code | text |
req_qty | double precision |
grv_qty | double precision |
skul_qty | double precision |
pref_date | timestamp without time zone |
skul_grv_qty | double precision |
out_qty | double precision |
skul_ord_cost | numeric(16,2) |
out_cost | numeric |
stktype_code | character varying(2) |
gir_type_code | text |
gir_type_descr | text |
Indexes: gir_oustanding_idx1 btree (cluster_brn, sku, stktype_code)
=# select count(1) from gir_outstanding;
count
-------
87607
(1 row)
=# \d fpp
Table "public.fpp"
Column | Type | Modifiers
---------+-----------------------------+-----------
code | text | not null
descr | text |
me_dte | timestamp without time zone |
cal_dte | timestamp without time zone |
gl_per | integer |
Indexes: fpp_pkey primary key btree (code),
fpp_uidx1 unique btree (code)
=# select count(1) from fpp;
count
-------
92
=# \d br_prof
Table "public.br_prof"
Column | Type | Modifiers
--------------------+-----------------------------+-----------
br_cde | text | not null
bprof_opened | timestamp without time zone |
fpp_cde | text |
bprof_avg_del_time | integer |
bprof_auto_err_log | boolean |
Indexes: br_prof_pkey primary key btree (br_cde)
=# select count(1) from br_prof;
count
-------
1
(1 row)
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
