Use single query to retrieve indexes in PostgreSQL

[fatkodima]

Previously, to get all indexes for the database in PostgreSQL, it was needed # of tables + # of indexes sql queries. Now, it is only # of tables sql queries.

For example, for gitlab's source base, which has 500 tables and 2000 indexes, it was needed 2500 queries, now 500.

This PR greatly reduces the time needed to generate a schema.rb file, for example. Or in other tools, where it is needed to retrieve all the indexes from the database. For example, I was able to speedup active_record_doctor by 3x (from 126 seconds to 39 seconds) using schema caching - https://github.com/gregnavis/active_record_doctor/pull/101. And with this PR, it reduces execution time by another 8 seconds.

[simi]

Is getting all indexes the most common usage? Can't we make it into 1 query and iterate over the result safely (in batches)?

[fatkodima]

Is getting all indexes the most common usage? Can't we make it into 1 query and iterate over the result safely (in batches)?

I didn't get it. We do not get all indexes in a single query, we get all indexes during the execution of the program (like in active_record_doctor, mentioned in the PR description). We get all indexes per table at a time.

[simi]

Is getting all indexes the most common usage? Can't we make it into 1 query and iterate over the result safely (in batches)?

I didn't get it. We do not get all indexes in a single query, we get all indexes during the execution of the program (like in active_record_doctor, mentioned in the PR description). We get all indexes per table at a time.

Is for example active_record_doctor getting info for all tables?

[fatkodima]

You can disable specific tables for specific checks, but usually all tables in the db are checked.

[fatkodima]

@yahonda Can you, please, take a look at this PR?

[yahonda]

I'd like https://github.com/rails/rails/pull/45381/files#r899190812 discussion to be resolved between @fatkodima and @matthewd .