Explaining the inexplicable. Part 4
Conference PG Day’16 every day is getting closer, and we continue to publish a series of articles Hubert Lubaczewski on the analysis and explain its basic operations.
In this, I hope, penultimate post series I will tell you about the rest of the most common operations, which you can see in the output of explain.
The name of the operation speaks for itself – it removes duplicate data.
This can happen for example when you do the following:
the
In more recent versions of Postgres this query will be implemented using HashAggregate.
The Unique problem is that the data must be sorted. Not because this operation requires data in a certain order, and to ensure that all rows with the same values was "together".
It makes a really cool Unique operation (in those cases where it can be used), as it requires almost no memory. It just compares the value in the previous row with the current and if they are the same, discards it. That's all.
Thus, we can stimulate its use, to pre-sort the data:
the
The plan simply runs a lot of subopercle and returns them all returned rows to the result.
This is used by queries in UNION/UNION ALL
the
Here you can see how append has launched three scans on three tables and returned all the rows together.
Please note that I used UNION ALL. If I were to use UNION, we would have the following:
the
This is because UNION removes dublirebuli line, which in this case was produced by the HashAggregate operation.
The Result appears mainly in a very simple test queries. This operation is used when your query selects any constant value (or values):
the
In addition to test queries it can be found in queries that do something like "insert, but only if it is not data duplication":
the
Also, as a Result, Values Scan is used to return simple typed in the query data, but in this case it could be a set of records based on the function VALUES().
If you do not know, you can choose a number of rows and lots of columns without tables, just using the syntax of VALUES, as in this example:
the
The plan of this query looks like the following:
the
This operation is typically used in INSERT'Ah, but it has other uses, for example, custom sorting.
This operation is similar to HashAggregate, which we mentioned earlier.
The difference is that GroupAggregate data must be sorted using the column or columns that you used in the GROUP BY clause.
As Unique GroupAggregate uses very little memory, but requires orderliness of the data.
Example:
the
This operation is used by the operations INTERSECT/EXCEPT (with optional modifier "ALL").
It works as follows: runs of suboperation Append for a couple of subqueries, and then, based on the result and an optional modifier ALL, decides which rows to return. I have not climbed deep into the source code, so I can't say exactly how it works, but based on their names, the operation is similar to a simple decision on the basis of the counter.
We see that, unlike UNION, these operations work with two data sources:
the
But three sources we get a more complex tree:
the
This operation is similar to already mentioned operation Materialize. She runs part of the query and saves the output, so it can be used by another part (or parts) of the query.
Example:
the
Please note that pg_class is scanned only one time – line #6. But the results are stored in “x" and then scanned twice within the unit (line #9) and operation HashAggregate (10).
What is the difference from Materialize? To give a detailed answer to this question, we need to dive into the source code, but I would say that the distinction is based on the simple fact that CTE are determined by the user, while the Materialize is an auxiliary operation that Postgres decides to use when you see fit.
Important: CTE always run exactly as specified. So you can use them to get around is not the most successful optimization which may be implemented by the scheduler.
This plan happens every time there is a part of the query that can (or should) be designed before anything else and do not depend on anything in the remainder of your request.
Suppose you want this query:
the
In this case, you must run limit/sort/seq-scan to the usual sequential scan on pg_class, because Postgres will need to compare the value relkind value returned by the subquery.
On the other hand, I could write:
the
Postgres correctly sees that the column subselect does not depend on any data from the table pg_class, so it can be run once and not need to count the length for each line.
Of course, you can have a lot of individual plans (plans init), like this:
the
But it is necessary to consider one detail – init plan's within a single request are numbered "globally" and not on transactions.
SubPlan's something similar to NestedLoop. In the sense that they, too, can be called many times.
SubPlan is called to calculate data from subserosa who really depend on the current line.
For example:
the
For each row returned by the scan in the "pg_class as c", Postgres must run the SubPlan, which checks how many rows in pg_class have the same (like the just-processed row) value in column relkind.
Note the "loops=295" in a "Seq Scan on pg_class x" and the value "rows=295" node "Seq Scan on pg_class c".
Yes, there are other operations. Some of them are too rare to be worthy of our attention (especially when you consider that you have a great source of knowledge – source) and some are (as I suspect) an old version of the new nodes.
If you have a plan with the surgery that I told, and you don't get it, write me, please, in the comments a link to the output of explain explain.depesz.com, the name of the operation, and the version of Postgres in which she was found. I will try to find all possible information on such cases and give you a detailed response.
Article based on information from habrahabr.ru
In this, I hope, penultimate post series I will tell you about the rest of the most common operations, which you can see in the output of explain.
Unique
The name of the operation speaks for itself – it removes duplicate data.
This can happen for example when you do the following:
the
select distinct field from table
In more recent versions of Postgres this query will be implemented using HashAggregate.
The Unique problem is that the data must be sorted. Not because this operation requires data in a certain order, and to ensure that all rows with the same values was "together".
It makes a really cool Unique operation (in those cases where it can be used), as it requires almost no memory. It just compares the value in the previous row with the current and if they are the same, discards it. That's all.
Thus, we can stimulate its use, to pre-sort the data:
the
$ explain select distinct relkind from (select relkind from pg_class order by relkind) as x;
QUERY PLAN
-----------------------------------------------------------------------
Unique (cost=27.26..22.88 rows=4 width=1)
-> Sort (cost=22.88..23.61 rows=292 width=1)
Sort Key: pg_class.relkind
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=1)
(4 rows)
Append
The plan simply runs a lot of subopercle and returns them all returned rows to the result.
This is used by queries in UNION/UNION ALL
the
$ explain select oid from pg_class union all select oid from pg_proc union all select oid from pg_database;
QUERY PLAN
-----------------------------------------------------------------
Append (cost=0.00..104.43 rows=2943 width=4)
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=4)
-> Seq Scan on pg_proc (cost=0.00..92.49 rows=2649 width=4)
-> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=4)
(4 rows)
Here you can see how append has launched three scans on three tables and returned all the rows together.
Please note that I used UNION ALL. If I were to use UNION, we would have the following:
the
$ explain select oid from pg_class union select oid from pg_proc union select oid from pg_database;
QUERY PLAN
-----------------------------------------------------------------------
HashAggregate (cost=141.22 170.65..rows=2943 width=4)
-> Append (cost=0.00..133.86 rows=2943 width=4)
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=4)
-> Seq Scan on pg_proc (cost=0.00..92.49 rows=2649 width=4)
-> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=4)
(5 rows)
This is because UNION removes dublirebuli line, which in this case was produced by the HashAggregate operation.
Result
The Result appears mainly in a very simple test queries. This operation is used when your query selects any constant value (or values):
the
$ explain select 1, 2;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
(1 row)
In addition to test queries it can be found in queries that do something like "insert, but only if it is not data duplication":
the
$ explain insert into t (i) select 1 where not exists (select * from t where i = 1);
QUERY PLAN
---------------------------------------------------------------------
Insert on t (cost=3.33..3.35 rows=1 width=4)
-> Result (cost=3.33..3.34 rows=1 width=0)
One-Time Filter: (NOT $0)
InitPlan 1 (returns $0)
-> Seq Scan on t t_1 (cost=0.00..40.00 rows=12 width=0)
Filter: (i = 1)
(6 rows)
Values Scan
Also, as a Result, Values Scan is used to return simple typed in the query data, but in this case it could be a set of records based on the function VALUES().
If you do not know, you can choose a number of rows and lots of columns without tables, just using the syntax of VALUES, as in this example:
the
$ select * from ( values (1, 'hubert'), (2, 'depesz'), (3, 'lubaczewski') ) as t (a,b);
a | b
---+-------------
1 | hubert
2 | depesz
3 | lubaczewski
(3 rows)
The plan of this query looks like the following:
the
the QUERY PLAN
--------------------------------------------------------------
Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=36)
(1 row)
This operation is typically used in INSERT'Ah, but it has other uses, for example, custom sorting.
GroupAggregate
This operation is similar to HashAggregate, which we mentioned earlier.
The difference is that GroupAggregate data must be sorted using the column or columns that you used in the GROUP BY clause.
As Unique GroupAggregate uses very little memory, but requires orderliness of the data.
Example:
the
$ explain select relkind, count(*) from (select relkind from pg_class order by relkind) x group by relkind;
QUERY PLAN
-----------------------------------------------------------------------
GroupAggregate (cost=22.88..28.03 rows=4 width=1)
-> Sort (cost=22.88..23.61 rows=292 width=1)
Sort Key: pg_class.relkind
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=1)
(4 rows)
HashSetOp
This operation is used by the operations INTERSECT/EXCEPT (with optional modifier "ALL").
It works as follows: runs of suboperation Append for a couple of subqueries, and then, based on the result and an optional modifier ALL, decides which rows to return. I have not climbed deep into the source code, so I can't say exactly how it works, but based on their names, the operation is similar to a simple decision on the basis of the counter.
We see that, unlike UNION, these operations work with two data sources:
the
$ explain select * from (select oid from pg_Class order by oid) x intersect all select * from (select oid from pg_proc order by oid) y;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
HashSetOp Intersect All (cost=0.15..170.72 rows=292 width=4)
-> Append (cost=0.15..163.36 rows=2941 width=4)
-> Subquery Scan on "*SELECT* 1" (cost=0.15..18.37 rows=292 width=4)
-> Index Only Scan using pg_class_oid_index on pg_class (cost=0.15..12.53 rows=292 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.28..145.00 rows=2649 width=4)
-> Index Only Scan using pg_proc_oid_index on pg_proc (cost=0.28..92.02 rows=2649 width=4)
(6 rows)
But three sources we get a more complex tree:
the
$ explain select * from (select oid from pg_Class order by oid) x intersect all select * from (select oid from pg_proc order by oid) y intersect all select * from (Select oid from pg_database order by oid) as w;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
HashSetOp Intersect All (cost=1.03..172.53 rows=2 width=4)
-> Append (cost=1.03..171.79 rows=294 width=4)
-> Subquery Scan on "*SELECT* 3" (cost=1.03..1.07 rows=2 width=4)
-> Sort (cost=1.03..1.03 rows=2 width=4)
Sort Key: pg_database.oid
-> Seq Scan on pg_database (cost=0.00..1.02 rows=2 width=4)
-> Result (cost=0.15..170.72 rows=292 width=4)
-> HashSetOp Intersect All (cost=0.15..170.72 rows=292 width=4)
-> Append (cost=0.15..163.36 rows=2941 width=4)
-> Subquery Scan on "*SELECT* 1" (cost=0.15..18.37 rows=292 width=4)
-> Index Only Scan using pg_class_oid_index on pg_class (cost=0.15..12.53 rows=292 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.28..145.00 rows=2649 width=4)
-> Index Only Scan using pg_proc_oid_index on pg_proc (cost=0.28..92.02 rows=2649 width=4)
(13 rows)
CTE Scan
This operation is similar to already mentioned operation Materialize. She runs part of the query and saves the output, so it can be used by another part (or parts) of the query.
Example:
the
$ explain analyze with x as (select relname, relkind from pg_class) select relkind, count(*), (select count(*) from x) from x group by relkind;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
HashAggregate (cost=24.80..26.80 rows=200 width=1) (actual time=0.466..0.468 rows=6 loops=1)
CTE x
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=65) (actual time=0.009..0.127 rows=295 loops=1)
InitPlan 2 (returns $1)
-> Aggregate (cost=6.57..6.58 rows=1 width=0) (actual time=0.085..0.085 rows=1 loops=1)
-> CTE Scan on x x_1 (cost=0.00..5.84 rows=292 width=0) (actual time=0.000..0.055 rows=295 loops=1)
Total runtime: 0.524 ms
(8 rows)
Please note that pg_class is scanned only one time – line #6. But the results are stored in “x" and then scanned twice within the unit (line #9) and operation HashAggregate (10).
What is the difference from Materialize? To give a detailed answer to this question, we need to dive into the source code, but I would say that the distinction is based on the simple fact that CTE are determined by the user, while the Materialize is an auxiliary operation that Postgres decides to use when you see fit.
Important: CTE always run exactly as specified. So you can use them to get around is not the most successful optimization which may be implemented by the scheduler.
InitPlan
This plan happens every time there is a part of the query that can (or should) be designed before anything else and do not depend on anything in the remainder of your request.
Suppose you want this query:
the
$ explain select * from pg_class where relkind = (select relkind from pg_class order by random() limit 1);
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=24.76..13.11 rows=73 width=203)
Filter: (relkind = $0)
InitPlan 1 (returns $0)
-> Limit (cost=13.11..13.11 rows=1 width=1)
-> Sort (cost=13.84..13.11 rows=292 width=1)
Sort Key: (random())
-> Seq Scan on pg_class pg_class_1 (cost=0.00..11.65 rows=292 width=1)
(7 rows)
In this case, you must run limit/sort/seq-scan to the usual sequential scan on pg_class, because Postgres will need to compare the value relkind value returned by the subquery.
On the other hand, I could write:
the
$ explain select *, (select length('depesz')) from pg_class;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on pg_class (cost=0.01..10.93 rows=292 width=203)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=0)
(3 rows)
Postgres correctly sees that the column subselect does not depend on any data from the table pg_class, so it can be run once and not need to count the length for each line.
Of course, you can have a lot of individual plans (plans init), like this:
the
$ explain select *, (select length('depesz')) from pg_class where relkind = (select relkind from pg_class order by random() limit 1);
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=13.12..24.77 rows=73 width=203)
Filter: (relkind = $1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=0)
InitPlan 2 (returns $1)
-> Limit (cost=13.11..13.11 rows=1 width=1)
-> Sort (cost=13.84..13.11 rows=292 width=1)
Sort Key: (random())
-> Seq Scan on pg_class pg_class_1 (cost=0.00..11.65 rows=292 width=1)
(9 rows)
But it is necessary to consider one detail – init plan's within a single request are numbered "globally" and not on transactions.
SubPlan
SubPlan's something similar to NestedLoop. In the sense that they, too, can be called many times.
SubPlan is called to calculate data from subserosa who really depend on the current line.
For example:
the
$ explain analyze select c.relname, c.relkind, (Select count(*) from pg_Class x where c.relkind = x.relkind) from pg_Class c;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on pg_class c (cost=0.00..3468.93 rows=292 width=65) (actual time=0.135..26.717 rows=295 loops=1)
SubPlan 1
-> Aggregate (cost=11.83..11.84 rows=1 width=0) (actual time=0.090..0.090 rows=1 loops=295)
-> Seq Scan on pg_class x (cost=0.00..11.65 rows=73 width=0) (actual time=0.010..0.081 rows=93 loops=295)
Filter: (c.relkind = relkind)
Rows Removed by Filter: 202
Total runtime: 26.783 ms
(7 rows)
For each row returned by the scan in the "pg_class as c", Postgres must run the SubPlan, which checks how many rows in pg_class have the same (like the just-processed row) value in column relkind.
Note the "loops=295" in a "Seq Scan on pg_class x" and the value "rows=295" node "Seq Scan on pg_class c".
Other?
Yes, there are other operations. Some of them are too rare to be worthy of our attention (especially when you consider that you have a great source of knowledge – source) and some are (as I suspect) an old version of the new nodes.
If you have a plan with the surgery that I told, and you don't get it, write me, please, in the comments a link to the output of explain explain.depesz.com, the name of the operation, and the version of Postgres in which she was found. I will try to find all possible information on such cases and give you a detailed response.
Комментарии
Отправить комментарий