2

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.

John Doe
  • 1,570
  • 3
  • 13
  • 22

2 Answers2

4

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

2

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}
ErMejo
  • 91
  • 1