Received: from bender.toppoint.de (bender.toppoint.de [195.244.243.2]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA40315 for ; Wed, 19 Jan 2000 12:49:52 -0500 (EST) (envelope-from marten@feki.toppoint.de) Received: (from uucp@localhost) by bender.toppoint.de (8.9.3/8.9.3) id SAA14827; Wed, 19 Jan 2000 18:49:42 +0100 (MET) >Received: (from marten@localhost) by feki.toppoint.de (8.9.3/8.9.3/SuSE Linux 8.9.3-0.1) id SAA09643; Wed, 19 Jan 2000 18:46:26 +0100 From: Marten Feldtmann Message-Id: <200001191746.SAA09643@feki.toppoint.de> Subject: Re: [SQL] index usage ... strange !? In-Reply-To: <200001180653.HAA05729@feki.toppoint.de> from Marten Feldtmann at "Jan 18, 2000 07:53:33 am" To: Marten Feldtmann Date: Wed, 19 Jan 2000 18:46:26 +0100 (CET) CC: Tom Lane , pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL60 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII > > Therefore I have to rewrite the sql statement above to: > > SELECT P3AT.AV,P3AT.AT,P3AT.AV FROM P3AT WHERE > EXISTS( SELECT B.AO FROM P3AT as B WHERE B.AO=P3AT.AO AND ..... ) > Ok, indeed it was the wrong statement, therefore I rewrote it to: SELECT P3AT.AO,P3AT.AT,P3AT.AV FROM P3AT WHERE EXISTS (SELECT B.AO FROM P3AT AS B WHERE B.AO=P3AT.AO AND ....) There're indices on AO,AT,AV. Size of table about 23000 rows. He uses indices only for the statement within EXISTS, which seems to be pretty fast but for the outer SELECT statement he uses a sequential scan ... which brings the the statement down from 23 ms to 2000ms. Actually I don not understand it. The "B.AO=P3AT.AO" should create internally a join (?) and therefore he could use the index on AO in the outer select to create the result - but the seq scan seems to be wrong. Marten