Re: Query planner question - Mailing list pgsql-general

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/