3 ms·

It's not about choosing the right index. The point here is that the database can only read the index (without going into the table) if all the columns you need

by n0tme 7y ago

It's not about choosing the right index. The point here is that the database can only read the index (without going into the table) if all the columns you need and use in where clause are in the index. Usually index segments are smaller, so it could be faster. I am talking from the Oracle perspective, but I believe PostgreSQL would work in a similar way.

danielbarla 7y ago
Yes, of course, indexes work like that with where clauses. However here I was referring to covering indexes, where the columns in your _select_ are also present (after the ones in the where clause / join / predicate). This prevents having to do extra seeks into the clustered index to fetch that data, and can make very significant differences, especially if row counts are high. Clearly, this type of index is both expensive to maintain, and the benefits are lessened if you include every column in a table in the covering index. So, to actually have real benefits, you need to have carefully crafted queries which only select what they need, and these being matched to carefully crafted covering indexes. Basically, you cannot have these - which are one of the better weapons you have for performance - if you always select everything.
- n0tme 7y ago
  I am talking about INDEX FAST FULL SCAN (Oracle) for example. If you do 'select distinct last_name from users;' and you have an index on users (last_name) you do not need to read the table at all. It is enough to read the index. Otherwise it does not matter how many columns you select, because you will have to read all the columns for every row anyway.
  - adamzochowski 7y ago
    > Otherwise it does not matter how many columns you select, because you will have to read all the columns for every row anyway. It does matter. An index can actually contain a cache of values for some columns. This is done with INCLUDE statement. CREATE INDEX idx ON book ( author_id ) INCLUDE ( book_title ) This select will use only index: select author_id, book_title from books where author_id = 123 This select will use index, and then have to follow and fetch data out of rows: select * from books where author_id = 123 https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes https://use-the-index-luke.com/blog/2019-04/include-columns-...

by n0tme 7y ago

danielbarla 7y ago

Yes, of course, indexes work like that with where clauses. However here I was referring to covering indexes, where the columns in your _select_ are also present (after the ones in the where clause / join / predicate). This prevents having to do extra seeks into the clustered index to fetch that data, and can make very significant differences, especially if row counts are high. Clearly, this type of index is both expensive to maintain, and the benefits are lessened if you include every column in a table in the covering index. So, to actually have real benefits, you need to have carefully crafted queries which only select what they need, and these being matched to carefully crafted covering indexes. Basically, you cannot have these - which are one of the better weapons you have for performance - if you always select everything.

n0tme 7y ago
I am talking about INDEX FAST FULL SCAN (Oracle) for example. If you do 'select distinct last_name from users;' and you have an index on users (last_name) you do not need to read the table at all. It is enough to read the index. Otherwise it does not matter how many columns you select, because you will have to read all the columns for every row anyway.
- adamzochowski 7y ago
  > Otherwise it does not matter how many columns you select, because you will have to read all the columns for every row anyway. It does matter. An index can actually contain a cache of values for some columns. This is done with INCLUDE statement. CREATE INDEX idx ON book ( author_id ) INCLUDE ( book_title ) This select will use only index: select author_id, book_title from books where author_id = 123 This select will use index, and then have to follow and fetch data out of rows: select * from books where author_id = 123 https://use-the-index-luke.com/blog/2019-04/include-columns-in-btree-indexes https://use-the-index-luke.com/blog/2019-04/include-columns-...