On Sun, Dec 31, 2017 at 1:39 PM, Peter Geoghegan <pg@xxxxxxx> wrote: > SELECT bt_index_check(index => c.oid, heapallindexed => true), > c.relname, > c.relpages > FROM pg_index i > JOIN pg_opclass op ON i.indclass[0] = op.oid > JOIN pg_am am ON op.opcmethod = am.oid > JOIN pg_class c ON i.indexrelid = c.oid > JOIN pg_namespace n ON c.relnamespace = n.oid > WHERE am.amname = 'btree' AND n.nspname = 'pg_catalog' > -- Don't check temp tables, which may be from another session: > AND c.relpersistence != 't' > -- Function may throw an error when this is omitted: > AND i.indisready AND i.indisvalid > ORDER BY c.relpages DESC LIMIT 10; > > As I mentioned earlier, if this takes too long, you could only do > heapallindexed checking once per table (not once per index) by giving > "indisprimary" as the heapallindexed argument. That way, only primary > keys would be verified against the heap, which is potentially a lot > faster. Oh, and I think that you should remove the "ORDER BY c.relpages DESC LIMIT 10", too. -- Peter Geoghegan
