Re: Query planner question - Mailing list pgsql-general

Ernest E Vogelsinger wrote:

>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)
>
>
Weird ....
Can you send the analyze output?

>
>
>>>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...
>
Even more weird!
Analyze?

>I'm inclined to
>force owid to being 0 instead of null so it may use the index approach.
>
May be a good idea, regardless - personally, I don't like this partial
indexes stuff, because, first, it is not standard, second, it wastes
space, and third it just makes life too damn complicated...
If it doesn't cause any problems (like, with 0 being a valid regular
value, or with foreign key/uniqueness  constraints, or outside code that
relies on nulls), you are better off using 0 (or -1 or whatever) instead
of null - and add a 'not null' on the column definition while you are at
it, so that you don't get those zeroes and null mixed together :-)

>
>Question - how do other databases handle this? Oracle, and MSSQL (as I mean
>"real" databases, not semi-pros like MySQL, or nadas like Access ;->)
>
>
I don't know much (well... anything) about MSSQL...
And I haven't used oracle since, I believe v. 8... and it was doing the
same thing back then.
I know that Informix does it too ...

Nobody seems to like nulls for some reason. I was always wonderring - WHY???
Ancient people did not know 0, and then, after they finally invented it,
it took centuries more to get them to believe, that it was actually a
number...
The modern database engines seem to be handling nulls the same way :-)

>
>
>
>>>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...)
>
>
That's what I said - when it was not using the index, it was 28.6
seconds, and when you forced it to, it was 711.9...
So, it looks like the plan it chose on its own was better, right?

Dima