X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7B528476C13 for ; Fri, 16 May 2003 15:25:27 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 34758-10 for ; Fri, 16 May 2003 15:25:26 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id C9FC8476C0F for ; Fri, 16 May 2003 15:25:25 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h4GJPMU6029031; Fri, 16 May 2003 15:25:22 -0400 (EDT) To: Mike Winter Cc: pgsql-sql@postgresql.org Subject: Re: Efficiency Issue with "SELECT IN (..)" and "SELECT .. OR .." In-reply-to: References: Comments: In-reply-to Mike Winter message dated "Thu, 15 May 2003 15:44:29 -0600" Date: Fri, 16 May 2003 15:25:22 -0400 Message-ID: <29030.1053113122@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/356 X-Sequence-Number: 13521 Mike Winter writes: > If the "IN (1, 2, 3, 6, ..., n)" clause is big enough, the > database will actually throw an error saying "Recursive Depth > Exceeded" or something similar and not complete the query. Would it perhaps be saying "out of free buffers: time to abort!" ? If so, you're probably running into this bug, which was introduced (by me :-() in 7.3: http://archives.postgresql.org/pgsql-hackers/2003-03/msg00939.php There is a fix in place for 7.3.3. > It looks to me like the query parser is recursively calling > an index scan for each row in the 'IN' clause rather than just > doing one index scan that it seems it should be. It is performing one index search per target value, yes, but not recursively. That's what it's supposed to do. regards, tom lane