X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A0B1B53917 for ; Tue, 12 Apr 2005 03:33:21 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59446-01 for ; Tue, 12 Apr 2005 06:33:11 +0000 (GMT) Received: from mx2.aiidatapro.com (ns.aiidatapro.com [195.47.193.1]) by svr1.postgresql.org (Postfix) with SMTP id AABB653811 for ; Tue, 12 Apr 2005 03:33:09 -0300 (ADT) Received: (qmail 13341 invoked by uid 1001); 12 Apr 2005 06:34:50 -0000 Received: from 192.168.1.10 by gruhcho (envelope-from , uid 64011) with qmail-scanner-1.25st (clamdscan: 0.83/761. spamassassin: 3.0.2. perlscan: 1.25st. Clear:RC:1(192.168.1.10):SA:0(-104.1/3.0):. Processed in 0.76857 secs); 12 Apr 2005 06:34:50 -0000 X-Qmail-Scanner-Mail-From: kr@aiidatapro.com via gruhcho X-Qmail-Scanner: 1.25st (Clear:RC:1(192.168.1.10):SA:0(-104.1/3.0):. Processed in 0.76857 secs Process 13324) Received: from unknown (HELO mailserver.aiidatapro.com) (192.168.1.10) by gruhcho.aiidatapro.com with SMTP; 12 Apr 2005 06:34:49 -0000 Received: (qmail 27859 invoked from network); 12 Apr 2005 06:32:37 -0000 Received: from unknown (HELO adpdev1.aiidatapro.net) (192.168.1.35) by zmei.aiidatapro.com with SMTP; 12 Apr 2005 06:32:37 -0000 From: Krasimir Dimitrov Reply-To: Krasimir Dimitrov Organization: AiiDatapro LTD To: "Andrus Moor" Subject: Re: Query runs very slowly in Postgres, but very fast in other DBMS Date: Tue, 12 Apr 2005 09:33:16 +0300 User-Agent: KMail/1.7.2 References: In-Reply-To: Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset="windows-1251" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200504120933.17457.kr@aiidatapro.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.785 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/160 X-Sequence-Number: 21053 try this query : SELECT rid.dokumnr as d1 ,dok.dokumnr as d2 FROM rid left join dok on rid.dokumnr = dok.dokumnr where dok.dokumnr is null; > Tables: > > CREATE TABLE dok ( dokumnr NUMERIC(12), > CONSTRAINT dok_pkey PRIMARY KEY (dokumnr) ); > CREATE TABLE rid ( dokumnr NUMERIC(12) ); > CREATE INDEX rid_dokumnr_idx ON rid (dokumnr); > > Query: > > SELECT dokumnr FROM rid WHERE dokumnr NOT IN > (select dokumnr FROM dok); > > runs VERY slowly in Postgres. It uses the following query plan: > > Seq Scan on rid (cost=0.00..28698461.07 rows=32201 width=14) > Filter: (NOT (subplan)) > SubPlan > -> Seq Scan on dok (cost=0.00..864.29 rows=10729 width=14) > > In Microsoft Visual FoxPro this query runs fast. FoxPro uses indexes speed > up the query by comparing bitmaps. > > Is it possible to speed up this query is Postgres ? How to force Postgres > to use indexes for this query ? > > Andrus > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- ________________________________________________________ Krasimir Dimitrov IT Department AII Data Processing Ltd., 16 Ivan Vazov Str, Sofia 1000, Bulgaria Phone: +359 2 9376 352 E-mail: kr@aiidatapro.com http://www.see-news.com