Re: [HACKERS] Including Snapshot Info with Indexes

Hi,
I was able to complete the performance test successfully. I have
attached the details. In the meanwhile, i have fixed some bugs and produced
a second patch (against which i ran the Load test).

As expected the thick index was performing much better than the thin index,
in the index only scans.

In Inserts, there was no improvement as expected.
But in the normal selects there was no improvement. This is partially
because the density of the thin index might have offset the extra I/Os it
has to do to verify the snapshot.
Updates have degraded more than expected. Thick index can't make use of HOT.
My updates were mostly HOT updates.

I have found some areas for performance improvement for updates and deletes
and also normal selects. This will be put in the third patch.
But for those who want index only scans, the thick index leads the way.

Vacuum can be re-written for thick indexes, since they don't need any input
from tables for Vacuum. But that would benefit the user, only if his table
contains only thick indexes.

As far as Load Test is concerned, i have tried to provide all the relevant
details. Please inform me, if i have left any.

Please put forward your feedback.

On 10/24/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>
>
> This has been saved for consideration for the 8.4 release:
>
> http://momjian.postgresql.org/cgi-bin/pgpatches_hold
>
>
> ---------------------------------------------------------------------------
>
> Gokulakannan Somasundaram wrote:
> > Hi,
> > I would like to present the first patch. It currently has the
> following
> > restrictions
> > a) It does not support any functional indexes.
> > b) It supports queries like select count(1) from table where
> (restrictions
> > from indexed columns), but it does not support select count(1) from
> table.
> >
> > The Syntax to create this type of index is
> >
> > create thick index idx on dd(n1,n2)
> >
> > here idx- index name and dd- table name and n1 and n2 are column names.
> >
> > I have created a extra column in pg_index called indhassnapshot.
> >
> > I have also enabled the display of Logical Reads. In order to see that,
> set
> > log_statement_stats on.
> >
> > The thick index is clearly on the front, if you issue queries like
> >
> > select n2 from dd where n1>1000 and n2<1500;
> >
> > As already said, if the update is not incurring any extra cost, except
> if
> > the indexed columns are updated. Deletes are costly, making it ideal for
> > partitioned tables.
> >
> > In order to update the thick indexes, i have accessed the ps_ExprContext
> in
> > PlanState to get the oldtuple. But if we have a outer plan and inner
> plan,
> > then i have set the ps_ExprContext of innerplan to the outerplan. I
> don't
> > know whether there will be instances where the ps_ExprContext of
> outerplan
> > node will have some use in update queries.
> >
> > Right now, it passes the regression test suite. I had slight trouble
> with
> > pg_indent, so i think it has not got applied properly. But i have tried
> to
> > remove all the whitespace differences. Please be kind to me in case i
> have
> > missed any whitespace differences. :)
> >
> > Please review the patch and provide your comments.
> >
> > Thanks,
> > Gokul.
> > CertoSQL Project,
> > Allied Solution Groups.
> > (www.alliedgroups.com)
> >
> > On 10/23/07, Hannu Krosing <hannu(at)skype(dot)net> wrote:
> > >
> > > ?hel kenal p?eval, L, 2007-10-20 kell 10:19, kirjutas Luke Lonergan:
> > > > Hi Hannu,
> > > >
> > > > On 10/14/07 12:58 AM, "Hannu Krosing" <hannu(at)skype(dot)net> wrote:
> > > >
> > > > > What has happened in reality, is that the speed difference between
> > > CPU,
> > > > > RAM and disk speeds has _increased_ tremendously
> > > >
> > > > Yes.
> > > >
> > > > > which makes it even
> > > > > more important to _decrease_ the size of stored data if you want
> good
> > > > > performance
> > > >
> > > > Or bring the cpu processing closer to the data it's using (or both).
> > > >
> > > > By default, the trend you mention first will continue in an unending
> way
> > > -
> > > > the consequence is that the "distance" between a processor and it's
> > > target
> > > > data will continue to increase ad-infinitum.
> > >
> > > the emergence of solid-state (flash) disks may help a little here, but
> > > in general it is true.
> > >
> > > > By contrast, you can only decrease the data volume so much - so in
> the
> > > end
> > > > you'll be left with the same problem - the data needs to be closer
> to
> > > the
> > > > processing. This is the essence of parallel / shared nothing
> > > architecture.
> > > >
> > > > Note that we've done this at Greenplum. We're also implementing a
> > > DSM-like
> > > > capability and are investigating a couple of different hybrid row /
> > > column
> > > > store approaches.
> > >
> > > Have you tried moving the whole visibility part of tuples out to a
> > > separate heap ?
> > >
> > > Especially in OLAP/ETL scenarios the distribution of tuples loaded in
> > > one transaction should be very good for visibility-info compression.
> > >
> > > I'd suspect that you could crush hundreds of pages worth of visibility
> > > into single RLE encoding unit (xmin=N, xmax=no_yet, start_ctid = X,
> > > end_ctid=Y), and it will stay in L1 cache most of the time you process
> > > the corresponding relation. and the relation itself will be smaller,
> and
> > > index-only (actually index-only + lookup inside L1 cache) access can
> > > happen, and so on .
> > >
> > > OTOH, if you load it in millions of small transactions, you can run
> > > VACUUM FREEZE _on_ the visibility heap only, which will make all
> > > visibility infoe look similar and thus RLE-compressable and again make
> > > it fit in L1 cache, if you dont have lots of failed loads interleaved
> > > with successful ones.
> > >
> > > > Bitmap index with index-only access does provide nearly all of the
> > > > advantages of a column store from a speed standpoint BTW. Even
> though
> > > > Vertica is touting speed advantages - our parallel engine plus
> bitmap
> > > index
> > > > will crush them in benchmarks when they show up with real code.
> > > >
> > > > Meanwhile they're moving on to new ideas - I kid you not
> "Horizontica"
> > > is
> > > > Dr. Stonebraker's new idea :-)
> > >
> > > Sounds like a result of a marketroid brainstorming session :P
> > >
> > > > So - bottom line - some ideas from column store make sense, but it's
> not
> > > a
> > > > cure-all.
> > > >
> > > > > There is also a MonetDB/X100 project, which tries to make MonetOD
> > > > > order(s) of magnitude faster by doing in-page compression in order
> to
> > > > > get even more performance, see:
> > > >
> > > > Actually, the majority of the points made by the MonetDB team
> involve
> > > > decreasing the abstractions in the processing path to improve the
> IPC
> > > > (instructions per clock) efficiency of the executor.
> > >
> > > The X100 part was about doing in-page compression, so the efficiency
> of
> > > disk to L1 cache pathway would increase. so for 1/2 compression the
> CPU
> > > would get twice the data threoughput.
> > >
> > > > We are also planning to do this by operating on data in vectors of
> > > projected
> > > > rows in the executor, which will increase the IPC by reducing
> I-cache
> > > misses
> > > > and improving D-cache locality. Tight loops will make a much bigger
> > > > difference when long runs of data are the target operands.
> > > >
> > > > - Luke
> > > >
> > > >
> > > >
> > > > ---------------------------(end of
> broadcast)---------------------------
> > > > TIP 7: You can help support the PostgreSQL project by donating at
> > > >
> > > > http://www.postgresql.org/about/donate
> > >
> > >
>
> [ Attachment, skipping... ]
>
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB
> http://postgres.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>

--
Thanks,
Gokul.
CertoSQL Project,
Allied Solution Groups.
(www.alliedgroups.com)