Usage of Explain with Buffer option in PostgresSQL and a PostgreSQL execution plan visualizer

The Buffer option as past of PostgreSQL and Active Record Explain can help to identify where we could potentially improve the PostgreSQL Query performance further and the PostgreSQL execution plan visualizer makes it easier to identify which part of the query is relatively more resource intensive

The above is a presentation used as part of giving a lightning talk given at Euruko 2024 which gives one a high level overview of how to use the above Buffer option and the PostgreSQL execution plan visualizer tool

The example syntax of how to use buffers options in Rails with Active Record explain is: `Company.includes(:owner).where(id: owning_companies_ids).explain(:analyze, :buffers) `

The Buffer option wasn't initially documented in Rails guides(when it was originally to rails) and I've attempted to add a PR here: https://github.com/rails/rails/pull/52893 to fix this so that it could be added to the Rails Guides for PostgreSQL: https://guides.rubyonrails.org/active_record_postgresql.html

Transcript

1. Usage of Explain with Bu ff er option in PostgresSQL and a PostgreSQL execution plan visualizer 1
2. API request and its varying response times API request had max response times of 10s, 20s(with 500 Error) 2
3. - Average response time: 2.5s - Other higher response times: 5s+ & 10s+ - Max response time: 20s+ (and erroring out with 500) Varying API response times included 3
4. A Count SQL query was taking majority of the API execution time 4
5. Explain Buffer on Count SQL Query with Actual API param values applied on https://explain.dalibo.com/ Count Query With Explain Bu ff er Option using actual param values from API 5
6. PostgreSQL execution plan visualizer: https://explain.dalibo.com/ 6
7. PostgreSQL visualizer(with sample execution plan): https://explain.dalibo.com/ 7 This uses the Sample Plan readily available on https://explain.dalibo.com/
8. Grid Breakdown of https://explain.dalibo.com/
9. Stats Breakdown of which table computation in the query is more time intensive Query planner breakdown can be obtained here: https://explain.dalibo.com/ 9
10. https://explain.dalibo.com/ - makes visualizing and understand PostgreSQL Explain plans easier 10
11. What does the Buffer option of Explain offer? - Gives an insight into the amount of data being fetched as part of a query operation - The amount of fetched data adds latency to the retrieval speed of the fi nal query result set -Types of fetched data includes blocks that are - Hit - Read - Dirtied - Written 11
12. Overview of each insight provided by the Buffer option 12 - Block: Fundamental working unit that Postgres uses when it reads data or writes data from your tables. - Insights provided by the buffer option - Hit: When a read was avoided because the block was found already in cache during query processing - Read: When having to read data from a source like disk during query processing - Dirtied: Indicates the number of previously unmodi fi ed blocks that were changed by this query - Written: Number of previously-dirtied blocks are removed/evicted from cache during query processing
13. Why should I care about the Buffer option? - At scale IO can be a signi fi cant performance bottleneck for databases - Bu ff er option is a great way to identify problem areas of a query - It could give potential indicators of where to begin one’s query optimisations - Getting a sense of the total I/O of the query - Spotting operations doing way more I/O than you expected - Provide a good way to verify if any query optimizations we make have reduced IO usage further 13
14. Analysing usage details(like duration) further on the jobs table 14
15. Convert blocks used to actual disk space utilized Reading 10GB of data from the shared in memory cache is huge - 391 blocks read from the disk(cache misses) - 1 Block is 8kb => 8 * 1024 bytes(using 1kb = 1024 bytes) - 391 blocks ==> 391 * 1024 * 8 = 3,203,782 bytes - 3,203,782 bytes => to convert to MB ==> (3,203,782/(1024 * 1000)) = 3.12 MB 15
16. PostgreSQL’s buffer option added as part of Rails 7.1 - Bu ff ers option introduced in Rails 7.1 in this PR: https://github.com/rails/rails/pull/47043 16
17. Rails syntax to use buffers with ActiveRecord::Relation#explain - Example query: Company.includes(:owner).where(id: owning_companies_ids).explain(:analyze, :bu ff ers) 17
18. A PR do add the buffers option Rails guides for PostgreSQL 18
19. Credits and I’m Thankful to -My Ruby friend Jan Szumiec for introducing me to the Bu ff er option -Andrew Atkinson’s book: High Performance PostgreSQL for Rails -https://www.linkedin.com/pulse/explain-analyze-bu ff ers-shyam-kumar-6pqpc/ -https://github.com/rails/rails/pull/47043 -https://www.bigbinary.com/blog/rails-7-1-adds-options-to-activerecord-relation- explain -https://willj.net/posts/bu ff er-analysis-when-using-explain-analyse-in-postgres/ -https://pganalyze.com/blog/5mins-explain-analyze-bu ff ers-nested-loops -https://tatiyants.com/postgres-query-plan-visualization/ -The people behind Dalibo for https://explain.dalibo.com/ - Anybody else I may have unintentionally missed(my sincere apologies for having missed you) 19
20. Thank you for holding the space to listen 🙂 Any feedback is much appreciated :) Name Mohnish G Jadwani Work As Freelance Senior Ruby developer | Agile coach Email mail4mohnishj@gmail.com LinkedIn https://www.linkedin.com/in/mohnish-jadwani-9a924619/ Currently I’m open to work & here’s how I intend to be of service 20