Re: [HACKERS] Including Snapshot Info with Indexes

From: "Gokulakannan Somasundaram" <gokul007(at)gmail(dot)com>
To: pgsql-patches(at)postgresql(dot)org
Cc: "Luke Lonergan" <llonergan(at)greenplum(dot)com>, "Gregory Stark" <stark(at)enterprisedb(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Andreas Joseph Krogh" <andreak(at)officenet(dot)no>, "Hannu Krosing" <hannu(at)skype(dot)net>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Including Snapshot Info with Indexes
Date: 2007-10-23 09:10:37
Message-ID: 9362e74e0710230210n2f58659fr24cb695a738035f8@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches

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 Content-Type Size
patchfile.tar.gz application/x-gzip 24.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Csaba Nagy 2007-10-23 09:14:41 Re: Feature Freeze date for 8.4
Previous Message Rafael Martinez 2007-10-23 09:00:59 Re: Feature Freeze date for 8.4

Browse pgsql-patches by date

  From Date Subject
Next Message Heikki Linnakangas 2007-10-23 10:30:04 Re: [HACKERS] Including Snapshot Info with Indexes
Previous Message Hannu Krosing 2007-10-23 07:20:48 Re: Including Snapshot Info with Indexes