On Sun, Dec 31, 2017 at 1:39 PM, Peter Geoghegan <pg@bowt.ie> 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
