A thought on Index Organized Tables
in [PgSql]
|
Prev: [COMMITTERS] pgsql: Oops, don't forget to rewind the directory before scanning it to
Next: Time travel on the buildfarm
From: Gokulakannan Somasundaram on 23 Feb 2010 08:47 On Tue, Feb 23, 2010 at 10:42 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Takahiro Itagaki <itagaki.takahiro(a)oss.ntt.co.jp> writes: > > Instead, how about excluding columns in primary keys from table data? > > How will you implement "select * from mytable" ? Or even > "select * from mytable where non_primary_key = something" ? > If you can't do either of those without great expense, I think > a savings on primary-key lookups is not going to be adequate > recompense. > Tom, I am talking things more from the perspective of how things have got implemented in Oracle/SQL Server. Both Oracle and SQL Server store the snapshot info with indexes and hence can do index-only scans with their indexes. But still they have the concept of Index Organized Tables / Clustered Indexes. Apart from the disk footprint, it will have an impact on the cache efficiency also. In Oracle IOT and SQL Server Clustered Indexes, you have an option to store some of the columns in the leaf pages( but not in the non-leaf pages) and hence the tuples won't get sorted based on them, but you don't require an extra i/o to access them. This optimization is again to reduce the size of IOT. Oracle IOT has a concept called overflow regions, which is more like a heap and will store a few columns. There will be a pointer from main b-tree structure to this secondary structure. Accessing these columns are costly, but the idea is that the database designer has taken this into account while deciding on the columns to be put in the overflow regions. We can design secondary indexes to make the access faster for non-primary key based searches. But since the Secondary indexes store primary key in the place of HeapTuple Pointer, the access will usually take 2-3 more i/os. But the idea is that the IOT is for those kind of data. which will be 99% queried based on primary keys. The database provides that extra performance for that kind of access patterns. So to answer your question, full table scans(if overflow regions are involved) and search based on non-primary keys will be slow in an IOT. I looked at the postgres nbtree code. From my analysis(which might be wrong!), we can implement IOTs, provided we make a decision on broken data types issue. Thanks, Gokul.
From: Csaba Nagy on 23 Feb 2010 07:00 Hi all, On Mon, 2010-02-22 at 10:29 +0000, Greg Stark wrote: > On Mon, Feb 22, 2010 at 8:18 AM, Gokulakannan Somasundaram > <gokul007(a)gmail.com> wrote: > > a) IOT has both table and index in one structure. So no duplication of data > > b) With visibility maps, we have three structures a) Table b) Index c) > > Visibility map. So the disk footprint of the same data will be higher in > > postgres ( 2x + size of the visibility map). > > These sound like the same point to me. I don't think we're concerned > with footprint -- only with how much of that footprint actually needs > to be scanned. For some data the disk foot-print would be actually important: on our data bases we have one table which has exactly 2 fields, which are both part of it's primary key, and there's no other index. The table is write-only, never updated and rarely deleted from. The disk footprint of the table is 30%-50% of the total disk space used by the DB (depending on the other data). This amounts to about 1.5-2TB if I count it on all of our DBs, and it has to be fast disk too as the table is heavily used... so disk space does matter for some. And yes, I put the older entries in some archive partition on slower disks, but I just halve the problem - the data is growing exponentially, and about half of it is always in use. I guess our developers are just ready to get this table out of postgres and up to hadoop... Cheers, Csaba. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 23 Feb 2010 09:49 On Mon, 2010-02-22 at 08:51 +0200, Heikki Linnakangas wrote: > Gokulakannan Somasundaram wrote: > > May i get a little clarification on this issue? Will we be supporting > > the IOT feature in postgres in future? > > What seems like the best path to achieve the kind of performance > benefits that IOTs offer is allowing index-only-scans using the > visibility map. I don't agree with that. Could you explain why you think that would be the case? It would be a shame to have everybody think you can solve a problem if it turned out not to be the case. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andrew Dunstan on 23 Feb 2010 09:58 Gokulakannan Somasundaram wrote: > > I looked at the postgres nbtree code. From my analysis(which > might be wrong!), we can implement IOTs, provided we make a decision > on broken data types issue. > > I am not familiar with this term "broken data types", and I just looked for it in the source code and couldn't find it. What exactly are you referring to? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Heikki Linnakangas on 23 Feb 2010 10:08
Simon Riggs wrote: > On Mon, 2010-02-22 at 08:51 +0200, Heikki Linnakangas wrote: >> Gokulakannan Somasundaram wrote: > >>> May i get a little clarification on this issue? Will we be supporting >>> the IOT feature in postgres in future? >> What seems like the best path to achieve the kind of performance >> benefits that IOTs offer is allowing index-only-scans using the >> visibility map. > > I don't agree with that. Could you explain why you think that would be > the case? It would be a shame to have everybody think you can solve a > problem if it turned out not to be the case. I'm thinking of a scan based on the index key. With an index-organised-table, you can skip the heap access because the heap and the index are the same structure. An index-only-scan likewise allows you to skip the heap access. I grant you that an index-organised-table can have other benefits, like reduced disk space usage (which is good cache efficiency), or less random I/O required for updates. The question was if PostgreSQL will be supporting index-organised-tables in the future. The answer is "not in the foreseeable future". No-one has come up with a plausible plan for how to do it, and no-one working on it at the moment. I don't want to discourage thinking about pie-in-the-sky features. There's many tricks like column-oriented storage, compression, index-organised-tables etc. that would be nice to have. Whether any particular feature is worthwhile in the end, the devil is in the details. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |