This is a table that helps me a lot when debugging my queries.
select * from pg_stat_activity;
it gives you a lot of useful information, for example you can see which queries are taking too long:
SELECT pid, query from pg_stat_activityWHERE state = 'active' AND (now() - query_start) > interval '10 seconds';
Indexes are great for performance but they can be very painful if misused.
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. -- Wikipedia
Basically, if you query a table by certain columns often enough you can create an index that will improve the performance of such query greatly.
The cost is that you save that index into memory (RAM) which is limited.
SELECTtable_name,pg_size_pretty(table_size) AS table_size,pg_size_pretty(indexes_size) AS indexes_size,pg_size_pretty(total_size) AS total_size,ROUND(indexes_size/1024/1024) as indexes_size_mb,ROUND(total_size/1024/1024) as total_size_mbFROM (SELECTtable_name,pg_table_size(table_name) AS table_size,pg_indexes_size(table_name) AS indexes_size,pg_total_relation_size(table_name) AS total_sizeFROM (SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_nameFROM information_schema.tables) AS all_tablesORDER BY total_size DESC) AS pretty_sizes;
Basely on how much GB (gigabytes) we use today, how much GB the table that we want to add the index uses and how much GB are available in the server we may decided to add the index or find a different solution to improve performance.