3 ms·

Can someone expand on this statement (from the top answer, last paragraph): "Since indices are only used to speed up the searching for a matching field within

Can someone expand on this statement (from the top answer, last paragraph): "Since indices are only used to speed up the searching for a matching field within the records, it stands to reason that indexing fields used only for output would be simply a waste of disk space and processing time when doing an insert or delete operation, and thus should be avoided." Specifically "indexing fields used only for output", what does this mean? I interpreted it as "indexes used only for `select` statements", but that would see completely counter to the main reason you'd want an index e.g. to speed up record retrieval.

esnard 5y ago
"fields used only for output" means "fields that aren't used in any WHERE, ORDER BY, GROUP BY clauses". They are just SELECTed, i.e. sent in the output (the query results).
geophile 5y ago
I think that advice is not necessarily correct. Suppose you have this query: select a, b from T where x = 1 And suppose you have an index on x. You can locate the x=1 INDEX records using the index quickly (probably no more than 1-3 disk accesses). But then the qualifying TABLE records have to be retrieved. That index lookup could turn up thousands of qualifying records, and now you have to retrieve each record to get the a, b values. Now suppose that you replace your index on (x) with an index keyed by (x, a, b). You can still search for x=1 using this index, but now, the (a, b) values that you are SELECTing are present in the index itself. You don't have get the table records to get those values. That can save a lot of random accesses, and there are secondary effects, since the pages containing those records aren't brought into the disk cache. Yes, this wider index has space and update costs, but the benefit is often worth it.
- deleted 5y ago
  [deleted]
- uhoh-itsmaciek 5y ago
  Right, Postgres supports this use case explicitly with INCLUDE [1], which can be more efficient than just indexing all the columns you need. Pretty sure SQL Server has something similar. [1]: https://www.postgresql.org/docs/current/sql-createindex.html https://www.postgresql.org/docs/current/sql-createindex.html
  - geophile 5y ago
    Yes, Postgres has been playing various tricks in the last few years, to make this optimization actually optimize things, but the Postgres MVCC approach makes it difficult. The problem is that index information can be stale, and that you often have to go to the indexed page to determine current state. They've made progress to lessen that "often" to "sometimes", but unless version information is added to indexes, they won't be able to fix this completely. I was teaching a database course a couple of years ago, using Postgres, and in exercises on query optimization, I found it surprisingly difficult to get columns added to indexes to produce a convincing improvement.
fabian2k 5y ago
That part is wrong if your database can perform index-only queries. In that case if all columns you query are part of the index the query can be answered from the index alone, so you don't have to read any parts of the actual table.

esnard 5y ago

"fields used only for output" means "fields that aren't used in any WHERE, ORDER BY, GROUP BY clauses". They are just SELECTed, i.e. sent in the output (the query results).

geophile 5y ago

I think that advice is not necessarily correct. Suppose you have this query: select a, b from T where x = 1 And suppose you have an index on x. You can locate the x=1 INDEX records using the index quickly (probably no more than 1-3 disk accesses). But then the qualifying TABLE records have to be retrieved. That index lookup could turn up thousands of qualifying records, and now you have to retrieve each record to get the a, b values. Now suppose that you replace your index on (x) with an index keyed by (x, a, b). You can still search for x=1 using this index, but now, the (a, b) values that you are SELECTing are present in the index itself. You don't have get the table records to get those values. That can save a lot of random accesses, and there are secondary effects, since the pages containing those records aren't brought into the disk cache. Yes, this wider index has space and update costs, but the benefit is often worth it.

deleted 5y ago
[deleted]
uhoh-itsmaciek 5y ago
Right, Postgres supports this use case explicitly with INCLUDE [1], which can be more efficient than just indexing all the columns you need. Pretty sure SQL Server has something similar. [1]: https://www.postgresql.org/docs/current/sql-createindex.html https://www.postgresql.org/docs/current/sql-createindex.html
- geophile 5y ago
  Yes, Postgres has been playing various tricks in the last few years, to make this optimization actually optimize things, but the Postgres MVCC approach makes it difficult. The problem is that index information can be stale, and that you often have to go to the indexed page to determine current state. They've made progress to lessen that "often" to "sometimes", but unless version information is added to indexes, they won't be able to fix this completely. I was teaching a database course a couple of years ago, using Postgres, and in exercises on query optimization, I found it surprisingly difficult to get columns added to indexes to produce a convincing improvement.

fabian2k 5y ago

That part is wrong if your database can perform index-only queries. In that case if all columns you query are part of the index the query can be answered from the index alone, so you don't have to read any parts of the actual table.