Re: Query planner question - Mailing list pgsql-general

Ernest E Vogelsinger wrote:
> 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");
>
>

Hmmm... this seems to be something different from what it we started
with...
Where are all those indexes with the owid in the front?
And also the one on (dcid, dsid, drid, nxid) isn't there either...

Actually, with *this* set on indices, I don't see anything that can be
useful for your query at all - no wonder it goes for a seq scan :-)

BTW, you seem to have too many of them - perhaps, a little cleanup is in
order...
For example, the first two are totally unnecessary, because all the
cases in which either of them could be useful are covered by
"id_mdata_dowid" and  "id_mdata_dpid"

Also, in general, it might make sense to look into the selectivity of
all those combinations...
For example, how many different "pid" and "owid" values is it reasonable
to get among those rows for which all of "dcid","drid","dsid","nxid" are
identical? If not very many, it might make sense to replace all four of
"id_dictid_noid","id_mdata_dictid","id_mdata_dowid",id_mdata_dpid" with
just one index on ("dcid","drid","dsid","nxid","usg")

etc...

Dima