X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 66A8B11F695F
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue,  4 Apr 2006 17:00:20 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 96306-04
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue,  4 Apr 2006 17:00:19 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mailserver.sandvine.com (sandvine.com [199.243.201.138])
	by postgresql.org (Postfix) with ESMTP id C77C011F603C
	for <pgsql-performance@postgresql.org>;
	Tue,  4 Apr 2006 17:00:15 -0300 (ADT)
X-MimeOLE: Produced By Microsoft Exchange V6.5
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Query runs too long for indexed tables
Date: Tue, 4 Apr 2006 16:00:13 -0400
Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C2BCC555@mailserver.sandvine.com>
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
Thread-Topic: [PERFORM] Query runs too long for indexed tables
Thread-Index: AcZYIN6PaDIsCtJ2S1eZKpjBV1ElnQAAS1sA
From: "Marc Morin" <marc@sandvine.com>
To: "Scott Marlowe" <smarlowe@g2switchworks.com>,
 "Andrus" <eetasoft@online.ee>
Cc: <pgsql-performance@postgresql.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120]
X-Spam-Score: 0.12
X-Spam-Level: 
X-Archive-Number: 200604/50
X-Sequence-Number: 18134

Wondering if

Update firma1.rid set toode=3Dnull where toode is not null and not
exists(select 1 from firma1.toode where toode=3Drid.toode);=20

Would be faster... Problem appears to be the seqscan of seqscan... No?

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org=20
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20
> Scott Marlowe
> Sent: Tuesday, April 04, 2006 3:49 PM
> To: Andrus
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Query runs too long for indexed tables
>=20
> On Tue, 2006-04-04 at 14:37, Andrus wrote:
> > I have relatively small tables (toode and rid) in fast server.
> > Both tables are indexed on toode field.
> >=20
> > Following query takes long time to run.
> > toode field type is char(20). It is difficult to change=20
> this field type.
> >=20
> > Any idea how to speed up this query ?
> >=20
> > UPDATE firma1.rid SET toode=3DNULL
> >        WHERE toode IS NOT NULL AND
> >        toode NOT IN (SELECT TOODE      FROM firma1.TOODE);
> >=20
> > Query returned successfully: 0 rows affected, 594813 ms=20
> execution time.
> >=20
> > explain window shows:
> >=20
> > Seq Scan on rid  (cost=3D2581.07..20862553.77 rows=3D51848 =
width=3D1207)
> >   Filter: ((toode IS NOT NULL) AND (NOT (subplan)))
> >   SubPlan
> >     ->  Materialize  (cost=3D2581.07..2944.41 rows=3D14734 =
width=3D84)
> >           ->  Seq Scan on toode  (cost=3D0.00..2350.34 rows=3D14734=20
> > width=3D84)
>=20
> Let me guess, you've updated it a lot and aren't familiar with Vacuum?
>=20
> run a vacuum full on your database.  schedule a vacuum (plain=20
> one) to run every so often (hours or days are a good interval=20
> for most folks)
>=20
> If that's NOT your problem, then please, let us know. =20
>=20
> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>=20