Re: Query planner question

On Fri, 13 Jun 2003, Ernest E Vogelsinger wrote:

> At 02:43 13.06.2003, Stephan Szabo said:
> --------------------[snip]--------------------
> >gather it doesn't use that index even when you set enable_seqscan=off as
> >well. Hmm, I've seen that work on simpler cases I think... Yeah, on a
> >simple table of ints I can get it to do just unique/index-scan. Hmm.
>
> It's not _that_ complicated - here's the complete layout:
>
> CREATE TABLE "rv2_mdata" (
> "rid" int4 DEFAULT nextval('rv2_mdata_id_seq') NOT NULL ,
> "pid" int4,
> "owid" int4,
> "ioid" int4,
> "dcid" varchar,
> "dsid" varchar,
> "drid" int4,
> "acl" text,
> "usg" int4,
> "idx" varchar,
> "env" int4,
> "nxid" int4,
> "ci" int4,
> "cd" numeric(21,6),
> "cr" float4,
> "cts" timestamptz,
> "cst" varchar,
> "ctx" text,
> "cbl" oid,
> CONSTRAINT "rv2_mdata_pkey" PRIMARY KEY ("rid")
> );
>
> CREATE INDEX "id_dictid_noid" ON "rv2_mdata" ("dcid","drid","dsid");
> CREATE INDEX "id_mdata_dictid" ON "rv2_mdata" ("dcid","drid","dsid","nxid");
> CREATE INDEX "id_mdata_dictid_dec" ON "rv2_mdata"
> ("cd","dcid","drid","dsid","nxid") WHERE usg & 1 = 1;
> CREATE INDEX "id_mdata_dictid_int" ON "rv2_mdata"
> ("ci","dcid","drid","dsid","nxid") WHERE usg & 2 = 2;
> CREATE INDEX "id_mdata_dictid_real" ON "rv2_mdata"
> ("cr","dcid","drid","dsid","nxid") WHERE usg & 4 = 4;
> CREATE INDEX "id_mdata_dictid_string" ON "rv2_mdata"
> ("cst","dcid","drid","dsid","nxid") WHERE usg & 8 = 8;
> CREATE INDEX "id_mdata_dictid_timestamp" ON "rv2_mdata"
> ("cts","dcid","drid","dsid","nxid") WHERE usg & 16 = 16;
> CREATE INDEX "id_mdata_dowid" ON "rv2_mdata"
> ("dcid","drid","dsid","nxid","owid","usg");
> CREATE INDEX "id_mdata_dpid" ON "rv2_mdata"
> ("dcid","drid","dsid","nxid","pid","usg");
> CREATE INDEX "id_mdata_ioid" ON "rv2_mdata" ("ioid","nxid","usg");
> CREATE INDEX "id_mdata_owid" ON "rv2_mdata" ("nxid","owid","usg");
> CREATE INDEX "id_mdata_pid" ON "rv2_mdata" ("nxid","pid","usg");

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.

> >> Makes perfectly sense since nulls can't be indexed *sigh*
> >>
> >> Anyone know why this decision has been taken?
> >
> >It's not the nulls precisely, it's the IS NULL predicate that doesn't
> >really fit into the mostly nicely flexible index system. :( There've been
> >discussions about this, I don't really remember details though.
>
> 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)