Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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 

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

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

Kind Regards

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


pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group