Re: Query planner question - Mailing list pgsql-general

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)