X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3830FD1B276 for ; Mon, 7 Jun 2004 08:35:41 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32529-08 for ; Mon, 7 Jun 2004 11:35:18 +0000 (GMT) Received: from ucsns.ucs.co.za (ucs.co.za [196.23.43.2]) by svr1.postgresql.org (Postfix) with ESMTP id 1F46ED1B238 for ; Mon, 7 Jun 2004 08:35:00 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by ucsns.ucs.co.za (Postfix) with ESMTP id 47FED2BDE8 for ; Mon, 7 Jun 2004 13:34:45 +0200 (SAST) Received: from ucsns.ucs.co.za ([127.0.0.1]) by localhost (ucsns.ucs.co.za [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20705-07 for ; Mon, 7 Jun 2004 13:34:16 +0200 (SAST) Received: from ucspost.ucs.co.za (mailgw1.ucs.co.za [196.23.43.253]) by ucsns.ucs.co.za (Postfix) with ESMTP id 19BCF2BDDF for ; Mon, 7 Jun 2004 13:34:16 +0200 (SAST) Received: from jhb.ucs.co.za (jhb.ucs.co.za [172.31.1.3]) by ucspost.ucs.co.za (Postfix) with ESMTP id E11C5D9F6A for ; Mon, 7 Jun 2004 13:34:15 +0200 (SAST) Received: from svb.ucs.co.za (svb.ucs.co.za [172.31.1.148]) by jhb.ucs.co.za (Postfix) with SMTP id B8E4297642 for ; Mon, 7 Jun 2004 13:34:15 +0200 (SAST) Date: Mon, 7 Jun 2004 13:34:15 +0200 From: Stef To: pgsql-performance@postgresql.org Subject: Postgres function use makes machine crash. Message-Id: <20040607133415.09a2ce2f@svb.ucs.co.za> X-Mailer: Sylpheed version 0.9.10claws (GTK+ 1.2.10; i386-pc-linux-gnu) User-Agent: sillypheed-claws (anti-aliased) X-Face: GFoC95e6r)_TTG>n~=uFLojP=O~4W@Ms]>:.DMm/')(z3\Mwj^XP@? Q:3";lD.OM1"^mDu}2NJ@US:)dO:U*iY5EM50&Tx. X-Operating-System: sid X-X-X: _-^-_ Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="Multipart=_Mon__7_Jun_2004_13_34_15_+0200_pHQJTKtA2k4B5Fq7" X-Virus-Scanned: by amavisd-new at ucs.co.za X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/58 X-Sequence-Number: 7116 This is a multi-part message in MIME format. --Multipart=_Mon__7_Jun_2004_13_34_15_+0200_pHQJTKtA2k4B5Fq7 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit 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 --Multipart=_Mon__7_Jun_2004_13_34_15_+0200_pHQJTKtA2k4B5Fq7 Content-Type: application/octet-stream; name="query.sql" Content-Disposition: attachment; filename="query.sql" Content-Transfer-Encoding: base64 Q1JFQVRFIFRBQkxFIGdpcl9vdXRzdGFuZGluZ19hZ2VkCkFTICgKU0VMRUNU ICAgIGdyb3VwX2NvZGU6OnRleHQgICAgICAgICAgICAgICAgICAgICAgICAg IEFTIGdyb3VwX2NvZGUsCiAgICAgICAgICBza3U6OnRleHQgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICBBUyBza3UsCiAgICAgICAgICBzdGt0 eXBlX2NvZGU6OnZhcmNoYXIoMikgICAgICAgICAgICAgICAgICBBUyBzdGt0 eXBlX2NvZGUsCiAgICAgICAgICBicm5fY29kZTo6dGV4dCAgICAgICAgICAg ICAgICAgICAgICAgICAgICBBUyBicm5fY29kZSwKICAgICAgICAgIFNVTShv dmVyZHVlKTo6aW50NCAgICAgICAgICAgICAgICAgICAgICAgIEFTIG92ZXJk dWUsCiAgICAgICAgICBTVU0oY3VycmVudCk6OmludDQgICAgICAgICAgICAg ICAgICAgICAgICBBUyBjdXJyZW50LAogICAgICAgICAgU1VNKGZ1dHVyZSk6 OmludDQgICAgICAgICAgICAgICAgICAgICAgICAgQVMgZnV0dXJlCkZST00g KApTRUxFQ1QgICAgZ3JvdXBfY29kZSwKICAgICAgICAgIHNrdSwKICAgICAg ICAgIHN0a3R5cGVfY29kZSwKICAgICAgICAgIGJybl9jb2RlLAogICAgICAg ICAgQ0FTRSBXSEVOIHRvX2RhdGUgPD0gbWF4X2ZwcF9tcygpIFRIRU4KICAg ICAgICAgICAgIFNVTShvdXRfcXR5KQogICAgICAgICAgRUxTRSAgMAogICAg ICAgICAgRU5EICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg IEFTIG92ZXJkdWUsCiAgICAgICAgICBDQVNFIFdIRU4gdG9fZGF0ZSA+IG1h eF9mcHBfbXMoKQogICAgICAgICAgQU5EIHRvX2RhdGUgPD0gbWF4X2ZwcF9t ZSgpIFRIRU4KICAgICAgICAgICAgIFNVTShvdXRfcXR5KQogICAgICAgICAg RUxTRSAwCiAgICAgICAgICBFTkQgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgQVMgY3VycmVudCwKICAgICAgICAgIENBU0UgV0hFTiB0 b19kYXRlID4gbWF4X2ZwcF9tZSgpIFRIRU4KICAgICAgICAgICAgIFNVTShv dXRfcXR5KQogICAgICAgICAgRUxTRSAwCiAgICAgICAgICBFTkQgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQVMgZnV0dXJlCkZST00g Z2lyX291dHN0YW5kaW5nCkdST1VQIEJZICBncm91cF9jb2RlLAogICAgICAg ICAgc2t1LAogICAgICAgICAgc3RrdHlwZV9jb2RlLAogICAgICAgICAgYnJu X2NvZGUsCiAgICAgICAgICB0b19kYXRlCikgQVMgc3ViCkdST1VQIEJZICBn cm91cF9jb2RlLAogICAgICAgICAgc2t1LAogICAgICAgICAgc3RrdHlwZV9j b2RlLAogICAgICAgICAgYnJuX2NvZGUKKTsK --Multipart=_Mon__7_Jun_2004_13_34_15_+0200_pHQJTKtA2k4B5Fq7 Content-Type: text/plain; name="explain.txt" Content-Disposition: attachment; filename="explain.txt" Content-Transfer-Encoding: 7bit =# 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) --Multipart=_Mon__7_Jun_2004_13_34_15_+0200_pHQJTKtA2k4B5Fq7 Content-Type: application/octet-stream; name="functions.sql" Content-Disposition: attachment; filename="functions.sql" Content-Transfer-Encoding: base64 Q1JFQVRFIEZVTkNUSU9OIG1heF9mcHAgKHRleHQpIFJFVFVSTlMgdGV4dAog ICAgQVMgJ3NlbGVjdCBmcHBfY2RlIGZyb20gYnJfcHJvZiAnCiAgICBMQU5H VUFHRSBzcWw7CgoKQ1JFQVRFIEZVTkNUSU9OIG1heF9mcHBfbWUgKCkgUkVU VVJOUyBkYXRlCiAgICBBUyAnCi8qIFRoaXMgZnVuY3Rpb24gcmV0dXJucyB0 aGUgY3VycmVudCBmcHAgbWUgZGF0ZSAgKi8Kc2VsZWN0IG1lX2R0ZTo6ZGF0 ZQpmcm9tIGZwcAp3aGVyZSBjb2RlID0gbWF4X2ZwcCgnJycnKQonCiAgICBM QU5HVUFHRSBzcWw7CgoKQ1JFQVRFIEZVTkNUSU9OIG1heF9mcHBfbXMgKCkg UkVUVVJOUyBkYXRlCiAgICBBUyAnCi8qIFRoaXMgZnVuY3Rpb24gcmV0dXJu cyB0aGUgY3VycmVudCBmcHAgbXMgZGF0ZSAgKi8Kc2VsZWN0IG1lX2R0ZTo6 ZGF0ZSAKZnJvbSAoCiAgIHNlbGVjdCBtYXgoY29kZSkgYXMgY29kZSAKZnJv bSBmcHAKd2hlcmUgY29kZSA8IG1heF9mcHAoJycnJykKCikgYXMgc3ViLCBm cHAgZgp3aGVyZSBmLmNvZGUgPSBzdWIuY29kZQoKJwogICAgTEFOR1VBR0Ug c3FsOwoK --Multipart=_Mon__7_Jun_2004_13_34_15_+0200_pHQJTKtA2k4B5Fq7 Content-Type: text/plain; name="structures.txt" Content-Disposition: attachment; filename="structures.txt" Content-Transfer-Encoding: 7bit =# \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) --Multipart=_Mon__7_Jun_2004_13_34_15_+0200_pHQJTKtA2k4B5Fq7--