3

In SQL Server you could check specific index information using the DBCC PAGE command. Is there anything similar in postgres?

And if there isn't, how would you get the same info then? This is simply for research purposes.

This command returns this:

Page @0x00000004ED67A000

m_pageId = (1:299)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x4
m_objId (AllocUnitId.idObj) = 121   m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594045857792
Metadata: PartitionId = 72057594040614912                                Metadata: IndexId = 1
Metadata: ObjectId = 99             m_prevPage = (1:297)                m_nextPage = (1:300)
pminlen = 4008                      m_slotCnt = 2                       m_freeCnt = 70
m_freeData = 8118                   m_reservedCnt = 0                   m_lsn = (35:184:44)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = ALLOCATED 
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                        DIFF (1:6) = CHANGED 
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 4011
4
  • 1
    What information does this return in SQL Server? Is pageinspect something similar or maybe amcheck? Nov 4, 2019 at 9:14
  • added picture to show the output @a_horse_with_no_name
    – Chessbrain
    Nov 4, 2019 at 9:26
  • so what is that information? What does it tell you that you want to know about indexes in Postgres? Nov 4, 2019 at 14:39
  • for example stuff like m_prevPage and m_nextPage show which pages are before and after the current page you're looking at (horizontally not vertically, talking about btree structure) m_freeCnt for example shows how much space is not used in that page etc...
    – Chessbrain
    Nov 4, 2019 at 16:08

1 Answer 1

2

You can

CREATE EXTENSION pageinspect;

Then you can use the function

SELECT * FROM bt_page_stats('indexname' , 42);

to get some information about block 42 of the index: the number of live and dead entries, the amount of free space and the pointers to the left and right sibling (btpo_prev and btpo_next) and the type (root, intermediate or leaf).

2
  • How do you get the block numbers?
    – Chessbrain
    Nov 5, 2019 at 9:04
  • 1
    You start with bt_metap() to get the root page and proceed from there. Nov 5, 2019 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.