CODEWITHSUNDEEP
postgresqlindexinggwt-gintrigram
John Doe
John Doe

Reputation: 1639

postgresql : read GIN index content

I would like to read the trigram list supposedly created with my GIN index but I can't seem to find the command anywhere: I tried \di+, \dv+, \dm+ or each without the +. Nothing returns.

Do I misunderstand something or my index is empty? It shows a 16ko size but it could be the empty standard size as far as I know.

Upvotes: 2

Views: 512

Answers (2)

ErMejo
ErMejo

Reputation: 141

There is an indirect way to obtain this information, but only if you are using the GIN index on a tsvector column.

Table structure

CREATE TABLE bench(
        id bigserial,
        f1 text,
        f2 text,
        f3 text,
        f4 text
        );

Populate table bench with some data. Add the tsvector column and update it:

ALTER TABLE bench
    ADD COLUMN ts tsvector;
UPDATE bench
    SET ts = 
        strip(to_tsvector('simple',coalesce(f1,''))
        ||to_tsvector('simple',coalesce(f2,''))
        ||to_tsvector('simple',coalesce(f3,''))
        ||to_tsvector('simple',coalesce(f4,''))
        )
    ;

Create the GIN index

CREATE INDEX ooz_gin ON bench USING GIN (ts) WITH (fastupdate=on);

List all known words in the ts column (and thus in the index) and their trigrams:

SELECT word,show_trgm(word) as trigrams
    FROM ts_stat('SELECT ts FROM bench');

Result (with my test data):

        word         |                                        trigrams                                         
---------------------+-----------------------------------------------------------------------------------------
 zworykin            | {"  z"," zw","in ",kin,ory,ryk,wor,yki,zwo}
 zulu                | {"  z"," zu","lu ",ulu,zul}
 zorch               | {"  z"," zo","ch ",orc,rch,zor}
 zoning              | {"  z"," zo",ing,"ng ",nin,oni,zon}
 zips                | {"  z"," zi",ips,"ps ",zip}
 zipping             | {"  z"," zi",ing,ipp,"ng ",pin,ppi,zip}
 zippered            | {"  z"," zi","ed ",ere,ipp,per,ppe,red,zip}
 ziploc              | {"  z"," zi",ipl,loc,"oc ",plo,zip}
 zion                | {"  z"," zi",ion,"on ",zio}

Upvotes: 2

user330315
user330315

Reputation:

It's not possible to see the data stored inside an index using plain SQL (and the psql backslash commands are simply translated to SQL).

If you want to inspect the internal of the blocks of an index, you need to install the extension pageinspect which also has some functions specifically for GIN indexes

Upvotes: 4

Related Questions