postgresqlazurepg-trgm

PostgreSQL GIN index size stuck at 0 bytes

I have the following kind of a database running in Azure Cosmos DB for PostgreSQL (latest version of PostgreSQL ans citus):

testingtable

id     SERIAL NOT NULL PRIMARY KEY,
first_name   TEXT NOT NULL,
last_name   TEXT NOT NULL,
full_name   TEXT NOT NULL

And then I have made a GIN index as follows:

CREATE INDEX testing_full_name_gin ON testingtable USING gin (full_name gin_trgm_ops);

The weird thing is, that I have let it run for a while, I have used the REINDEX feature as well, but when I check the size of the index by calling:

SELECT pg_size_pretty (pg_indexes_size('public.testing_full_name_gin'));

It always returns size of 0 bytes. What is going on? Is it because there is only about 17k rows in that table?

I'm trying to make a search as fast as possible to the PostgreSQL using SIMILARITY -function, and would really appreciate all tricks to make this query as fast as possible.

Solution

  • You used the wrong function. See the PostgreSQL documentation:

    pg_indexes_size ( regclass ) → bigint

            Computes the total disk space used by indexes attached to the specified table.

    Now public.testing_full_name_gin has no indexes attached, it is an index.

    Try using pg_relation_size().