Equivalence of INDEXPROPERTY in PostgreSQL

user_4238587 0
Viewed 55

In SQL Server we have INDEXPROPERTY function. Example of usage: INDEXPROPERTY(ObjectId, IndexName, 'IndexDepth') Is there any PostgreSQL analogs? I didn't find any surfing the Internet.

UPDATE: So we are currently migrating from SQL Server to PostgreSQL and I have to convert a dozen procedures. One of them is responsible of index rebuilding. In the original procedure we check do we have indexes for rebuild. Here is a piece of code I'm talking about:

SELECT @numberoftables = COUNT(DISTINCT objectname)
FROM #fraglist
WHERE (LogicalFrag >= @maxfrag OR [ScanDensity] < @mindensity)
     AND CountPages > 100
     AND INDEXPROPERTY(ObjectId, IndexName, 'IndexDepth') > 0;

#fraglist contains all indexes and their parameters.

postgresql
1 Answers

To determine the depth of a B-tree index, you need the pageinspect extension:

CREATE EXTENSION pageinspect;

SELECT level FROM bt_metap('indexname');

Note that this counts the number of intermediate levels, so an index that consists only of the root page will have a depth of 0.

This function requires superuser privileges.

user_64643080
Related