sqldatabasepostgresql

PostgreSQL thinks there are no indices, when there are

We have one large PostgreSQL in our production. Recently, we saw that our queries have become extremely slow. So we inspected it and it all came down to checking the indices for a sample table. So we ran the following query to check if there were any indices:

select * from pg_indexes where tablename = 'DriverHourlyOnlineHoursNew'

We get no results for this query, which means there are no indices on our sample table. This is a bit weird, because we were almost sure that we had a btree index on two columns in this table.

So we tried adding that index to the table. We ran the following query:

create index "DriverHourlyOnlineHoursNew_driverId_date" on "DriverHourlyOnlineHoursNew" using btree("driverId",date);

and surprisingly we get the following error:

ERROR:  relation "DriverHourlyOnlineHoursNew_driverId_date" already exists

So it turns out the index actually exists, but PostgreSQL thinks that it does not!

I was wondering if anybody has any ideas what this problem is and how it can be resolved?

Thanks

--Update: The first query that is ran on the pg_indexes view responds randomly! Meaning that sometimes it returns an empty result set, and sometimes it returns correct results. One more thing I should add that proves this is not a naming problem is that running the first query returns no results, while PostgreSQL creates a default index on primary key columns and therefore returning no results means that there is something wrong with the pg_indexes view.

Solution

  • Did you just tried to do something like this :

    DROP INDEX DriverHourlyOnlineHoursNew_driverId_date;

    Then create it again :

    CREATE INDEX on DriverHourlyOnlineHoursNew (driverId, date);

    You should have a pg_* table corruption. Did you VACUUM your database regularly ?

    Otherwise what you can do is simply :

    SELECT * INTO TEMP tmp_DriverHourlyOnlineHoursNew FROM DriverHourlyOnlineHoursNew;

DROP TABLE DriverHourlyOnlineHoursNew;

SELECT * INTO DriverHourlyOnlineHoursNew FROM tmp_DriverHourlyOnlineHoursNew;

CREATE INDEX on DriverHourlyOnlineHoursNew (driverId, date);

    And you should recover a "normal table".

    Do not hesitate to do regular :

    VACUUM ANALYZE;

    This is not blocking and clean your database.