pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: webmaster@tony.cz
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Few questions about my slow query
Date: Wed, 14 Jul 1999 10:41:51 -0400
Message-ID: <25532.931963311@sss.pgh.pa.us> (raw)
In-Reply-To: <99071410540300.00685@localhost.localdomain>

webmaster <webmaster@tony.cz> writes:
> 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

Hmm.  Are the id fields unique, or are there duplicates?  If there were,
say, 10 records in temp_kat with the same ID, then the corresponding
records in refs would each get updated 10 times.

It would help to see what EXPLAIN says about this query.  Since you have
indexes, a merge join would probably be the best strategy, but we can't
tell what's actually being used.

> 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

I think you should update to 6.5.  You might also want to start the
postmaster with "-o -F" to turn off fsync, but this assumes that you
have confidence in your OS, hardware, and power supply --- in the event
of a system crash, you have a greater risk of losing data with -F.

			regards, tom lane



view thread (3+ messages)

Message-ID: <25532.931963311@sss.pgh.pa.us>
Permalink:  ../25532.931963311@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/25532.931963311@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, webmaster@tony.cz
  Subject: Re: [SQL] Few questions about my slow query
  In-Reply-To: <25532.931963311@sss.pgh.pa.us>
  References: <99071410540300.00685@localhost.localdomain> <25532.931963311@sss.pgh.pa.us>

* 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