Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

'one',
'two',
'three'

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.

share|improve this question
1  
What do you mean by "terms used in the index"? A fulltext index indexes all of the words in the column. –  Josh Berkus Aug 16 '12 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. –  Craig Ringer Aug 18 '12 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 '12 at 18:58
add comment

migrated from stackoverflow.com Aug 15 '12 at 15:09

This question came from our site for professional and enthusiast programmers.

1 Answer

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?

share|improve this answer
    
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 '12 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). –  Chris Travers Sep 3 '12 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 '12 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. –  Chris Travers Sep 3 '12 at 12:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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