Re: Bug in query planer ?

Hi,

On Tuesday 31 January 2006 18:59, you wrote:
> Shouldn't the query planner be able to do the right thing without the
> index? Where does the magic 'rows=3D459' come from?

ok - I've spend some time reading the postgres sources now. qesel() is using
a selectivity of DEFAULT_EQ_SEL (0.005) for all expressions with functions.

Since our query has three such equals AND'ed this gives a selectivity of
0.000000125 instead of 0.9. That's causing postgres to create a query plan
which runs aprox. 8 hours instead of less then a second.

I've now created a combined expression index for my case so the query plann=
er
can check the selectivity. This is a huge overkill and there is a lot of
space for improvements..

As a last resort for such cases it would be good to be able to hardcode
selectivities in the SQL statements. Something like:

 SELECT ...
   FROM ...
  WHERE con.ccu_objid IN (...)
    AND cel.isActiv =3D 'Y'
    AND (     upper(coalesce(dev.isActiv,'Y')) =3D 'Y'
          AND upper(coalesce(dev.IsCommittedSP,'Y')) =3D 'Y'
          AND upper(coalesce(dev.IsCommittedCust,'Y')) =3D 'Y'
        ) WITH SELECTIVITY 0.9
    AND loc.shortName =3D '5195'

However, it would be great to have get_restriction_variable() and
get_attstatsslot() extended so they can pass the most common values
from the statistics cache thru expressions, as described in my earlier
mail.

yours,
 - clifford

--=20
: Clifford Wolf            =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Tel +=
43-1-8178292-00 :
: LINBIT Information Technologies GmbH =A0 =A0 =A0 =A0 =A0Fax +43-1-8178292=
-82 :
: Sch=F6nbrunnerstr 244, 1120 Vienna, Austria =A0 =A0http://www.linbit.com :