Thread: clustering by partial indexes
This might have been discussed before but I wanted to know if clustering tables
by partial indexes will be availble in a later release of pgSQL?
For the record, this is the error I get in 8.1:
iprism=# cluster hrs_idx on report;
ERROR: cannot cluster on partial index "hrs_idx"
hrs_idx is defined as:
iprism=# \d hrs_idx
Index "public.hrs_idx"
Column | Type
--------+--------------------------
stamp | timestamp with time zone
btree, for table "public.report", predicate (thehour(stamp) >= 0::double
precision AND thehour(stamp) <= 23::double precision)
--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com
____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com
"Keith C. Perry" <netadmin@vcsn.com> writes:
> This might have been discussed before but I wanted to know if clustering tables
> by partial indexes will be availble in a later release of pgSQL?
What in the world would it mean to do that?
regards, tom lane
Quoting Tom Lane <tgl@sss.pgh.pa.us>:
> "Keith C. Perry" <netadmin@vcsn.com> writes:
> > This might have been discussed before but I wanted to know if clustering
> tables
> > by partial indexes will be availble in a later release of pgSQL?
>
> What in the world would it mean to do that?
I'm not sure I understand your question. Right now you can cluster tables based
on an index but when I tried to do that with a partial index I got an error.
That information was in my first email. Perhaps a more basic question why can't
I cluster by a partial index. Here is the information again cut & pasted from
pgadminIII v1.4 (I omitted the table ddl previously).
CREATE TABLE report
(
"match" int4 NOT NULL,
"action" varchar(16),
stamp timestamptz NOT NULL,
account varchar(32),
ipaddress inet,
profile varchar(16),
rating text,
url text,
CONSTRAINT report_pkey PRIMARY KEY ("match", stamp)
)
WITHOUT OIDS;
CREATE INDEX hrs_idx
ON report
USING btree
(stamp)
WHERE thehour(stamp) >= 0::double precision AND thehour(stamp) <= 23::double
precision;
CREATE OR REPLACE FUNCTION thehour(timestamptz)
RETURNS float8 AS
$BODY$
begin
return extract(hour from $1);
end;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
Now when I go into the database with psql...
Welcome to psql 8.1.0, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
iprism=# \h cluster
Command: CLUSTER
Description: cluster a table according to an index
Syntax:
CLUSTER indexname ON tablename
CLUSTER tablename
CLUSTER
iprism=# cluster hrs_idx on report;
ERROR: cannot cluster on partial index "hrs_idx"
iprism=#
--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com
____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com
"Keith C. Perry" <netadmin@vcsn.com> writes:
> Quoting Tom Lane <tgl@sss.pgh.pa.us>:
>> "Keith C. Perry" <netadmin@vcsn.com> writes:
>>> This might have been discussed before but I wanted to know if clustering
>>> tables by partial indexes will be availble in a later release of pgSQL?
>>
>> What in the world would it mean to do that?
> I'm not sure I understand your question.
CLUSTER says "order the table according to the order of the entries in
this index". A partial index doesn't define an ordering for the whole
table, only the rows that have entries in that index. So it doesn't
seem to me that you are asking for something that has a well defined
meaning.
regards, tom lane
Tom Lane wrote: > "Keith C. Perry" <netadmin@vcsn.com> writes: > > Quoting Tom Lane <tgl@sss.pgh.pa.us>: > >> "Keith C. Perry" <netadmin@vcsn.com> writes: > >>> This might have been discussed before but I wanted to know if clustering > >>> tables by partial indexes will be availble in a later release of pgSQL? > >> > >> What in the world would it mean to do that? > > > I'm not sure I understand your question. > > CLUSTER says "order the table according to the order of the entries in > this index". A partial index doesn't define an ordering for the whole > table, only the rows that have entries in that index. So it doesn't > seem to me that you are asking for something that has a well defined > meaning. I assume it would cluster the part of the table covered by the partial index, and the rest of the table would be in any order. It seems like reasonable behavior, though this is the first request I can remember. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> CLUSTER says "order the table according to the order of the entries in
>> this index". A partial index doesn't define an ordering for the whole
>> table, only the rows that have entries in that index. So it doesn't
>> seem to me that you are asking for something that has a well defined
>> meaning.
> I assume it would cluster the part of the table covered by the partial
> index, and the rest of the table would be in any order. It seems like
> reasonable behavior, though this is the first request I can remember.
But what is the point? You might as well cluster by a full index.
This is *not* trivial to implement, btw, so one request with no
justification should not be enough to get it on the TODO list.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> CLUSTER says "order the table according to the order of the entries in > >> this index". A partial index doesn't define an ordering for the whole > >> table, only the rows that have entries in that index. So it doesn't > >> seem to me that you are asking for something that has a well defined > >> meaning. > > > I assume it would cluster the part of the table covered by the partial > > index, and the rest of the table would be in any order. It seems like > > reasonable behavior, though this is the first request I can remember. > > But what is the point? You might as well cluster by a full index. But I assume they created a partial index because they didn't want a full index. > This is *not* trivial to implement, btw, so one request with no > justification should not be enough to get it on the TODO list. Yea, that is the real issue I was alluding to. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tuesday 15 November 2005 10:12, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> CLUSTER says "order the table according to the order of the > >> entries in this index". A partial index doesn't define an > >> ordering for the whole table, only the rows that have entries in > >> that index. So it doesn't seem to me that you are asking for > >> something that has a well defined meaning. > > > > I assume it would cluster the part of the table covered by the > > partial index, and the rest of the table would be in any order. > > It seems like reasonable behavior, though this is the first > > request I can remember. > > But what is the point? You might as well cluster by a full index. > > This is *not* trivial to implement, btw, so one request with no > justification should not be enough to get it on the TODO list. Not trivial? Seems to me more like impossible to implement for the general case which would require you to resolve the situation where someone requests multiple, overlapping, clustered partial indexes where the ordering requirements are in conflict. Cheers, Steve
On Tue, Nov 15, 2005 at 10:18:26AM -0800, Steve Crawford wrote: > Not trivial? Seems to me more like impossible to implement for the > general case which would require you to resolve the situation where > someone requests multiple, overlapping, clustered partial indexes > where the ordering requirements are in conflict. Well, currently you can only cluster on a single index per table, and I really doubt that will change. In any case, if someone's going to work on clustered indexes I think it would be much more worthwhile to make them self-maintaining (or at least more self-maintaining). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > On Tue, Nov 15, 2005 at 10:18:26AM -0800, Steve Crawford wrote: > > Not trivial? Seems to me more like impossible to implement for the > > general case which would require you to resolve the situation where > > someone requests multiple, overlapping, clustered partial indexes > > where the ordering requirements are in conflict. > > Well, currently you can only cluster on a single index per table, and I > really doubt that will change. In any case, if someone's going to work > on clustered indexes I think it would be much more worthwhile to make > them self-maintaining (or at least more self-maintaining). Wow, imagine if we could cluster by partial indexes, and then imagine we could allow multiple clustering per table if the partial indexes did not overlap --- that is a massive project. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tue, Nov 15, 2005 at 08:41:06PM -0500, Bruce Momjian wrote: > Jim C. Nasby wrote: > > Well, currently you can only cluster on a single index per table, and I > > really doubt that will change. In any case, if someone's going to work > > on clustered indexes I think it would be much more worthwhile to make > > them self-maintaining (or at least more self-maintaining). > > Wow, imagine if we could cluster by partial indexes, and then imagine we > could allow multiple clustering per table if the partial indexes did not > overlap --- that is a massive project. Particularly the bit about proving they didn't overlap. Only a full table scan will prove one way or the other in the general case. ISTM that the only use for clustering on a partial index is as an indication of the actual order. So, if you have the index: CREATE INDEX myindex ON mytable (col1,col2) WHERE col3 = true; Then clustering on it would be equivalent to: CREATE INDEX dummyindex ON mytable (col1,col2,col3=true); CLUSTER mytable ON dummyindex; DROP INDEX dummyindex; Although you'd probably use a more efficient SELECT INTO construct. Additionally, clustering by multiple indexes would be simply be an indication of the order, concatinate the fields. All that is needed is a cluster method that doesn't use an INDEX, the rest is just sugercoating. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.