Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA47692 for ; Wed, 19 Jan 2000 13:07:38 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id NAA10415; Wed, 19 Jan 2000 13:07:27 -0500 (EST) To: Marten Feldtmann cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] index usage ... strange !? In-reply-to: <200001191746.SAA09643@feki.toppoint.de> References: <200001191746.SAA09643@feki.toppoint.de> Comments: In-reply-to Marten Feldtmann message dated "Wed, 19 Jan 2000 18:46:26 +0100" Date: Wed, 19 Jan 2000 13:07:26 -0500 Message-ID: <10412.948305246@sss.pgh.pa.us> From: Tom Lane Marten Feldtmann writes: > 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. You are right, this would be better done as a join, but the system is not currently smart enough to rewrite a sub-SELECT into a join. You have to do it yourself :-(. I think preserving the semantics of this exactly would require outer joins, which we don't yet have, but you could get close with DISTINCT: SELECT DISTINCT A.AO,A.AT,A.AV FROM P3AT A, P3AT B WHERE A.AO = B.AO AND ...; Both of these issues are on the TODO list, and probably will get addressed in a release or three... regards, tom lane