X-Original-To: pgsql-performance@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id F22FD924F51 for ; Wed, 28 May 2003 21:46:17 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 93578-09 for ; Wed, 28 May 2003 21:46:14 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by developer.postgresql.org (Postfix) with ESMTP id ADE36924EFC for ; Wed, 28 May 2003 21:46:13 -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 h4T1kDU6013454; Wed, 28 May 2003 21:46:13 -0400 (EDT) To: Dave Tenny Cc: pgsql-performance@postgresql.org Subject: Re: IN list processing performance (yet again) In-reply-to: <3ED5603C.2020800@attbi.com> References: <3ED4B0E5.3050703@attbi.com> <9207.1054165441@sss.pgh.pa.us> <3ED5603C.2020800@attbi.com> Comments: In-reply-to Dave Tenny message dated "Wed, 28 May 2003 21:19:56 -0400" Date: Wed, 28 May 2003 21:46:13 -0400 Message-ID: <13453.1054172773@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/256 X-Sequence-Number: 2035 Dave Tenny writes: > > There is one very interesting thing in my test case though.  It > certainly seemed as if the
> parameterized statements were successfully using the index of the > freshly-created-but-unanalyzed table,
> or else the times on those queries would have been terrible too.  It > was only the IN list form
> of query that wasn't making correct use of the index.  How can the > planner recognize uniqueness for
> one case but not the other? The question is whether a seqscan will be faster than an indexscan; at some point there's less I/O involved to just scan the table once. If the planner doesn't know the index is unique then it's going to estimate a higher cost for the indexscan (due to more rows fetched) and there is some number of rows at which it will flip over to a seqscan. The same will happen even if it *does* know the index is unique, it's just that it will take more IN elements to make it happen. This is reasonable behavior IMHO, although whether the flip-over point is anywhere near the actual breakeven point on your hardware is anyone's guess. The cost estimates are often far enough off that it's not very close. regards, tom lane