Re: Query planner question

At 04:32 13.06.2003, Stephan Szabo said:
--------------------[snip]--------------------
>Odd. Given the above (with no data of course) on my 7.3.1 and 7.4 testing
>databases,
>create index rv222 on rv2_mdata(dcid, dsid, drid) where owid is null;
>EXPLAIN ANALYZE SELECT DISTINCT dcid,dsid,drid FROM rv2_mdata WHERE owid
>is null;
>
>gives me:
> QUERY PLAN
>----------------------------------------------------------------------------
>-------------------------------------------
> Unique (cost=0.00..17.09 rows=1 width=68) (actual time=0.02..0.02 rows=0
>loops=1)
> -> Index Scan using rv222 on rv2_mdata (cost=0.00..17.05 rows=5
>width=68) (actual time=0.01..0.01 rows=0 loops=1)
> Filter: (owid IS NULL)
> Total runtime: 0.34 msec
>
>I'd have expected that turning off seqscans would give something
>like that even with data.

Maybe some version issues (I'm running the outdated 7.2.1 here)?

>> Hmm, maybe I'm not enough DB developer but rather DB user to grasp the
>> reasons for this...
>
>IIRC, right now in general btree indexes are usable in clauses of the
>general form <col> <op> <value> and is built to be flexible for different
>types and different sets of <op>, but not really for things that don't fit
>that pattern. It's one of those things that'll probably get fixed
>if someone comes up with a good way to handle it (I don't think anyone
>likes the current situation)

So if I get you right the "IS NULL" predicate doesn't fit into "<op>"
"<value>"? Maybe they (== core ;->) could "invent" an "IS" operator, and a
"NULL" value; or simply an "IS NULL" operator taking no value?

If I have the time I'll try to understand the sources...

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