How to SELECT data from a postgreSQL INDEX?

Question

If I created an index with following command: CREATE INDEX ixname ON tbname (id);

Where ixname is the name of index, tbname is the table name for which the index is being created and id is the column the index is for.

Now, if I wanted to view what's in ixname, how would I do it? (I'm asking with the assumption that an index is a relation/table with sorted column)

• Gordon Linoff: Your question is not clear at all, and the referenced question/answer do not help.
• TZHX: The referenced answer doesn't really do what you seem to think it does. If you want to know what an index includes, look at its definition and write a query that gets just that from the relevant table if you care to do so.
• Robert C. Holland: I've edited to hopefully clarify the question.
• user330315: The answer is simple: you don't. You select from the table. If it makes sense, Postgres will retrieve the data from the index.
• Robert C. Holland: @a_horse_with_no_name Yes, I know I'm not suppose to fetch anything from index, I understand what indexes are for. But as learning exercise and to ensure everything in database is properly set, I wanted to view what's in the index relation.

Answer 1

You just can't. Not as a client, not using SQL.

Data in the index is internal to PostgreSQL, and it's not accessible to the outside world. You can introspect your index definitions (using pg_indexes table or pg_get_indexdef function), but you can't look up what's actually stored in those.

Well, you technically can find the file(s) in which the index data is stored (use pg_class.relfilenode and checking for files in base/ subdirectory), and decode the binary data of their b-trees (or whatever your indexes use), but I'm not sure this is what you want to do. Unless you intend to learn or hack PostgreSQL internals.

• Robert C. Holland: Thank you, this is much more helpful than commenters.
• Dmytro Zhluktenko: If anybody found any quick solution on how to view the content of index, I would appreciate it!

Answer 2

You can with pg_filedump utility

step 1. Installing pg_filedump in Debian

$ git clone git://git.postgresql.org/git/pg_filedump.git
$ cd pg_filedump/
$ make
$ make install
# note the line /usr/bin/install -c pg_filedump '/usr/lib/postgresql/17/bin'
# add env var
$ PATH=/usr/lib/postgresql/17/bin
# check that it works
$ pg_filedump -h

step 2. Finding path to the index file via SQL

SHOW data_directory;
-- /var/lib/postgresql/17/main
-- list your indices
SELECT *
FROM pg_indexes;
-- locate particular index
SELECT pg_relation_filepath('name_of_target_index');
-- let's say output is
-- base/123/4567

step 3. Look inside the index file

$ cd /var/lib/postgresql/17/main/base/123/
$ pg_filedump -D int,varchar 4567

Play with pg_filedump parameters to get nice visualization. Inspired by https://blog.dbi-services.com/displaying-the-contents-of-a-postgresql-data-file-with-pg_filedump/