Postgres Indices: how can they got lost (missing indices)?

TauCeti 0
Viewed 21

I have a question concerning postgres-indices:

  1. What "field" exactly is covered by an index? Is it correct, that indices are effective on columns? Does each field in the column then has an index? Is it correct, that all columns used in queries should have an index?

  2. By comparison of seq-scan and idx-scan one can detect missing indices. What exactly is a missing index and how can they got lost?

indexing postgresql
1 Answers
  1. No, not all columns should have an index. Indexes are for use on the columns that are used in filtering critera; ON, WHERE, HAVING. Generally, only the most selective column or columns should be used to define the index (and you can use more than one column). Now, some queries can benefit from adding columns to the INCLUDE clause of an index. But, that's not the same as making them the keys. The basics for indexes are here.
  2. Indexes aren't really missing. That's the optimizer making a suggestion for a candidate index. Don't assume it's correct. Test and validate the suggestion.
Grant Fritchey0
Related