CODEWITHSUNDEEP
djangopostgresqlindexing
nmajoros
nmajoros

Reputation: 127

Django PostgreSQL double index cleanup

We've got this table in our database with 80GB of data and 230GB of Indexes. We are constrained on our disk which is already maxed out.

What bothers me is we have two indexes that look pretty darn similar

 CREATE        INDEX tracks_trackpoint_id   ON tracks_trackpoint USING btree (id)   
 CREATE UNIQUE INDEX tracks_trackpoint_pkey ON tracks_trackpoint USING btree (id)

I have no idea what's the history behind this, but the first one seems quite redundant. What could be the risk of dropping it ? This would buy us one year of storage.

Upvotes: 0

Views: 109

Answers (2)

nmajoros
nmajoros

Reputation: 127

Careful as I am, I disabled the index to benchmark this, and the query seems to fallback nicely on the other index. I'll try a few variants.

appdb=# EXPLAIN analyze SELECT * FROM tracks_trackpoint where id=266082;
 Index Scan using tracks_trackpoint_id on tracks_trackpoint  (cost=0.57..8.59 rows=1 width=48) (actual time=0.013..0.013 rows=0 loops=1)
   Index Cond: (id = 266082)
 Total runtime: 0.040 ms
(3 rows)

appdb=# UPDATE pg_index SET indisvalid = FALSE WHERE indexrelid = 'tracks_trackpoint_id'::regclass;

appdb=# EXPLAIN analyze SELECT * FROM tracks_trackpoint where id=266082;
 Index Scan using tracks_trackpoint_pkey on tracks_trackpoint  (cost=0.57..8.59 rows=1 width=48) (actual time=0.013..0.013 rows=0 loops=1)
   Index Cond: (id = 266082)
 Total runtime: 0.036 ms
(3 rows)

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 248215

You can drop the first index, it is totally redundant.

If your tables are 80GB and your indexes 230GB, I am ready to bet that you have too many indexes in your database.

Drop the indexes that are not used.

Upvotes: 1

Related Questions