On Sun, Dec 31, 2017 at 1:10 PM, Ibrahim Edib Kokdemir
<kokdemir@gmail.com> wrote:
> I just installed and used amcheck_next, I have used your sample query on the
> git page (changed the schema name) and that listed all indexes different
> schemes and produced same outputs like yours with bt_index_check field as
> empty, that means no error.
> Am I doing right?
You should give the argument of heapallindexed as 'true'. So:
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.
--
Peter Geoghegan