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 C5A3011F61C0 for ; Tue, 4 Apr 2006 17:03:33 -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 38579-03 for ; Tue, 4 Apr 2006 17:03:32 -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 7420911F6934 for ; Tue, 4 Apr 2006 17:03:31 -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:03:30 -0400 Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C2BCC556@mailserver.sandvine.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] Query runs too long for indexed tables Thread-Index: AcZYH5vRJ2+Lzo2dTJ+lq4jVSstShgAAzRYQ From: "Marc Morin" To: "Andrus" , 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/51 X-Sequence-Number: 18135 Explain analyze would be nice ;-)=20 > -----Original Message----- > From: pgsql-performance-owner@postgresql.org=20 > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Andrus > Sent: Tuesday, April 04, 2006 3:37 PM > To: pgsql-performance@postgresql.org > Subject: [PERFORM] Query runs too long for indexed tables >=20 > 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 this=20 > 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=20 > rows=3D14734 width=3D84) >=20 >=20 > Andrus.=20 >=20 >=20 >=20 > ---------------------------(end of=20 > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >=20