Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 124D5101438B for ; Thu, 1 Mar 2012 17:18:29 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S3DOM-0001QB-1p for pgsql-performance@postgresql.org; Thu, 01 Mar 2012 21:18:27 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q21LI0jY017821; Thu, 1 Mar 2012 16:18:00 -0500 (EST) To: Ants Aasma cc: Daniele Varrazzo , pgsql-performance@postgresql.org Subject: Re: Bad estimation for "where field not in" In-reply-to: References: Comments: In-reply-to Ants Aasma message dated "Thu, 01 Mar 2012 19:53:07 +0200" Date: Thu, 01 Mar 2012 16:18:00 -0500 Message-ID: <17820.1330636680@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201203/30 X-Sequence-Number: 46480 Ants Aasma writes: > On Thu, Mar 1, 2012 at 6:40 PM, Daniele Varrazzo > wrote: >> Is this a known planner shortcoming or something unexpected, to be >> escalated to -bugs? Server version is 9.0.1. > The relevant code is in scalararraysel() function. It makes the > assumption that element wise comparisons are completely independent, > while the exact opposite is true. This has been this way since > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=290166f93404d8759f4bf60ef1732c8ba9a52785 > introduced it to version 8.2. > At least for equality and inequality ops it would be good to rework > the logic to aggregate with > s1 = s1 + s2 and s1 = s1 + s2 - 1 correspondingly. Yeah, I was about to make a similar proposal. In principle, when working with a constant array, we could de-dup the array elements and then arrive at an exact result ... but that seems like expensive overkill, and in particular it'd be penalizing intelligently-written queries (which wouldn't have dups in the array to start with) to benefit badly-written ones. So it seems like the right thing is for scalararraysel to (1) check if the operator is equality or inequality, and if so (2) just assume the array elements are all different and so the probabilities sum directly. If the operator is something else it's probably best to stick with the existing logic. We could probably also protect ourselves a bit more by noting if the sum gives an impossible result (probability > 1 or < 0) and falling back to the normal calculation in that case. regards, tom lane