Re: Query planner question

At 02:43 13.06.2003, Stephan Szabo said:
--------------------[snip]--------------------
>Well, I'm considering the col IS NULL partial index as a hackaround. I

I thought you were referring to code patches... *shiver* ;-)

>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");

>> 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...

Thanks,

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