Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As described here: https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes indexes do not store the entire row data themselves. Is it possible to store the whole row in the index for fast retrieve? I don't care about space.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Of course, you can create an index which would contain all columns from the rows.

From PostgreSQL 9.2 there is a possibility that this will speed up certain SELECT queries as from that version there are index-only scans. The difference between the 'raw' table and this index will only show if the query picks only a part of all rows, based on the first column(s) of the index. A WHERE clause containing conditions only about some later columns will most probably do a sequential scan, that is, it will go to the table itself as the rows to be retrieved will be spread across the whole index.

Also, maintaining such an index is quite expensive when it comes to DML statements - I would say that it doubles the number of writes for every such statement.

share|improve this answer
1  
In DB2 there is the possibility to add columns to a unique index which are not indexed (so there's no subtree), but only added for performance issues. You do this with INCLUDE(column1, ...). Is there something like this in Postgres? Your answer sounds more like "just create a multidimensional index". –  kasia.b Sep 18 '13 at 11:29
2  
Well, there is nothing like that in PostgreSQL. Also, it is a multicolumn index :) Another example, in SQL Server a clustered index is basically the table itself, physically ordered, but again, PostgreSQL doesn't have that feature either. –  dezso Sep 18 '13 at 12:09
    
Let me know if I got it: when I create a multicolumn index, all columns are stored in index for fast retrieve? But it lets write worse, isn't it? –  Felipe Micaroni Lalli Sep 18 '13 at 22:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.