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)

Your question is not clear at all, and the referenced question/answer do not help. — Gordon Linoff
– Gordon Linoff, Commented May 6, 2017 at 15:24
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. — TZHX
– TZHX, Commented May 6, 2017 at 15:26
The answer is simple: you don't. You select from the table. If it makes sense, Postgres will retrieve the data from the index. — user330315
– user330315, Commented May 6, 2017 at 15:37
@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. — Robert C. Holland
– Robert C. Holland, Commented May 6, 2017 at 15:38

Community · Accepted Answer · 2017-05-23 11:47:11Z

32

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.

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered May 6, 2017 at 15:53

drdaeman

11.6k8 gold badges63 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Robert C. Holland Over a year ago

Thank you, this is much more helpful than commenters.

Dmytro Zhluktenko Over a year ago

If anybody found any quick solution on how to view the content of index, I would appreciate it!

Lipstick Slaver · Accepted Answer · 2020-04-08 13:41:18Z

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/

Collectives™ on Stack Overflow

How to SELECT data from a postgreSQL INDEX?

2 Answers 2

2 Comments

step 1. Installing pg_filedump in Debian

step 2. Finding path to the index file via SQL

step 3. Look inside the index file

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

step 1. Installing pg_filedump in Debian

step 2. Finding path to the index file via SQL

step 3. Look inside the index file

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related