Re: Query planner question
От | Ernest E Vogelsinger |
---|---|
Тема | Re: Query planner question |
Дата | |
Msg-id | 5.1.1.6.2.20030613025159.041c7058@mail.vogelsinger.at обсуждение исходный текст |
Ответ на | Re: Query planner question (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
Ответы |
Re: Query planner question
(Stephan Szabo <sszabo@megazone23.bigpanda.com>)
Re: Query planner question (Dima Tkach <dmitry@openratings.com>) |
Список | 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/
В списке pgsql-general по дате отправления: