![]() ![]() ![]() It is highly commendable to set track_io_timing = on to get data about the I/O performance. Include SETTINGS if you are on v12 or better and WAL for data modifying statements from v13 on. Typically, the best way to call EXPLAIN is:ĮXPLAIN (ANALYZE, BUFFERS) /* SQL statement */ The other formats ( XML, JSON and YAML are better for automated processing. The default format TEXT is the best for humans to read, so use that to analyze query performance. FORMAT: this specifies the output format.You can only use it together with ANALYZE. WAL: introduced in v13, this option shows the WAL usage incurred by data modifying statements.SETTINGS: this option exists since v12 and includes all performance-relevant parameters that are different from their default value in the output.This is usually just clutter, and you are better off without it, but it can be useful if the executor spends its time in a frequently-executed, expensive function. VERBOSE: if you specify this option, EXPLAIN shows all the output expressions for each step in an execution plan.BUFFERS: You can only use this keyword together with ANALYZE, and it shows how many 8kB-blocks each step reads, writes and dirties.This is indispensable for analyzing SQL performance. ![]() ANALYZE: with this keyword, EXPLAIN does not only show the plan and PostgreSQL’s estimates, but it also executes the query (so be careful with UPDATE and DELETE!) and shows the actual execution time and row count for each step.You can obtain such additional information by adding EXPLAIN options in parentheses. This is helpful, but typically you need more information. The EXPLAIN command shows you the execution plan that is generated by the optimizer. In SQL, it is harder to see how the engine spends its time, because it is not a procedural language, but a declarative language: you describe the result you want to get, not how to calculate it. If you have an SQL statement that executes too slowly, you want to know what is going on and how to fix it. Rather, I want to give you a brief introduction, explain what to look for and show you some helpful tools to visualize the output. This article does not attempt to explain everything there is to it. Analyze buffers cache hit execution plan EXPLAIN performance postgresql track_io_timing tuningĮXPLAIN ANALYZE is the key to optimizing SQL statements in PostgreSQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |