Postgresql – Disappearing index

postgresql

We are running postgresql on a single instance on AWS RDS, currently on version 11.5
when creating the following index:

CREATE INDEX foo_idx
    ON public.foo_table USING gin
    (foo_col COLLATE pg_catalog."default" gin_trgm_ops)
    TABLESPACE pg_default;

it is created and queries utilizing it run fine, but after a number of them the index just disappears. recreating it works fine but it disappears again.

There is noting in the error logs, and we are not sure how to troubleshoot.

Best Answer

Thanks to Laurenz Albe:

Set log_statement = 'ddl' in postgresql.conf to log DDL statements. Also, verify that you didn't create the index in a transaction that got rolled back later.

Logging DDL statements surfaced the cause of the drop.

Related Solutions

PostgreSQL Indexing – PostgreSQL Not Using Index

The problem here is that it thinks to_tsvector is a very cheap operation to perform, even when performing it on an entire table. Basically, it think it is the same cost as adding together two integers. But of course it is not. In the upcoming release 9.5, the default cost for that function has been increased 100-fold (which is probably still not enough to be accurate...but it should be enough to tip the scales for your case, which is all you need).

You can change the cost of that function yourself:

alter function to_tsvector ( regconfig, text) cost 1000;

(Note that because this is a built-in function, changes you make will not survive a pg_upgrade, or a dump, initdb and restore cycle)

This is much safer than fiddling around with the global cost parameters.

The general (global) cost parameters are described here, and ones for specific functions are described here. The way the two interact with each other don't seem to be well-described anywhere in the docs.

Postgresql – Optimizing PostgreSQL with Index

I found a better index. I think I was reading the query plan results, which caused me to determine the wrong index order.. Now I'm reading it from bottom to top, and it gives me this index:

create index index1 on lineorder(lo_partkey,lo_suppkey,lo_orderdate);

However, is there anyway of making it even better??

Best Answer

Related Solutions

PostgreSQL Indexing – PostgreSQL Not Using Index

Postgresql – Optimizing PostgreSQL with Index

Related Question