Thread: Re: [HACKERS] Including Snapshot Info with Indexes
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)
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@skype.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@skype.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
Gokulakannan Somasundaram wrote: > 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. An interesting question is how to represent tuples coming from the index in the executor. I see that you didn't address that at all, because you only support "COUNT(1)", and not things like "SELECT column FROM table WHERE id = ?" where you actually return datums from the index. But that's something that we have to think about in the DSM approach as well. One solution is to form a heap tuple, using the datums from the index, with the attributes that are not used in the query replaced with NULLs. That seems simple, but I don't think it'll work with expression indexes, when you do something like "SELECT length(column) FROM table WHERE id = ?", and there's an index on (id, length(column)). > I have also enabled the display of Logical Reads. In order to see that, set > log_statement_stats on. You should start benchmarking, to verify that you're really getting the kind of speed up you're looking for, before you spend any more effort on that. Reduction in logical reads alone isn't enough. Remember that for a big change like that, the gain has to be big as well. As a first test, I'd like to see results from SELECTs on different sized tables. On tables that fit in cache, and on tables that don't. Tables large enough that the index doesn't fit in cache. And as a special case, on a table just the right size that a normal index fits in cache, but a thick one doesn't. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On 10/23/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
Gokulakannan Somasundaram wrote:
> 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.
An interesting question is how to represent tuples coming from the index
in the executor. I see that you didn't address that at all, because you
only support "COUNT(1)", and not things like "SELECT column FROM table
WHERE id = ?" where you actually return datums from the index. But
that's something that we have to think about in the DSM approach as well.
That's addressed as well.
One solution is to form a heap tuple, using the datums from the index,
with the attributes that are not used in the query replaced with NULLs.
That seems simple, but I don't think it'll work with expression indexes,
when you do something like "SELECT length(column) FROM table WHERE id =
?", and there's an index on (id, length(column)).
> I have also enabled the display of Logical Reads. In order to see that, set
> log_statement_stats on.
You should start benchmarking, to verify that you're really getting the
kind of speed up you're looking for, before you spend any more effort on
that. Reduction in logical reads alone isn't enough. Remember that for a
big change like that, the gain has to be big as well.
As a first test, I'd like to see results from SELECTs on different sized
tables. On tables that fit in cache, and on tables that don't. Tables
large enough that the index doesn't fit in cache. And as a special case,
on a table just the right size that a normal index fits in cache, but a
thick one doesn't.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
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@skype.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@skype.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@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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.
--
Thanks,
Gokul.
CertoSQL Project,
Allied Solution Groups.
(www.alliedgroups.com)
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@momjian.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@skype.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@skype.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@momjian.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)
Attachment
Hi,
I did some more bug fixes and performance updates especially for select count(1) queries.
Thanks,
Gokul.
I did some more bug fixes and performance updates especially for select count(1) queries.
Thanks,
Gokul.
Attachment
This fixes the bug in dealing with scans with 'or' conditions. I have also attached the design document.
Thanks,
Gokul.
Thanks,
Gokul.
On Jan 16, 2008 7:25 PM, Gokulakannan Somasundaram < gokul007@gmail.com> wrote:
Hi,
I did some more bug fixes and performance updates especially for select count(1) queries.
Thanks,
Gokul.
Attachment
Fixed a type 'o'....
On Jan 23, 2008 9:28 PM, Gokulakannan Somasundaram <gokul007@gmail.com> wrote:
This fixes the bug in dealing with scans with 'or' conditions. I have also attached the design document.
Thanks,
Gokul.On Jan 16, 2008 7:25 PM, Gokulakannan Somasundaram < gokul007@gmail.com> wrote:Hi,
I did some more bug fixes and performance updates especially for select count(1) queries.
Thanks,
Gokul.
Attachment
On Jan 23, 2008 11:28 AM, Gokulakannan Somasundaram <gokul007@gmail.com> wrote: > Fixed a type 'o'.... I'm playing with this now against 8.3 HEAD. Looks like there's a couple things which are problematic: - DefineIndex was updated only in bootparse.c, not in bootparse.y - The patch contains changes to pg_config.h - THICK isn't defined in gram.y (as a token or under unreserved_keywords), so compilation of keywords.c fails. In the future, please make changes to the proper pre-built files so that someone doesn't have to configure it, then patch it. I have them fixed and will submit the patch back here if you'd like. Or, you can fix it. It's up to you :) -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
Thanks for reviewing the patch. Please go ahead and make the changes and re-submit the patch. I will take care, that i won't repeat the stated mistakes again.
The Missing of Thick Keyword - i don't know how it got removed.
Thanks,
Gokul
The Missing of Thick Keyword - i don't know how it got removed.
Thanks,
Gokul
On Jan 23, 2008 10:49 PM, Jonah H. Harris <jonah.harris@gmail.com> wrote:
On Jan 23, 2008 11:28 AM, Gokulakannan Somasundaram <gokul007@gmail.com> wrote:
> Fixed a type 'o'....
I'm playing with this now against 8.3 HEAD. Looks like there's a
couple things which are problematic:
- DefineIndex was updated only in bootparse.c, not in bootparse.y
- The patch contains changes to pg_config.h
- THICK isn't defined in gram.y (as a token or under
unreserved_keywords), so compilation of keywords.c fails.
In the future, please make changes to the proper pre-built files so
that someone doesn't have to configure it, then patch it. I have them
fixed and will submit the patch back here if you'd like. Or, you can
fix it. It's up to you :)
--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com
Edison, NJ 08837 | http://www.enterprisedb.com/
Doh! Can you please send another patch with gram.y as well. Mine is missing all of the thick index stuff. On Jan 23, 2008 2:45 PM, Gokulakannan Somasundaram <gokul007@gmail.com> wrote: > Thanks for reviewing the patch. Please go ahead and make the changes and > re-submit the patch. I will take care, that i won't repeat the stated > mistakes again. > > The Missing of Thick Keyword - i don't know how it got removed. > > > > Thanks, > Gokul > > > > On Jan 23, 2008 10:49 PM, Jonah H. Harris <jonah.harris@gmail.com> wrote: > > On Jan 23, 2008 11:28 AM, Gokulakannan Somasundaram <gokul007@gmail.com> > wrote: > > > Fixed a type 'o'.... > > > > I'm playing with this now against 8.3 HEAD. Looks like there's a > > couple things which are problematic: > > > > - DefineIndex was updated only in bootparse.c, not in bootparse.y > > - The patch contains changes to pg_config.h > > - THICK isn't defined in gram.y (as a token or under > > unreserved_keywords), so compilation of keywords.c fails. > > > > In the future, please make changes to the proper pre-built files so > > that someone doesn't have to configure it, then patch it. I have them > > fixed and will submit the patch back here if you'd like. Or, you can > > fix it. It's up to you :) > > > > > > > > > > -- > > Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 > > EnterpriseDB Corporation | fax: 732.331.1301 > > 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com > > Edison, NJ 08837 | http://www.enterprisedb.com/ > > > > -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
I am not seeing my mail getting listed in the archives. So i am just resending it, in case the above one has got missed.
Thanks,
Gokul.
Thanks,
Gokul.
On Jan 28, 2008 4:14 PM, Gokulakannan Somasundaram <gokul007@gmail.com> wrote:
Doh! Can you please send another patch with gram.y as well. Mine is
missing all of the thick index stuff.
I apologize for the mistake. I am sending the revised patch - generated against CVS Head. Thanks for the guidance provided.
This patch also fixes a bug, which appeared in select count(1) from table where varchar_column like 'xx%' kind of queries.
In order to find out whether the index will be able to answer all the where clause conditions, i have put the checks under expand_indexqual_conditions function. Please get back, if there is any problem with the approach.
Waiting for feedback...
Thanks,
Gokul.
Attachment
On Jan 28, 2008 8:21 AM, Gokulakannan Somasundaram <gokul007@gmail.com> wrote: > I am not seeing my mail getting listed in the archives. So i am just > resending it, in case the above one has got missed. It was sent. Archive processing is delayed. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
I have added URLs to your patch to the TODO list:
* Allow data to be pulled directly from indexes
---------------------------------------------------------------------------
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@skype.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@skype.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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +