Re: [HACKERS] proposal for smaller indexes on index-ordered tables

Jeffrey Baker wrote: The way I read it, the current btree index stores the index value and the TID of every tuple having that value. When you have a table with three columns, you index one of them and you get an index which is practically as large as the table itself. Supposing the table is gen

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

"Jeffrey Baker" <[EMAIL PROTECTED]> writes: > I guess nobody has any interest in my proposal, only in the departure of my > described experience from expected behavior :-( Well, we certainly should try to understand the unexpected behavior in detail before we consider solutions. Per Sir A.C. Doyl

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

On Tue, Jun 24, 2008 at 3:50 PM, Jonah H. Harris <[EMAIL PROTECTED]> wrote: > On Tue, Jun 24, 2008 at 4:34 PM, Jeffrey Baker <[EMAIL PROTECTED]> wrote: >> Supposing the table is generally or strictly ordered by the column to be >> indexed, it would be more compact if the index stored ranges of tupl

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

On Tue, Jun 24, 2008 at 3:08 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Kevin Grittner" <[EMAIL PROTECTED]> writes: > > Tom Lane <[EMAIL PROTECTED]> wrote: > >> Now, *why* it is a mistake is interesting to speculate about, but > >> let's confirm the theory first. > > > Could this be related to hin

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

"Kevin Grittner" <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> Now, *why* it is a mistake is interesting to speculate about, but >> let's confirm the theory first. > Could this be related to hint bit rewrites during indexing? If so, changing maintenance_work_mem won't imp

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

>>> On Tue, Jun 24, 2008 at 4:54 PM, in message <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: > "Jeffrey Baker" <[EMAIL PROTECTED]> writes: >> Creating the table in this case takes half an >> hour and then indexing it requires almost an hour. > > These numbers seem to me to be pretty

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

"Jeffrey Baker" <[EMAIL PROTECTED]> writes: > For this query, work_mem is 100MB and maintenance_work_mem is 1GB, on a > system with 8GB of memory. Notably I just installed a new storage subsystem > and upgraded to 8.3.1 less than a week ago, so my experience with this > instance is somewhat limite

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

On Tue, Jun 24, 2008 at 2:38 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Jeffrey Baker" <[EMAIL PROTECTED]> writes: > > I'm quite aware of the problems of maintaining such a table and index, > but > > the fact is that data warehouse type tables may never be updated after > being > > created. The p

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

"Jeffrey Baker" <[EMAIL PROTECTED]> writes: > I'm quite aware of the problems of maintaining such a table and index, but > the fact is that data warehouse type tables may never be updated after being > created. The particular application I'm struggling with does a SELECT ... > INTO ... ORDER BY to

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

Jeffrey Baker írta: > On Tue, Jun 24, 2008 at 1:59 PM, Zoltan Boszormenyi <[EMAIL PROTECTED] > > wrote: > > Jeffrey Baker írta: > > The way I read it, the current btree index stores the index > value and > > the TID of every tuple having that value. When y

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

On Tue, Jun 24, 2008 at 1:59 PM, Zoltan Boszormenyi <[EMAIL PROTECTED]> wrote: > Jeffrey Baker írta: > > The way I read it, the current btree index stores the index value and > > the TID of every tuple having that value. When you have a table with > > three columns, you index one of them and you

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

Jeffrey Baker írta: > The way I read it, the current btree index stores the index value and > the TID of every tuple having that value. When you have a table with > three columns, you index one of them and you get an index which is > practically as large as the table itself. > > Supposing the tabl

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

On Tue, Jun 24, 2008 at 4:34 PM, Jeffrey Baker <[EMAIL PROTECTED]> wrote: > Supposing the table is generally or strictly ordered by the column to be > indexed, it would be more compact if the index stored ranges of tuples. > Instead of storing the TID of every tuple with that value, the index would