Over 8 years ago the `indexes` method in the Postgres adapter was updated to "only include valid non-partial indexes of simple column references":
https://github.com/jeremyevans/sequel/commit/7679098eb64dea234143386e2be7031dd4d27091 (This line of code has moved through various changes and is now present here: https://github.com/jeremyevans/sequel/blob/c133f066b2b663d09549c18d967f0d7ebfff3bb9/lib/sequel/adapters/shared/postgres.rb#L434 ) To accomplish this we only select indexes which have the `indcheckxmin` column set to false when the Postgres version is above 8.3. The `indcheckxmin` column was introduced in Postgres 8.3 as part of the HOT (Heap-Only Tuples) performance improvement. Basically because of how HOT works a newly created index won't be be usable for certain transactions: If [`indcheckxmin` is] true, queries must not use the index until the xmin > of this pg_index row is below their TransactionXmin event horizon, because > the table may contain broken HOT chains with incompatible rows that they > can see https://www.postgresql.org/docs/9.6/static/catalog-pg-index.html Explanation of why `indcheckxmin` is needed is explained here: https://github.com/postgres/postgres/blob/master/src/backend/access/heap/README.HOT I don't fully understand Postgres transaction numbers, but I'm almost positive that even if `indcheckxmin` is true then eventually all queries will be able to use the index. Because `indcheckxmin` *may* mean an index cannot be used, the Sequel implementation is technically consistent with the explanation of the commit message. However, I searched through the Postgres source and it seems that `indcheckxmin` is only ever set to `false` when an index is rebuilt. Since this is never guaranteed to happen, Sequel may never return some non-partial simple column reference indexes. I don't know what the justification was for only returning valid indexes 8 years ago, but it seems easier to return all indexes and include `is_valid` / `is_ready` / `is_live` in the returned hash. (There are fields in pg_index for each of these.) I think most users would expect the `indexes` method to match the output of `\d table_name` when using psql, which shows indexes where `indcheckxmin` is true. (If you start psql with the '-E' option it will show what queries it executes when you run things like \d table_name, and that way you can see the query that Postgres runs to fetch all the indexes for a particular table.) -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
