Re: Query planner question

At 23:57 12.06.2003, Dmitry Tkach said:
--------------------[snip]--------------------
>>1) Why chooses the planner to use id_owid (and not id_dowid as it would
>>seem logical) for the first query?
>>
>It is not smart enough to realize that owid doesn't matter for
>sorting/distinct purposes...
>I think, something like this should make it do what you expect:
>
>SELECT DISTINCT ON (owid,dcid,dsid,drid) dcid,dsid,drid FROM table WHERE
>owid = 1;

Nope - it's still using id_owid... but anyway that's the least problem as
it uses an index anyway (I can live with the 12 msec)

>>2) Why is NO index used for the second query, the only difference being in
>>the constraint value (owid is set vs. owid is null)?
>>
>Because the 'is null' operator is not indexable...
>There was a long discussion here a while ago that, as far as I remember,
>ended up with a resolution, that it is possible to make it indexable,
>and it would be nice to do ... when somebody gets a chance to actually
>implement it.
>
>As a wrokaround, you may consider using partial indexes - like:
>
>create id_dictid_noid on table (dcid, dsid, drid, nxid) where owid is null;

Tried this, to no avail - still using sequential scan... I'm inclined to
force owid to being 0 instead of null so it may use the index approach.

Question - how do other databases handle this? Oracle, and MSSQL (as I mean
"real" databases, not semi-pros like MySQL, or nadas like Access ;->)

>>3) Why does it use id_dictid for the second query when forced to, and not
>>id_owid or id_dowid?
>>
>Because having owid in the beginning doesn't help at all, and using
>id_dictid at least eliminates the need to sort.

This one makes sense.

>>4) What could I do to get the planner to use the index access method (apart
>>from setting enable_seqscan to off)?
>>
>>
>Nothing really :-)
>ANALYZE or SET STATISTICS to a higher value may help sometimes (when
>its refusal to use the index is due to misestimating the number of rows) ...
>In your case though, it does seem to pick the best available plan, so
>you, probably, don't want to force it use the index to begin with...

Not really (712 secs vs. 38 secs...)

Thanks!

--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/