Few questions about my slow query

From: webmaster <webmaster(at)tony(dot)cz>
To: pgsql-sql(at)postgresql(dot)org
Subject: Few questions about my slow query
Date: 1999-07-14 08:52:10
Message-ID: 99071410540300.00685@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)tony(dot)cz (++420659/321350)
ICQ: 38607210

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message webmaster 1999-07-14 09:12:47 Re: Few questions about my slow query
Previous Message Jim Rowan 1999-07-14 05:17:12 WHERE clause?