Query kills machine.

From: Stef <svb(at)ucs(dot)co(dot)za>
To: pgsql-performance(at)postgresql(dot)org
Subject: Query kills machine.
Date: 2004-08-24 08:32:40
Message-ID: 20040824103240.4d909777@svb.ucs.co.za
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi all,

I've attached all the query in query.sql

I'm using postgres 7.3.4 on Linux version 2.4.26-custom
( /proc/sys/vm/overcommit_memory = 0 this time )

free :
total used free shared buffers cached
Mem: 1810212 1767384 42828 0 5604 1663908
-/+ buffers/cache: 97872 1712340
Swap: 505912 131304 374608

After I rebuilt the database, the query was fast (28255.12 msec).
After one night's insertion into the tables that the query select from,
the query all of a sudden uses up all resources , and the kernel
starts swapping, and I haven't seen the query actually finish when
this happens. I did vacuum analyze AND reindex, but that doesn't
help.

I attached the explain analyze of the query before this happens, and
the explain plan from when it actually happens that the query doesn't finish.

The one noticeable difference, was that before, it used merge joins, and
after, it used hash joins.

When the query was slow, I tried to : set enable_hashjoin to off
for this query, and the query finished relatively fast again (316245.16 msec)

I attached the output of that explain analyze as well, as well as the postgres
settings.

Can anyone shed some light on what's happening here. I can't figure it out.

Kind Regards
Stefan

Attachment Content-Type Size
query.sql application/octet-stream 2.5 KB
explain_analyze_before.txt text/plain 4.8 KB
explain_after.txt text/plain 3.5 KB
explain_analyze_after_hashjoin_off.txt text/plain 5.0 KB
settings.txt text/plain 175 bytes
stmst_sku.sql application/octet-stream 4.0 KB
old_sku.sql application/octet-stream 263 bytes
master_sku_descr.sql application/octet-stream 1.4 KB
master_branch_descr.sql application/octet-stream 2.1 KB
gir_outstanding.sql application/octet-stream 1.5 KB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Christopher Kings-Lynne 2004-08-24 09:08:50 Re: Query kills machine.
Previous Message my ho 2004-08-24 05:08:42 Re: postgresql performance with multimedia