From: Stef <svb@ucs.co.za>
To: pgsql-performance@postgresql.org
Subject: Postgres function use makes machine crash.
Date: Mon, 7 Jun 2004 13:34:15 +0200
Message-ID: <20040607133415.09a2ce2f@svb.ucs.co.za> (raw)
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
Attachments:
query.sql (application/octet-stream, 1.3K) downloadexplain.txt (text/plain, 2.4K) download
=# 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 (application/octet-stream, 546B) downloadstructures.txt (text/plain, 2.7K) download
=# \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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: svb@ucs.co.za
Subject: Re: Postgres function use makes machine crash.
In-Reply-To: <20040607133415.09a2ce2f@svb.ucs.co.za>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox