Обсуждение: Query planner question
Maybe someone can shed a light on this:
I have a (test) table, populated with 2M rows:
rid int4, -- primary key
dcid varchar,
dsid varchar,
drid int4,
owid int4,
nxid int4,
usg int4,
--- something more that's not important
Indexes in question:
CREATE INDEX id_owid ON table (owid,nxid,usg) USING BTREE
CREATE INDEX id_dowid ON table (owid, dcid, dsid, drid, nxid, usg)
USING BTREE
CREATE INDEX id_dictid ON table (dcid, dsid, drid, nxid) USING BTREE
Test Run
VACUUM ANALYZE FULL;
VACUUM
EXPLAIN ANALYZE SELECT DISTINCT dcid,dsid,drid FROM table WHERE owid = 1;
NOTICE: QUERY PLAN:
Unique (cost=402.07..402.90 rows=11 width=20) (actual time=10.13..11.49
rows=512 loops=1)
-> Sort (cost=402.07..402.07 rows=111 width=20) (actual
time=10.13..10.46 rows=512 loops=1)
-> Index Scan using id_owid on table (cost=0.00..398.30 rows=111
width=20) (actual time=0.05..4.44 rows=512 loops=1)
Total runtime: 11.95 msec
EXPLAIN ANALYZE SELECT DISTINCT dcid,dsid,drid FROM table WHERE owid is null;
NOTICE: QUERY PLAN:
Unique (cost=126611.73..128034.59 rows=18971 width=20) (actual
time=27515.63..28359.88 rows=513 loops=1)
-> Sort (cost=126611.73..126611.73 rows=189714 width=20) (actual
time=27515.62..27792.29 rows=199131 loops=1)
-> Seq Scan on table (cost=0.00..106885.04 rows=189714
width=20) (actual time=18.76..16467.28 rows=199131 loops=1)
Total runtime: 28633.68 msec
SET enable_seqscan TO off;
SET VARIABLE
EXPLAIN SELECT DISTINCT dcid,dsid,drid FROM table WHERE owid is null;
-- this process uses 34M RSS!!!
NOTICE: QUERY PLAN:
Unique (cost=0.00..7887659.31 rows=18971 width=20) (actual
time=2.57..711940.78 rows=513 loops=1)
-> Index Scan using id_dictid on table (cost=0.00..7886236.46
rows=189714 width=20) (actual time=2.57..710482.07 rows=199131 loops=1)
Total runtime: 711942.76 msec
A couple of questions arise:
1) Why chooses the planner to use id_owid (and not id_dowid as it would
seem logical) for the first query?
2) Why is NO index used for the second query, the only difference being in
the constraint value (owid is set vs. owid is null)?
3) Why does it use id_dictid for the second query when forced to, and not
id_owid or id_dowid?
4) What could I do to get the planner to use the index access method (apart
from setting enable_seqscan to off)?
This is PostgreSQL 7.2.1 on a dual 1GHz / 1GB Dell server
SHMALL = 128MB
SHMMAX = 128MB
shared_buffers = 4000
sort_mem = 4000
Thanks for any pointers/insights/whatever makes sense,
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
Ernest E Vogelsinger wrote: >Maybe someone can shed a light on this: > >I have a (test) table, populated with 2M rows: > rid int4, -- primary key > dcid varchar, > dsid varchar, > drid int4, > owid int4, > nxid int4, > usg int4, > --- something more that's not important > >Indexes in question: > CREATE INDEX id_owid ON table (owid,nxid,usg) USING BTREE > CREATE INDEX id_dowid ON table (owid, dcid, dsid, drid, nxid, usg) >USING BTREE > CREATE INDEX id_dictid ON table (dcid, dsid, drid, nxid) USING BTREE > >Test Run > >VACUUM ANALYZE FULL; >VACUUM >EXPLAIN ANALYZE SELECT DISTINCT dcid,dsid,drid FROM table WHERE owid = 1; >NOTICE: QUERY PLAN: > Unique (cost=402.07..402.90 rows=11 width=20) (actual time=10.13..11.49 >rows=512 loops=1) > -> Sort (cost=402.07..402.07 rows=111 width=20) (actual >time=10.13..10.46 rows=512 loops=1) > -> Index Scan using id_owid on table (cost=0.00..398.30 rows=111 >width=20) (actual time=0.05..4.44 rows=512 loops=1) >Total runtime: 11.95 msec > >EXPLAIN ANALYZE SELECT DISTINCT dcid,dsid,drid FROM table WHERE owid is null; >NOTICE: QUERY PLAN: > Unique (cost=126611.73..128034.59 rows=18971 width=20) (actual >time=27515.63..28359.88 rows=513 loops=1) > -> Sort (cost=126611.73..126611.73 rows=189714 width=20) (actual >time=27515.62..27792.29 rows=199131 loops=1) > -> Seq Scan on table (cost=0.00..106885.04 rows=189714 >width=20) (actual time=18.76..16467.28 rows=199131 loops=1) >Total runtime: 28633.68 msec > >SET enable_seqscan TO off; >SET VARIABLE >EXPLAIN SELECT DISTINCT dcid,dsid,drid FROM table WHERE owid is null; >-- this process uses 34M RSS!!! >NOTICE: QUERY PLAN: > Unique (cost=0.00..7887659.31 rows=18971 width=20) (actual >time=2.57..711940.78 rows=513 loops=1) > -> Index Scan using id_dictid on table (cost=0.00..7886236.46 >rows=189714 width=20) (actual time=2.57..710482.07 rows=199131 loops=1) >Total runtime: 711942.76 msec > >A couple of questions arise: > >1) Why chooses the planner to use id_owid (and not id_dowid as it would >seem logical) for the first query? > It is not smart enough to realize that owid doesn't matter for sorting/distinct purposes... I think, something like this should make it do what you expect: SELECT DISTINCT ON (owid,dcid,dsid,drid) dcid,dsid,drid FROM table WHERE owid = 1; >2) Why is NO index used for the second query, the only difference being in >the constraint value (owid is set vs. owid is null)? > Because the 'is null' operator is not indexable... There was a long discussion here a while ago that, as far as I remember, ended up with a resolution, that it is possible to make it indexable, and it would be nice to do ... when somebody gets a chance to actually implement it. As a wrokaround, you may consider using partial indexes - like: create id_dictid_noid on table (dcid, dsid, drid, nxid) where owid is null; >3) Why does it use id_dictid for the second query when forced to, and not >id_owid or id_dowid? > Because having owid in the beginning doesn't help at all, and using id_dictid at least eliminates the need to sort. >4) What could I do to get the planner to use the index access method (apart >from setting enable_seqscan to off)? > > Nothing really :-) ANALYZE or SET STATISTICS to a higher value may help sometimes (when its refusal to use the index is due to misestimating the number of rows) ... In your case though, it does seem to pick the best available plan, so you, probably, don't want to force it use the index to begin with... I hope, it helps... Dima >This is PostgreSQL 7.2.1 on a dual 1GHz / 1GB Dell server >SHMALL = 128MB >SHMMAX = 128MB >shared_buffers = 4000 >sort_mem = 4000 > >Thanks for any pointers/insights/whatever makes sense, > > > >
On Thu, 12 Jun 2003, Ernest E Vogelsinger wrote: > > Maybe someone can shed a light on this: > > I have a (test) table, populated with 2M rows: > rid int4, -- primary key > dcid varchar, > dsid varchar, > drid int4, > owid int4, > nxid int4, > usg int4, > --- something more that's not important > > Indexes in question: > CREATE INDEX id_owid ON table (owid,nxid,usg) USING BTREE > CREATE INDEX id_dowid ON table (owid, dcid, dsid, drid, nxid, usg) > USING BTREE > CREATE INDEX id_dictid ON table (dcid, dsid, drid, nxid) USING BTREE > > Test Run > > VACUUM ANALYZE FULL; > VACUUM > EXPLAIN ANALYZE SELECT DISTINCT dcid,dsid,drid FROM table WHERE owid = 1; > NOTICE: QUERY PLAN: > Unique (cost=402.07..402.90 rows=11 width=20) (actual time=10.13..11.49 > rows=512 loops=1) > -> Sort (cost=402.07..402.07 rows=111 width=20) (actual > time=10.13..10.46 rows=512 loops=1) > -> Index Scan using id_owid on table (cost=0.00..398.30 rows=111 > width=20) (actual time=0.05..4.44 rows=512 loops=1) > Total runtime: 11.95 msec > > EXPLAIN ANALYZE SELECT DISTINCT dcid,dsid,drid FROM table WHERE owid is null; > NOTICE: QUERY PLAN: > Unique (cost=126611.73..128034.59 rows=18971 width=20) (actual > time=27515.63..28359.88 rows=513 loops=1) > -> Sort (cost=126611.73..126611.73 rows=189714 width=20) (actual > time=27515.62..27792.29 rows=199131 loops=1) > -> Seq Scan on table (cost=0.00..106885.04 rows=189714 > width=20) (actual time=18.76..16467.28 rows=199131 loops=1) > Total runtime: 28633.68 msec > > SET enable_seqscan TO off; > SET VARIABLE > EXPLAIN SELECT DISTINCT dcid,dsid,drid FROM table WHERE owid is null; > -- this process uses 34M RSS!!! > NOTICE: QUERY PLAN: > Unique (cost=0.00..7887659.31 rows=18971 width=20) (actual > time=2.57..711940.78 rows=513 loops=1) > -> Index Scan using id_dictid on table (cost=0.00..7886236.46 > rows=189714 width=20) (actual time=2.57..710482.07 rows=199131 loops=1) > Total runtime: 711942.76 msec > > A couple of questions arise: > > 1) Why chooses the planner to use id_owid (and not id_dowid as it would > seem logical) for the first query? I think that it doesn't entirely know that owid=1, sort by dcid, dsid, drid can be handled by that index. I think it's possible that if you added owid to the select list it might use id_dowid instead. I think this is similar to the issues with order by, conditions and index choice, which you may find useful information in the archives about) > 2) Why is NO index used for the second query, the only difference being in > the constraint value (owid is set vs. owid is null)? IS NULL is not considered an indexable condition currently (there are past discussions and hackarounds in the archives) > 3) Why does it use id_dictid for the second query when forced to, and not > id_owid or id_dowid? As for #2, it doesn't think it can use an index with owid in the front.
At 23:57 12.06.2003, Dmitry Tkach said:
--------------------[snip]--------------------
>>1) Why chooses the planner to use id_owid (and not id_dowid as it would
>>seem logical) for the first query?
>>
>It is not smart enough to realize that owid doesn't matter for
>sorting/distinct purposes...
>I think, something like this should make it do what you expect:
>
>SELECT DISTINCT ON (owid,dcid,dsid,drid) dcid,dsid,drid FROM table WHERE
>owid = 1;
Nope - it's still using id_owid... but anyway that's the least problem as
it uses an index anyway (I can live with the 12 msec)
>>2) Why is NO index used for the second query, the only difference being in
>>the constraint value (owid is set vs. owid is null)?
>>
>Because the 'is null' operator is not indexable...
>There was a long discussion here a while ago that, as far as I remember,
>ended up with a resolution, that it is possible to make it indexable,
>and it would be nice to do ... when somebody gets a chance to actually
>implement it.
>
>As a wrokaround, you may consider using partial indexes - like:
>
>create id_dictid_noid on table (dcid, dsid, drid, nxid) where owid is null;
Tried this, to no avail - still using sequential scan... I'm inclined to
force owid to being 0 instead of null so it may use the index approach.
Question - how do other databases handle this? Oracle, and MSSQL (as I mean
"real" databases, not semi-pros like MySQL, or nadas like Access ;->)
>>3) Why does it use id_dictid for the second query when forced to, and not
>>id_owid or id_dowid?
>>
>Because having owid in the beginning doesn't help at all, and using
>id_dictid at least eliminates the need to sort.
This one makes sense.
>>4) What could I do to get the planner to use the index access method (apart
>>from setting enable_seqscan to off)?
>>
>>
>Nothing really :-)
>ANALYZE or SET STATISTICS to a higher value may help sometimes (when
>its refusal to use the index is due to misestimating the number of rows) ...
>In your case though, it does seem to pick the best available plan, so
>you, probably, don't want to force it use the index to begin with...
Not really (712 secs vs. 38 secs...)
Thanks!
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
At 23:56 12.06.2003, Stephan Szabo said:
--------------------[snip]--------------------
>I think that it doesn't entirely know that owid=1, sort by dcid, dsid,
>drid can be handled by that index. I think it's possible that if you
>added owid to the select list it might use id_dowid instead. I think this
>is similar to the issues with order by, conditions and index choice, which
>you may find useful information in the archives about)
Nope, still uses the wrong index, but as I said to Dimitry that's my least
problem ;-)
>> 2) Why is NO index used for the second query, the only difference being in
>> the constraint value (owid is set vs. owid is null)?
>
>IS NULL is not considered an indexable condition currently (there are past
>discussions and hackarounds in the archives)
Hmm - I'm not into hackarounds on a production server, really. I'll rather
modify the approach the application takes.
>> 3) Why does it use id_dictid for the second query when forced to, and not
>> id_owid or id_dowid?
>
>As for #2, it doesn't think it can use an index with owid in the front.
Makes perfectly sense since nulls can't be indexed *sigh*
Anyone know why this decision has been taken?
Thanks for your insight, guys :)
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
Ernest E Vogelsinger wrote: >At 23:57 12.06.2003, Dmitry Tkach said: >--------------------[snip]-------------------- > > >>>1) Why chooses the planner to use id_owid (and not id_dowid as it would >>>seem logical) for the first query? >>> >>> >>> >>It is not smart enough to realize that owid doesn't matter for >>sorting/distinct purposes... >>I think, something like this should make it do what you expect: >> >>SELECT DISTINCT ON (owid,dcid,dsid,drid) dcid,dsid,drid FROM table WHERE >>owid = 1; >> >> > >Nope - it's still using id_owid... but anyway that's the least problem as >it uses an index anyway (I can live with the 12 msec) > > Weird .... Can you send the analyze output? > > >>>2) Why is NO index used for the second query, the only difference being in >>>the constraint value (owid is set vs. owid is null)? >>> >>> >>> >>Because the 'is null' operator is not indexable... >>There was a long discussion here a while ago that, as far as I remember, >>ended up with a resolution, that it is possible to make it indexable, >>and it would be nice to do ... when somebody gets a chance to actually >>implement it. >> >>As a wrokaround, you may consider using partial indexes - like: >> >>create id_dictid_noid on table (dcid, dsid, drid, nxid) where owid is null; >> >> > >Tried this, to no avail - still using sequential scan... > Even more weird! Analyze? >I'm inclined to >force owid to being 0 instead of null so it may use the index approach. > May be a good idea, regardless - personally, I don't like this partial indexes stuff, because, first, it is not standard, second, it wastes space, and third it just makes life too damn complicated... If it doesn't cause any problems (like, with 0 being a valid regular value, or with foreign key/uniqueness constraints, or outside code that relies on nulls), you are better off using 0 (or -1 or whatever) instead of null - and add a 'not null' on the column definition while you are at it, so that you don't get those zeroes and null mixed together :-) > >Question - how do other databases handle this? Oracle, and MSSQL (as I mean >"real" databases, not semi-pros like MySQL, or nadas like Access ;->) > > I don't know much (well... anything) about MSSQL... And I haven't used oracle since, I believe v. 8... and it was doing the same thing back then. I know that Informix does it too ... Nobody seems to like nulls for some reason. I was always wonderring - WHY??? Ancient people did not know 0, and then, after they finally invented it, it took centuries more to get them to believe, that it was actually a number... The modern database engines seem to be handling nulls the same way :-) > > > >>>4) What could I do to get the planner to use the index access method (apart >>> >>> >>>from setting enable_seqscan to off)? >> >> >>> >>> >>> >>> >>Nothing really :-) >>ANALYZE or SET STATISTICS to a higher value may help sometimes (when >>its refusal to use the index is due to misestimating the number of rows) ... >>In your case though, it does seem to pick the best available plan, so >>you, probably, don't want to force it use the index to begin with... >> >> > >Not really (712 secs vs. 38 secs...) > > That's what I said - when it was not using the index, it was 28.6 seconds, and when you forced it to, it was 711.9... So, it looks like the plan it chose on its own was better, right? Dima
On Fri, 13 Jun 2003, Ernest E Vogelsinger wrote: > At 23:56 12.06.2003, Stephan Szabo said: > --------------------[snip]-------------------- > Nope, still uses the wrong index, but as I said to Dimitry that's my least > problem ;-) > > >> 2) Why is NO index used for the second query, the only difference being in > >> the constraint value (owid is set vs. owid is null)? > > > >IS NULL is not considered an indexable condition currently (there are past > >discussions and hackarounds in the archives) > > Hmm - I'm not into hackarounds on a production server, really. I'll rather > modify the approach the application takes. Well, I'm considering the col IS NULL partial index as a hackaround. I 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. > >> 3) Why does it use id_dictid for the second query when forced to, and not > >> id_owid or id_dowid? > > > >As for #2, it doesn't think it can use an index with owid in the front. > > 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.
At 02:12 13.06.2003, Dmitry Tkach said:
--------------------[snip]--------------------
>>Nope - it's still using id_owid... but anyway that's the least problem as
>>it uses an index anyway (I can live with the 12 msec)
>>
>>
>Weird ....
>Can you send the analyze output?
explain analyze select distinct on (owid,dcid,dsid,drid) dcid,dsid,drid
from table where owid = 2;
NOTICE: QUERY PLAN:
Unique (cost=385.76..386.83 rows=11 width=24) (actual time=11.61..12.98
rows=512 loops=1)
-> Sort (cost=385.76..385.76 rows=106 width=24) (actual
time=11.60..11.93 rows=512 loops=1)
-> Index Scan using id_owid on table (cost=0.00..382.18 rows=106
width=24) (actual time=0.05..3.43 rows=512 loops=1)
Total runtime: 14.00 msec
EXPLAIN
>>>create id_dictid_noid on table (dcid, dsid, drid, nxid) where owid is null;
>>
>>Tried this, to no avail - still using sequential scan...
>>
>Even more weird!
>Analyze?
create index id_dictid_noid on table (dcid,dsid,drid) where owid is null;
CREATE
explain select distinct dcid,dsid,drid from table where owid is null;
NOTICE: QUERY PLAN:
Unique (cost=128002.92..129507.00 rows=20054 width=20)
-> Sort (cost=128002.92..128002.92 rows=200543 width=20)
-> Seq Scan on table (cost=0.00..106951.52 rows=200543 width=20)
EXPLAIN
>May be a good idea, regardless - personally, I don't like this partial
>indexes stuff, because, first, it is not standard, second, it wastes
>space, and third it just makes life too damn complicated...
I'm using them (in the same application, and the same table) with good
success. This table has a number of columns where only one is used of the
whole set (this is used for an object oriented DB kernel where scalar data
types map to their DB type counterparts). A control bitfield points out
which columns are to be considered for a particular row, and it helps to
keep the index activity at a necessary minimum.
>If it doesn't cause any problems (like, with 0 being a valid regular
>value, or with foreign key/uniqueness constraints, or outside code that
>relies on nulls), you are better off using 0 (or -1 or whatever) instead
>of null - and add a 'not null' on the column definition while you are at
>it, so that you don't get those zeroes and null mixed together :-)
Actually I can't use any number than null since owid (owner ID) has a
foreign key pointing to the owning row, or is null if there's none. So I
simply set up a "helper row" that "owns" those rows that are in fact
unowned - the row ID being a reserved value (I'm using -1 as you
suggested). Only the application has to know when querying and writing
rows, and that's a single place. ACK and done ;-)
>Nobody seems to like nulls for some reason. I was always wonderring - WHY???
>Ancient people did not know 0, and then, after they finally invented it,
>it took centuries more to get them to believe, that it was actually a
>number...
>The modern database engines seem to be handling nulls the same way :-)
I always recognized "null" as a very important value (non-value, better
then). I believe it should be indexable.
>So, it looks like the plan it chose on its own was better, right?
Absolutely,
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
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/
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)
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
At 04:32 13.06.2003, Stephan Szabo said:
--------------------[snip]--------------------
>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.
Maybe some version issues (I'm running the outdated 7.2.1 here)?
>> 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)
So if I get you right the "IS NULL" predicate doesn't fit into "<op>"
"<value>"? Maybe they (== core ;->) could "invent" an "IS" operator, and a
"NULL" value; or simply an "IS NULL" operator taking no value?
If I have the time I'll try to understand the sources...
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
At 03:46 13.06.2003, Dima Tkach said:
--------------------[snip]--------------------
>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...
Right - I was using PGExplorer to list the table structure, and this
doesn't really seem to do a good job (simply mixed the column sequences
around)... I also had to manually add the constraints for the partial
indices and missed the "noid" constraint as well...
FWIW Here's the psql \d output:
test=# \d rv2_mdata \d id_mdata_dictid \d id_mdata_dictid_dec \d
id_mdata_dictid_int \d id_mdata_dictid_real \d id_mdata_dictid_string \d
id_mdata_dictid_timestamp \d id_mdata_dowid \d id_mdata_dpid \d
id_mdata_ioid \d id_mdata_owid \d id_mdata_pid \d rv2_mdata_pkey
Table "rv2_mdata"
Column | Type | Modifiers
--------+--------------------------+----------------------------------------
------------
rid | integer | not null default
nextval('rv2_mdata_id_seq'::text)
pid | integer |
owid | integer |
ioid | integer |
dcid | character varying |
dsid | character varying |
drid | integer |
usg | integer |
idx | character varying |
env | integer |
nxid | integer |
ci | integer |
cd | numeric(21,6) |
cr | real |
cts | timestamp with time zone |
cst | character varying |
ctx | text |
cbl | oid |
acl | text |
Indexes: id_mdata_dictid,
id_mdata_dictid_dec,
id_mdata_dictid_int,
id_mdata_dictid_real,
id_mdata_dictid_string,
id_mdata_dictid_timestamp,
id_mdata_dowid,
id_mdata_dpid,
id_mdata_ioid,
id_mdata_owid,
id_mdata_pid
Primary key: rv2_mdata_pkey
Index "id_mdata_dictid"
Column | Type
--------+-------------------
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
btree
Index "id_mdata_dictid_dec"
Column | Type
--------+-------------------
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
cd | numeric(21,6)
btree
Index predicate: ((usg & 2) = 2)
Index "id_mdata_dictid_int"
Column | Type
--------+-------------------
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
ci | integer
btree
Index predicate: ((usg & 1) = 1)
Index "id_mdata_dictid_real"
Column | Type
--------+-------------------
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
cr | real
btree
Index predicate: ((usg & 4) = 4)
Index "id_mdata_dictid_string"
Column | Type
--------+-------------------
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
cst | character varying
btree
Index predicate: ((usg & 16) = 16)
Index "id_mdata_dictid_timestamp"
Column | Type
--------+--------------------------
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
cts | timestamp with time zone
btree
Index predicate: ((usg & 8) = 8)
Index "id_mdata_dowid"
Column | Type
--------+-------------------
owid | integer
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
cd | numeric(21,6)
btree
Index predicate: ((usg & 2) = 2)
Index "id_mdata_dictid_int"
Column | Type
--------+-------------------
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
ci | integer
btree
Index predicate: ((usg & 1) = 1)
Index "id_mdata_dictid_real"
Column | Type
--------+-------------------
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
cr | real
btree
Index predicate: ((usg & 4) = 4)
Index "id_mdata_dictid_string"
Column | Type
--------+-------------------
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
cst | character varying
btree
Index predicate: ((usg & 16) = 16)
Index "id_mdata_dictid_timestamp"
Column | Type
--------+--------------------------
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
cts | timestamp with time zone
btree
Index predicate: ((usg & 8) = 8)
Index "id_mdata_dowid"
Column | Type
--------+-------------------
owid | integer
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
usg | integer
btree
Index "id_mdata_dpid"
Column | Type
--------+-------------------
pid | integer
dcid | character varying
dsid | character varying
drid | integer
nxid | integer
usg | integer
btree
Index "id_mdata_ioid"
Column | Type
--------+---------
ioid | integer
nxid | integer
usg | integer
btree
Index predicate: (ioid IS NOT NULL)
Index "id_mdata_owid"
Column | Type
--------+---------
owid | integer
nxid | integer
usg | integer
btree
Index "id_mdata_pid"
Column | Type
--------+---------
pid | integer
nxid | integer
usg | integer
btree
Index "rv2_mdata_pkey"
Column | Type
--------+---------
rid | integer
unique btree (primary key)
>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 :-)
right, I should stick to my SSH shell directly on the server ;-)
>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"
Now that I know that NULL is not indexable I will drop some of them - at
least the
key on ioid would never be used as I found. Since the predicate is "ioid IS
NOT NULL", any query giving "ioid = ###" will not match the predicate (at
least my old 7.2.1 doesn't use it...) I can also drop id_mdata_owid since
id_mdata_dowid should usable for such queries as well.
>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")
There will be a lot of these combinations. For any given owid value there
will be up to 40 different dcid/dsid/drid combinations (owid being an
"owner record", and dcid/dsid/drid a pointer into a type dictionary). "pid"
is going to be dropped.
Thanks for your input :)
--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/
Ernest E Vogelsinger <ernest@vogelsinger.at> writes:
> So if I get you right the "IS NULL" predicate doesn't fit into "<op>"
> "<value>"?
Exactly.
In the last go-round on this topic, there was some discussion of
changing IS NULL from a specialized construct into an operator
(and underlying function), which'd allow it to be fit into the
index opclass abstraction. I think it's doable, but no one's
got round to doing it yet.
regards, tom lane