Thread: Grouped Index Tuples / Clustered Indexes
I've updated the GIT patch at http://community.enterprisedb.com/git/. Bitrot caused by the findinsertloc-patch has been fixed, making that part of the GIT patch a little bit smaller and cleaner. I also did some refactoring, and minor cleanup and commenting. Any comments on the design or patch? For your convenience, I copied the same text I added to access/nbtree/README to http://community.enterprisedb.com/git/git-readme.txt Should we start playing the name game at this point? I've been thinking we should call this feature just Clustered Indexes, even though it's not exactly the same thing as clustered indexes in other DBMSs. From user point of view, they behave similarly enough that it may be best to use the existing term. As a next step, I'm hoping to get the indexam API changes from the bitmap index patch committed soon, and in a way that supports GIT as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
my only question would be. Why isn't that in core already ?
+1 On 3/7/07 6:53 AM, "Grzegorz Jaskiewicz" <gj@pointblue.com.pl> wrote: > my only question would be. > Why isn't that in core already ? > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
On Wed, 2007-03-07 at 10:32 +0000, Heikki Linnakangas wrote: > I've been thinking > we should call this feature just Clustered Indexes Works for me. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
> On Wed, 2007-03-07 at 10:32 +0000, Heikki Linnakangas wrote: >> I've been thinking >> we should call this feature just Clustered Indexes So we would have "clustered tables" which are tables whose heap is ordered according to an index and separately "clustered indexes" which are indexes optimized for such tables? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark wrote: >> On Wed, 2007-03-07 at 10:32 +0000, Heikki Linnakangas wrote: >>> I've been thinking >>> we should call this feature just Clustered Indexes > > So we would have "clustered tables" which are tables whose heap is ordered > according to an index and separately "clustered indexes" which are indexes > optimized for such tables? Yes, that's what I was thinking. There's a third related term in use as well. When you issue CLUSTER, the table will be clustered on an index. And that index is then the "index the table is clustered on". That's a bit cumbersome but that's the terminology we're using at the moment. Maybe we should to come up with a new term for that to avoid confusion.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: > There's a third related term in use as well. When you issue CLUSTER, the > table will be clustered on an index. And that index is then the "index > the table is clustered on". That's a bit cumbersome but that's the > terminology we're using at the moment. Maybe we should to come up with a > new term for that to avoid confusion.. This reminds me of something i've been wondering about for quite some time. Why is it that one has to write "cluster <index> on <table>", and not "cluster <table> on <index>"? To me, the second variant would seem more logical, but then I'm not a native english speaker... I'm not suggesting that this should be changed, I'm just wondering why it is the way it is. greetings, Florian Pflug
On Sun, 2007-03-11 at 11:22 +0000, Heikki Linnakangas wrote: > Gregory Stark wrote: > >> On Wed, 2007-03-07 at 10:32 +0000, Heikki Linnakangas wrote: > >>> I've been thinking > >>> we should call this feature just Clustered Indexes > > > > So we would have "clustered tables" which are tables whose heap is ordered > > according to an index and separately "clustered indexes" which are indexes > > optimized for such tables? > > Yes, that's what I was thinking. > > There's a third related term in use as well. When you issue CLUSTER, the > table will be clustered on an index. And that index is then the "index > the table is clustered on". That's a bit cumbersome but that's the > terminology we're using at the moment. Maybe we should to come up with a > new term for that to avoid confusion.. First thought: we can use the term "cluster*ing* index" for CLUSTER and use the term "clustered" to refer to what has happened to the table and the index. That will probably be confused with high availability clustering, so perhaps not. Better thought: say that CLUSTER requires an "order-defining index". That better explains the point that it is the table being clustered, using the index to define the physical order of the rows in the heap. We then use the word "clustered" to refer to what has happened to the table, and with this patch, for the index also. That way we can have new syntax for CLUSTER CLUSTER table ORDER BY indexname which is then the preferred syntax, rather than the perverse CLUSTER index ON table which gives the wrong impression about what is happening, since it is the table that is changed, not the index. - - - - Are you suggesting that we have an explicit new syntax CREATE [UNIQUE] CLUSTERED INDEX [CONCURRENTLY] fooidx ON foo (....) ... or just that we refer to this feature as Clustered Indexes? - Do we still need the index WITH option, in either case? - Do you think that all Primary Keys should be clustered? - Are you thinking to rename docs, catalog etc to reflect the new naming/meaning? My thinking would be: CLUSTERED, no, yes, yes but I'd like to know what you think? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
On Sun, 2007-03-11 at 19:06 +0100, Florian G. Pflug wrote: > Heikki Linnakangas wrote: > > There's a third related term in use as well. When you issue CLUSTER, the > > table will be clustered on an index. And that index is then the "index > > the table is clustered on". That's a bit cumbersome but that's the > > terminology we're using at the moment. Maybe we should to come up with a > > new term for that to avoid confusion.. > > This reminds me of something i've been wondering about for quite some > time. Why is it that one has to write "cluster <index> on <table>", > and not "cluster <table> on <index>"? > > To me, the second variant would seem more logical, but then I'm > not a native english speaker... > > I'm not suggesting that this should be changed, I'm just wondering > why it is the way it is. No idea, but I agree it conveys exactly the opposite view of what happens when the command is issued. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > Better thought: say that CLUSTER requires an "order-defining index". > That better explains the point that it is the table being clustered, > using the index to define the physical order of the rows in the heap. We > then use the word "clustered" to refer to what has happened to the > table, and with this patch, for the index also. > > That way we can have new syntax for CLUSTER > > CLUSTER table ORDER BY indexname > > which is then the preferred syntax, rather than the perverse > > CLUSTER index ON table > > which gives the wrong impression about what is happening, since it is > the table that is changed, not the index. I like that, "order-defining index" conveys the point pretty well. > - Are you suggesting that we have an explicit new syntax > > CREATE [UNIQUE] CLUSTERED INDEX [CONCURRENTLY] fooidx ON foo (....) ... > > or just that we refer to this feature as Clustered Indexes? I'm not proposing new syntax, just a WITH-parameter. Makes more sense to me that way, the clusteredness has no user-visible effects except performance, and it's b-tree specific (though I guess you could apply the same concept to other indexams as well). > - Do you think that all Primary Keys should be clustered? No. There's a significant CPU overhead when the index and table are in memory and you're doing simple one-row lookups. And there's no promise that a table is physically in primary key order anyway. There might be some interesting cases where we could enable it automatically. I've been thinking that if you explicitly CLUSTER a table, the order-defining index would definitely benefit from being a clustered index. If it's small enough that it fits in memory, there's no point in running CLUSTER in the first place. And if you run CLUSTER, we know it's in order. That seems like a pretty safe bet. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > On Sun, 2007-03-11 at 19:06 +0100, Florian G. Pflug wrote: > > Heikki Linnakangas wrote: > > > There's a third related term in use as well. When you issue CLUSTER, the > > > table will be clustered on an index. And that index is then the "index > > > the table is clustered on". That's a bit cumbersome but that's the > > > terminology we're using at the moment. Maybe we should to come up with a > > > new term for that to avoid confusion.. > > > > This reminds me of something i've been wondering about for quite some > > time. Why is it that one has to write "cluster <index> on <table>", > > and not "cluster <table> on <index>"? > > > > To me, the second variant would seem more logical, but then I'm > > not a native english speaker... > > > > I'm not suggesting that this should be changed, I'm just wondering > > why it is the way it is. > > No idea, but I agree it conveys exactly the opposite view of what > happens when the command is issued. We got the syntax from Berkely, and it has always seemed backwards to me too. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Added to TODO:
o Add more logical syntax CLUSTER table ORDER BY index; support current syntax for backward
compatibility
---------------------------------------------------------------------------
Simon Riggs wrote:
> On Sun, 2007-03-11 at 11:22 +0000, Heikki Linnakangas wrote:
> > Gregory Stark wrote:
> > >> On Wed, 2007-03-07 at 10:32 +0000, Heikki Linnakangas wrote:
> > >>> I've been thinking
> > >>> we should call this feature just Clustered Indexes
> > >
> > > So we would have "clustered tables" which are tables whose heap is ordered
> > > according to an index and separately "clustered indexes" which are indexes
> > > optimized for such tables?
> >
> > Yes, that's what I was thinking.
> >
> > There's a third related term in use as well. When you issue CLUSTER, the
> > table will be clustered on an index. And that index is then the "index
> > the table is clustered on". That's a bit cumbersome but that's the
> > terminology we're using at the moment. Maybe we should to come up with a
> > new term for that to avoid confusion..
>
> First thought: we can use the term "cluster*ing* index" for CLUSTER and
> use the term "clustered" to refer to what has happened to the table and
> the index. That will probably be confused with high availability
> clustering, so perhaps not.
>
> Better thought: say that CLUSTER requires an "order-defining index".
> That better explains the point that it is the table being clustered,
> using the index to define the physical order of the rows in the heap. We
> then use the word "clustered" to refer to what has happened to the
> table, and with this patch, for the index also.
>
> That way we can have new syntax for CLUSTER
>
> CLUSTER table ORDER BY indexname
>
> which is then the preferred syntax, rather than the perverse
>
> CLUSTER index ON table
>
> which gives the wrong impression about what is happening, since it is
> the table that is changed, not the index.
>
> - - -
>
> - Are you suggesting that we have an explicit new syntax
>
> CREATE [UNIQUE] CLUSTERED INDEX [CONCURRENTLY] fooidx ON foo (....) ...
>
> or just that we refer to this feature as Clustered Indexes?
>
> - Do we still need the index WITH option, in either case?
>
> - Do you think that all Primary Keys should be clustered?
>
> - Are you thinking to rename docs, catalog etc to reflect the new
> naming/meaning?
>
> My thinking would be: CLUSTERED, no, yes, yes
> but I'd like to know what you think?
>
> --
> Simon Riggs
> EnterpriseDB http://www.enterprisedb.com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --------------------------------------------------------------------------- Heikki Linnakangas wrote: > I've updated the GIT patch at http://community.enterprisedb.com/git/. > Bitrot caused by the findinsertloc-patch has been fixed, making that > part of the GIT patch a little bit smaller and cleaner. I also did some > refactoring, and minor cleanup and commenting. > > Any comments on the design or patch? For your convenience, I copied the > same text I added to access/nbtree/README to > http://community.enterprisedb.com/git/git-readme.txt > > Should we start playing the name game at this point? I've been thinking > we should call this feature just Clustered Indexes, even though it's not > exactly the same thing as clustered indexes in other DBMSs. From user > point of view, they behave similarly enough that it may be best to use > the existing term. > > As a next step, I'm hoping to get the indexam API changes from the > bitmap index patch committed soon, and in a way that supports GIT as well. > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +