1

Hi i am a bit confused about the handling of indexes in postgres. I use the 9.6 version. From my understanding after reading postgres docs and answers from stackoverflow i want to verify the following:

  • postgres does not support indexes with the classic notion
  • all indexes in postgres are non-clustered indexes
  • indexes does not allocate any new space but apply a sort on the table thats way after create index a CLUSTER command shall follow.
  • in the docs it is stated that after updates/inserts on table the index is updated automatically

Show i created a table with col1,col2,col3,col4 and the an index based on col2, col3. Selects that have to do with col2, col3 became 15 times faster. When i execute select * from table then results are displayed first sorted based on col2 and then based on col3.

When i add a new row in the table (with a col2 value (test_value) that already existed), this row went at the end of the table (this was checked with : select * from table).

1) Did the index got updated with this new entry automatically even if the select all showed the row at the end?

2) If a execute a query will all the rows that have the test_value on col2 what will happen? Will i get all the results through the index?

Tapxxor Stanijkis
  • 81
  • 8
  • **Some Notes**: The default order is by modification, be that an insert or update. In postgresql if you want an ordered list, you always have to use `ORDER BY` in the query. When you assign a PK it automatically creates an 'index' for the PK, and you can create indexes by your own. You can assign a different tablespace for the indexes, this makes a table that has millions to be queried faster. It's good to give indexes an exclusive tablespace. – Dan Oct 04 '17 at 19:44
  • What is considered as a best practice either use a common tablespace for 2 indexes that operate on the same table or 2 different tablespaces? – Tapxxor Stanijkis Oct 04 '17 at 20:24
  • Generally, you use a tablespace for everything. But if you have a large database, you can use a tablespace for the tables and another tablespace for the Indexes (that makes queries a little bit faster when you have a lot of data). On the other hand If your Database is Huge, you may have to check the tablespaces accordingly, perhaps a tablespace for table or depending the DB structure. – Dan Oct 04 '17 at 21:42

1 Answers1

0

There are some wrong assumptions here.

The most important is: The order of the rows in a select is indeterminate unless you include ORDER BY. So you can get any result db engine decide is the faster way to get the data. So if select * from table return the last inserted element at the end, that doesn't tell you anything regarding the index.

How Rows are stored and Index information are separate things

1) Yes, index was updated after insert.

2) Yes, because index was already update.

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Thanks its clear now. I had read a post regarding indexing and CLUSTER and it messed all the things in my mind. So CLUSTER just applies the sorting described from the index so as row fetching be faster due to data locality. Thats why they mentioned that after some inserts you should apply CLUSTER again. – Tapxxor Stanijkis Oct 04 '17 at 20:49