I have a fulltext index on a table. Is it possible to retrieve the set of terms used in the index (gist or gin)? With weights if possible?

To clarify:

If I have the following table:

create table "test" (id integer, thing tsvector);

I then make a GIST index on it:

create index thing_index on test using gist (thing);

Then some data:

insert into test (id, thing)
values (1, 'one'),(2, 'two'), (3, 'three'), (4, 'one'), (5, 'two');

The index thing_index is going contain the following mapping:

'one' => {1, 4}
'two' => {2, 5}
'three' => {3}

I want to obtain the following response from the index:


Maybe even with rankings:

'one' => 2
'two' => 2
'three' => 1

I know I can do this myself by scanning and building my own index, but I want to get it out of Postgres if possible.

  • 1
    What do you mean by "terms used in the index"? A fulltext index indexes all of the words in the column. Aug 16, 2012 at 4:34
  • 1
    It might be worth elaborating on your question a bit, since I suspect nobody is clear on exactly what you want, so they're having a hard time answering. Aug 18, 2012 at 8:12
  • Thanks Josh and Craig, it didn't occur to me that my question might be unclear. I've added a worked example.
    – Joe
    Aug 18, 2012 at 18:58

2 Answers 2


If I understand your question correctly and it isnt at all clear, you are trying to pull back information as far as what id's are connected with a value. I don't think you can just pull it from the index in PostgreSQL because the index will not contain visibility info, and so you will have a LOT of random IO and waiting for platters to turn.

The query for your test case is:

select thing, array_agg(id) from test group by thing;

Assuming you are on a version high enough to have array_agg.

In my system (9.1) this gives me:

chris=> select thing, array_agg(id) from test group by thing;
   thing  | array_agg 
  'one'   | {1,4}
  'two'   | {2,5}
  'three' | {3}
 (3 rows)

that's what you are looking for, right?

  • Thanks, that looks hopeful. I will give it a go. I'm on 9.0 FWIW. How could I make the question? I have given a sample input and output with an explanation of the process by which I would want one to be turned into the other.
    – Joe
    Sep 3, 2012 at 11:07
  • array_agg was introduced I think in 8.4 so you shoudl be good. The question wasn't as clear as to whether you just wanted this sort of aggregation or whether you were trying to see what an index thought a table was supposed to contain (which is usually a superset of what it actually contains). Sep 3, 2012 at 12:07
  • Ah OK. Well I wanted the actual index so as to avoid a full-table scan. I just wanted access to the inverted index as calculated. I took another route in the end (calculating my own) but thought it worth attempting to get it for free first.
    – Joe
    Sep 3, 2012 at 12:12
  • 1
    On PostgreSQL prior to 9.2 it is not possible to do the index and avoid the table. On 9.2 and higher, I suppose you could vacuum and assuming no other queries, you may be able to make use of the index (disabling seq_scan). However sequential scans of the table may actually be faster given discussions on pgsql-general. Sep 3, 2012 at 12:15

And what about:

SELECT * FROM ts_stat('SELECT thing FROM test')                 
ORDER BY nentry DESC, ndoc DESC, word                              
LIMIT 100;

Works at a glance for me (pg9.1) an shows a Hitlist of words used in the documents.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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