10

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)

Robert C. Holland
  • 1,651
  • 4
  • 24
  • 57
  • 1
    Your question is not clear at all, and the referenced question/answer do not help. – Gordon Linoff May 06 '17 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 May 06 '17 at 15:26
  • I've edited to hopefully clarify the question. – Robert C. Holland May 06 '17 at 15:30
  • 5
    The answer is simple: you don't. You select from the table. If it makes sense, Postgres will retrieve the data from the index. –  May 06 '17 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 May 06 '17 at 15:38
  • Are you trying to ask how you would retrieve the data that is stored in column `id` within table `tbname`, or are you trying to ask how you would subsequently be able to determine the definition of index `ixname` ? –  May 06 '17 at 15:39
  • @MarkBannister I want to view the sorted data stored in index, not from the table or column it was created on. – Robert C. Holland May 06 '17 at 15:41
  • Robert Holland - see a_horse_with_no_name's comment. –  May 06 '17 at 15:42
  • Again, that's not doing what you seem to think it is. But if you are looking for that, your question is a duplicate of http://stackoverflow.com/questions/309786/how-do-i-force-postgres-to-use-a-particular-index – TZHX May 06 '17 at 15:54

2 Answers2

29

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.

Community
  • 1
  • 1
drdaeman
  • 11,159
  • 7
  • 59
  • 104
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/

Lipstick Slaver
  • 31
  • 2