pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: webmaster <webmaster@tony.cz>
To: pgsql-sql@postgresql.org
Subject: Few questions about my slow query
Date: Wed, 14 Jul 1999 10:52:10 +0200
Message-ID: <99071410540300.00685@localhost.localdomain> (raw)

Hi, I'm returning to postgresql performance problem, while I'm trying a lot
to understand way pg is performing queries I need your help... So I have two
tables:

refs (...some fields... , key is id_tit char(7) - indexed with default method);
temp_kat (...some fields..., key is id_ref char(7) - indexed with default
method);

Refs contains 80000 recs, temp_kat contains 5500 recs and all records from
temp_kat have a matching key value in refs... When I need "mark" records in
table refs (set the field exist for records existing also in temp_kat) I do
following query:

update refs set exist='true' where temp_kat.id_ref=refs.id_tit;

This query takes a lot of time - I don't know exactly how long because I had
to leave my office after 1.5 hour and the query was still running. I think
it's too long... Before executing I increased shared buffers to 1024 and both
tables are vacuumized. And I'm running the RedHat5.1 box on Celeron 266 with
64MB ram.

So the questions are:

1. Did I reach the maximum possible performance or should I rewrite query some
other way? 

2. Could I start postgres with some diff. options for ex. with the feature of
delayed flushing? My pg is 6.4.2 and I saw here there is a possibility to
run it at some diff. mode but I don't know how (no docs about it) and don't
know if it's only pg 6.5 feature

3. Should I change the indexing method from default one to some other? 

Many thanks for helping me; believe me that I'm not lazy to study but there is
not so much docs about the internal pg behaviour so I can only ask you...
--
Michal Samek, Tony distribuce s.r.o.
webmaster@tony.cz  (++420659/321350)
ICQ: 38607210



view thread (3+ messages)  latest in thread

Message-ID: <99071410540300.00685@localhost.localdomain>
Permalink:  ../99071410540300.00685@localhost.localdomain/
Also on:    postgresql.org/message-id/99071410540300.00685@localhost.localdomain

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: webmaster@tony.cz
  Subject: Re: Few questions about my slow query
  In-Reply-To: <99071410540300.00685@localhost.localdomain>
  References: <99071410540300.00685@localhost.localdomain>

* 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