Indexing BD records

Question

I recently circled a EXPLAIN ANALYZE in my database which is fairly large, about 800mil records in some tables, and I noticed that the indexing methods adopted were not the best. I always let the bank decide what was the best way to index the records, only I have noticed that it is something common the error of choosing the method, so:

• There are good practices for indexing files in the database?

• Knowing my data set, how can I know which is the best indexing technique?

• There are tools, besides the BD itself, that can help me?

Answer 1

I can’t comment, so I’ll put it in answer form.

The bank will be based on ANALYZE[1] to make sure you’re making the right decision. Always remember to keep self-enalyze strong enough.

Check if the table you are monitoring ran an analyze and when was the last time; and if necessary, do it manually:

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, n_tup_ins, n_tup_upd, n_tup_del, n_dead_tup 
FROM pg_stat_all_tables  
WHERE schemaname = 'public'
AND relname = 'tablename'
;

The bank will hardly make a wrong decision on this. To have more idea of what is happening, it would be good to post here some data, as the output da query above, and the explain analyze of the query you ran.

[1] https://www.postgresql.org/docs/current/static/sql-analyze.html