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 6B41253365 for ; Tue, 12 Apr 2005 12:23:12 -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 31411-05 for ; Tue, 12 Apr 2005 15:23:06 +0000 (GMT) Received: from hq.ncbr.com (unknown [207.174.141.130]) by svr1.postgresql.org (Postfix) with ESMTP id 626B15321D for ; Tue, 12 Apr 2005 12:23:05 -0300 (ADT) Received: from enitz2 (unknown [216.183.117.62]) by hq.ncbr.com (Postfix) with SMTP id 099B3153562 for ; Tue, 12 Apr 2005 10:16:02 -0600 (MDT) Message-ID: <006001c53f72$f1c8af90$fb04030a@CCPlanet.Com> From: "Dan Feiveson" To: References: <200504120933.17457.kr@aiidatapro.com> Subject: Re: Query runs very slowly in Postgres, but very fast in other DBMS Date: Tue, 12 Apr 2005 09:18:56 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="windows-1251" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.085 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/173 X-Sequence-Number: 21066 You might also try: SELECT dokumnr FROM rid WHERE NOT EXISTS ( SELECT 'd' FROM dok WHERE dok.dokumnr = rid.dokumnr ); Dan Feiveson DataJoe LLC ----- Original Message ----- From: "Krasimir Dimitrov" To: "Andrus Moor" Cc: Sent: Tuesday, April 12, 2005 12:33 AM Subject: Re: [SQL] Query runs very slowly in Postgres, but very fast in other DBMS > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match