Re: Including Snapshot Info with Indexes
| От | Gokulakannan Somasundaram |
|---|---|
| Тема | Re: Including Snapshot Info with Indexes |
| Дата | |
| Msg-id | 9362e74e0710140614j5d18d296s2bebdfd979483459@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Including Snapshot Info with Indexes (Hannu Krosing <hannu@skype.net>) |
| Ответы |
Re: Including Snapshot Info with Indexes
(Gregory Stark <stark@enterprisedb.com>)
Re: Including Snapshot Info with Indexes ("Trevor Talbot" <quension@gmail.com>) |
| Список | pgsql-hackers |
On 10/14/07, Hannu Krosing <hannu@skype.net> wrote:
What i meant there was, it has duplicated storage of certain columns of the table. A table with more than one projection always needs more space, than a table with just one projection. By doing this they are reducing the number of disk operations. If they are duplicating columns of data to avoid reading un-necessary information, we are duplicating the snapshot information to avoid going to the table.
And more one more good news for people, who are following this thread. It seems like we won't be having a hit on update performance, if the indexes are not updated. BTStack remains the same for the old and new tuples, if the index tuple is not updated. But i don't know whether i would be able to put that tuning(re-using BTSTack) in the first patch
So Indexes with snapshots will be degrading the performance only for deletes and only those updates, which are updating the index tuple.
I think delete overhead can be ruled out for those who will be working with partitions, since they usually drop the partitions.
Thanks,
Gokul.
Ühel kenal päeval, L, 2007-10-13 kell 17:44, kirjutas Gokulakannan
Somasundaram:
> Hi,
> I went through this article and it was good. Please have a look
> at it.
>
> http://www.databasecolumn.com/2007/09/one-size-fits-all.html
>
> This article was written by Michael Stonebraker, considered to be the
> founder of our database. He has mentioned that the DBMS designed in
> 1970s haven't changed according to the change that has happened in
> Hardware landscape.
What has happened in reality, is that the speed difference between CPU,
RAM and disk speeds has _increased_ tremendously, which makes it even
more important to _decrease_ the size of stored data if you want good
performance
> The Vertica database(Monet is a open source version with the same
> principle) makes use of the very same principle. Use more disk space,
> since they are less costly and optimize the data warehousing.
MonetDB is not about "using more disk to get better performance", but
about reducing the need to read unused data and increasing the speed by
that.
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:
http://homepages.cwi.nl/~boncz/x100.html
http://www.cwi.nl/themes/ins1/publications/docs/ZuBoNeHe:DEBULL:05.pdf
What i meant there was, it has duplicated storage of certain columns of the table. A table with more than one projection always needs more space, than a table with just one projection. By doing this they are reducing the number of disk operations. If they are duplicating columns of data to avoid reading un-necessary information, we are duplicating the snapshot information to avoid going to the table.
> Even otherwise we are recommending Indexes with snapshot as an option.
> We are not replacing the current index scheme. So if someone feels
> that his database should run on lesser disk space, let them create the
> normal index. If he feels he can afford to have more redundant disk
> space, then he can create indexes with snapshots. We are reducing
> random I/Os at the cost of extra disk space. So definitely that's a
> good. But tech folks like us can better decide on something based on
> experiments, as Tom has pointed out. So let's see whether Indexes with
> snapshot is worth the trade-off in space.
Agreed.
And more one more good news for people, who are following this thread. It seems like we won't be having a hit on update performance, if the indexes are not updated. BTStack remains the same for the old and new tuples, if the index tuple is not updated. But i don't know whether i would be able to put that tuning(re-using BTSTack) in the first patch
So Indexes with snapshots will be degrading the performance only for deletes and only those updates, which are updating the index tuple.
I think delete overhead can be ruled out for those who will be working with partitions, since they usually drop the partitions.
Thanks,
Gokul.
------------
Hannu
В списке pgsql-hackers по дате отправления: