Ants Aasma <ants.aasma@xxxxxxxx> writes: > On Thu, Mar 1, 2012 at 6:40 PM, Daniele Varrazzo > <daniele.varrazzo@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
