Half a year ago - public to PostgreSQL.
Over the past months we have done about him , prepared a summary based on the recommendations that it gives out ... but most importantly, we collected your feedback and looked at real use cases.
And now we are ready to tell you about the new features that you can use.
Support for different plan formats
Plan from the log, along with the request
Directly from the console, we select the entire block, starting from the line with Query Text, with all leading spaces:
Query Text: INSERT INTO dicquery_20200604 VALUES ($1.*) ON CONFLICT (query)
DO NOTHING;
Insert on dicquery_20200604 (cost=0.00..0.05 rows=1 width=52) (actual time=40.376..40.376 rows=0 loops=1)
Conflict Resolution: NOTHING
Conflict Arbiter Indexes: dicquery_20200604_pkey
Tuples Inserted: 1
Conflicting Tuples: 0
Buffers: shared hit=9 read=1 dirtied=1
-> Result (cost=0.00..0.05 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=1)
... and throw everything copied directly into the field for the plan, without separating anything:
At the output, we also get a bonus to the disassembled plan context tab, where our request is presented in all its glory:
JSON and YAML
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pg_class;"[
{
"Plan": {
"Node Type": "Seq Scan",
"Parallel Aware": false,
"Relation Name": "pg_class",
"Alias": "pg_class",
"Startup Cost": 0.00,
"Total Cost": 1336.20,
"Plan Rows": 13804,
"Plan Width": 539,
"Actual Startup Time": 0.006,
"Actual Total Time": 1.838,
"Actual Rows": 10266,
"Actual Loops": 1,
"Shared Hit Blocks": 646,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
"Planning Time": 5.135,
"Triggers": [
],
"Execution Time": 2.389
}
]"Even with external quotes, as pgAdmin copies, even without - we throw in the same field, the output is beauty:
Advanced visualization
Planning Time/Execution Time
Now you can better see where the extra time went when executing the query:
I/O Timing
Sometimes you have to deal with a situation where, in terms of resources, it seems that not too much was read and written, but it seems that the execution time is incongruously large for some reason.
It has to be said here:Oh, at that moment, the disk on the server was probably too busy, that's why it took so long to read!"But somehow it's not very accurate ...
But it can be determined absolutely reliably. The fact is that among the configuration options of the PG server there are :
Enables timed I/O operations. This setting is disabled by default, as it requires the operating system to constantly query the current time, which can slow things down significantly on some platforms. You can use the pg_test_timing utility to estimate the overhead of timing on your platform. I/O statistics can be obtained through the pg_stat_database view, in the EXPLAIN output (when the BUFFERS parameter is used) and through the pg_stat_statements view.
This option can also be enabled within a local session:
SET track_io_timing = TRUE;Well, now the best part is that we have learned to understand and display this data, taking into account all the transformations of the execution tree:
Here you can see that out of 0.790ms of the total execution time, 0.718ms took reading one page of data, 0.044ms - writing it, and only 0.028ms was spent on all other useful activity!
Future with PostgreSQL 13
For a complete overview of what's new, see , and we are specifically talking about changes in plans.
Planning buffers
Accounting for resources allocated to the scheduler is reflected in another patch that is not related to pg_stat_statements. EXPLAIN with the BUFFERS option will report the number of buffers used during the planning phase:
Seq Scan on pg_class (actual rows=386 loops=1) Buffers: shared hit=9 read=4 Planning Time: 0.782 ms Buffers: shared hit=103 read=11 Execution Time: 0.219 ms
Incremental sort
In cases where sorting by many keys (k1, k2, k3β¦) is needed, the planner can now take advantage of knowing that the data is already sorted by several of the first keys (eg k1 and k2). In this case, you can not re-sort all the data anew, but divide them into successive groups with the same values ββof k1 and k2, and βre-sortβ them by the key k3.
Thus, the entire sorting breaks up into several successive sortings of a smaller size. This reduces the amount of memory required, and also allows you to return the first data before all sorting is complete.
Incremental Sort (actual rows=2949857 loops=1) Sort Key: ticket_no, passenger_id Presorted Key: ticket_no Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB -> Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1) Planning Time: 2.137 ms Execution Time: 2230.019 ms
UI/UX Improvements
Screenshots are everywhere!
Now on each tab there is an opportunity to quickly take screenshot of tab to clipboard for the entire width and depth of the tab - "sight" right-top:
Actually, most of the pictures for this publication were obtained in this way.
Recommendations on nodes
There are not only more of them, but about each one you can by following the link:
Removing from the archive
Some have asked for the ability to delete "absolutely" even plans that are not published in the archive - please, just click the corresponding icon:
Well, let's not forget that we have where you can write your comments and suggestions.
Source: habr.com