Joe: improve recommendations
Goal
"Recommendation v0.2": suggestions based on Filter: ..
, improve current items.
Related issue: #19
TODO / How to implement
-
Do not complain about SeqScan if we hit+read < 50 buffers -
When discussing SeqScan, use this link https://docs.gitlab.com/ee/development/understanding_explain_plans.html#optimising-queries -
Instead of 2 current recommendations related to buffers hit/read, show only one: "Query processes too much data (XXX MiB) to return the relatively small number of rows (XXX) - Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. -
If Filter: XXX
for Index Scan appears, and the value of filtered out rows exceeds 100, recommend the creation of another index -
If Heap Fetches: XXX
for Index Only Scan appears and XXX is no 0, recommend applying VACUUM ANALYZE for the table (exec vacuum analyze {{table_name}}
) -
If we see temporary buffers written, recommend raising work_mem (currently, the recipe is: exec alter system set work_mem to '100MB';
, thenexec select pg_reload_conf()
) -
Lack of LIMIT -- if the number of rows in the result set exceeds 10000, this is not normal for OLTP -
NO OFFSET!!!!! https://use-the-index-luke.com/no-offset
Acceptance criteria
Edited by Anatoly Stansler