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 EE80C11F61C8 for ; Tue, 4 Apr 2006 16:39:22 -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 95133-07 for ; Tue, 4 Apr 2006 16:39:21 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id CB5B511F6106 for ; Tue, 4 Apr 2006 16:39:20 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 7B80130F1B; Tue, 4 Apr 2006 21:39:20 +0200 (MET DST) From: "Andrus" X-Newsgroups: pgsql.performance Subject: Query runs too long for indexed tables Date: Tue, 4 Apr 2006 22:37:18 +0300 Organization: Hub.Org Networking Services Lines: 26 Message-ID: X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2527 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527 X-RFC2646: Format=Flowed; Original To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.128 required=5 tests=[AWL=0.128] X-Spam-Score: 0.128 X-Spam-Level: X-Archive-Number: 200604/48 X-Sequence-Number: 18132 I have relatively small tables (toode and rid) in fast server. Both tables are indexed on toode field. Following query takes long time to run. toode field type is char(20). It is difficult to change this field type. Any idea how to speed up this query ? UPDATE firma1.rid SET toode=NULL WHERE toode IS NOT NULL AND toode NOT IN (SELECT TOODE FROM firma1.TOODE); Query returned successfully: 0 rows affected, 594813 ms execution time. explain window shows: Seq Scan on rid (cost=2581.07..20862553.77 rows=51848 width=1207) Filter: ((toode IS NOT NULL) AND (NOT (subplan))) SubPlan -> Materialize (cost=2581.07..2944.41 rows=14734 width=84) -> Seq Scan on toode (cost=0.00..2350.34 rows=14734 width=84) Andrus.