Indexing BD records

Question

Indexing BD records

Navigation

#1 by (0 votes)

0

Recently I ran a EXPLAIN ANALYZE in my database that is fairly large, about 800,000 records in some tables, and I noticed that the indexing methods adopted were not the best. I've always let the bank decide how best to index the records, but I've noticed that it's a common mistake to choose the method, so:

Are there good practices for indexing files in the database?
Knowing my data set, how can I know which one is the best indexing technique?
Are there tools besides the DB itself that can help me?

postgresql

asked by anonymous 07.07.2017 / 22:04

1 answer

Should I build a constructor with Setter? PHP [duplicate] How to leave a "Default" language in GitHub?

score 0 · Answer 1

I can not comment, so I'll go in response.

The bank will rely on ANALYZE [1] to make sure you are making the right decision. Always remember to keep the autoanalize strong enough.

Verify that 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 the wrong decision on this. To get a better idea of what's happening, you'd better post some data, such as output da query up, and explain analyze of the query you ran.

[1] link