Query Processing in Oracle and PostgreSQL: Implications of a Single Solution
Processing SQL queries in both Orakle and Postgres has a lot in common. Anyway, you need to perform a syntactic analysis, check the semantics (which requires meta information, and it does not matter whether it is called a “data dictionary” or a “system catalog”), perform any transformations, build an optimal execution plan (in both systems based on cost, and therefore requiring pre-collected statistics).
But there is only one significant difference that radically changes the whole approach to processing. Speech, of course, that Orakl uses a global cache of the disassembled requests, and Postgres saves requests locally.
In the article we will try to trace how, due to the difference in one architectural solution, a completely different ideology of work in queries in two DBMS follows logically.
')
The examples given (which were run on versions of Oracle 11.2 XE and PostgreSQL 9.4) contain query execution times. We are only interested in relative values: how many times the execution time has changed after making certain changes in the query. In this case, the absolute figures may differ by orders of magnitude depending on the equipment, load and settings. In order not to give rise to meaningless conclusions based on them, all absolute values ​​in the article are scaled so that one of the requests is 10 seconds in both systems.
Orakl uses a cache of disassembled queries global for the entire instance (library cache, library cache). The plan of any executed query is guaranteed to be in the cache: either the query is executed with the already prepared plan from the cache, or a new plan is built and stored in the cache - and this happens automatically.
Simplifiedly, the general query execution scheme can be represented as follows:
The same request, repeated two times in a row, will be processed differently. The first time there will be a so-called full parse (hard parse) - from the first to the last item. The second time, only partial parsing (soft parse) will be performed - syntactic and semantic analysis - after which a ready plan will be found and used in the cache, which is much more efficient.
The presence of a global cache pushes to minimize the number of entries in it. One reason is that a large stream of “one-time” requests can force out useful plans from the cache, while these requests themselves will never be repeated. But most importantly, parallel processes access the shared cache, therefore, it must be protected by locks and writing to it can become a bottleneck.
Indeed, a process that performs many complete parses becomes a problem for the entire instance. Consider this situation with the following example:
Here we create a table, insert hundreds of thousands of rows into it (the “dual connect by rowid <= N” construction is an idiom to generate a sample of N rows) and collect statistics.
Let us execute the PL / SQL code below, which updates the table line by line in a loop, using dynamically generated update queries (perhaps the example looks far-fetched, but in practice it is not like this):
If you run a trace, here's what you can find:
Information on all SQL queries initiated from a code block is shown here. The elapsed column shows the total elapsed time (which is made up of cpu and different expectations), and the lines parse, execute, fetch correspond to the stages of parsing, executing and receiving the results of the query. As you can see, most of the time (95 seconds out of 110, column elapsed) went into the analysis of one hundred thousand (column count) of the same type of requests and placing their one-time plans in the cache. If you run several similar processes at the same time, expectations such as “latch: shared pool” and “latch: row cache objects” will start to appear (the names change from version to version), indicating competition for access to the library cache.
To avoid this, in Oracle it is common to use bind variables. For example:
Or simply, without dynamic SQL, since PL / SQL automatically converts its variables to database bind variables:
Here is what tracing will show in this case:
The parsing time has been reduced to the minimum - all update requests now look the same for the DBMS. "Same", that is, in fact, the key for the cache, is determined by two values:
Thus, the update request is parsed only once (the number 3 in the count column corresponds to the PL / SQL block parses, the select query in the for clause, and the update query in the body of the loop). His plan is placed in the cache and then everything works relatively quickly.
(Why “relative”? Because the correct way is to perform the update with one command “update t set n = n + 1”, which is performed an order of magnitude faster.)
However, a “general” query plan, constructed without taking into account the values ​​of variables, will be adequate only for evenly distributed data.
Let's change the table: add and index the flag field that is equal to “Y” for 0.1% of the rows and “N” for the remaining 99.9%.
In order for the optimizer to take note of the irregularity of the data in the flag field, it is necessary to assemble a histogram on this field. For example:
Interestingly, the explain plan command (the result of which is available using the dbms_xplan.display function) will still show a plan built on the assumption of uniformity, as if the optimizer is expecting to get half of the table:
This means only that by and large the explain plan command in Orakle cannot be used. It does not take into account either the values ​​of the variables or their types, and the plan generated by it does not fall into the cache and is not used in any way.
In fact, when executing the query, Orakl “spies” the values ​​of the binding variables (this is called “bind peeking”) and builds a plan based on these values. The real plan needs to be looked at directly in the cache when the request has already been sent for execution and parsed. To do this, use the dbms_xplan.display_cursor function; with the parameters specified in the example, it displays the plan of the last executed query and information about the bind variables:
Now it is clear that the optimizer took into account the value of the variable (section peeked binds), adequately estimated the number of lines (135; the error does not affect the result) and chose access by index.
The problem is that the built “private” plan gets into the cache and will be reused for the same requests - without taking into account the values ​​of variables. This is not always good: in our example, access by index will be extremely inefficient for the value of 'N'. Traditionally, the solution was to use dynamic SQL with literals pasted into the query text - but this solution is not a good one: in addition to the minuses discussed above, this approach is also dangerous with the possibility of SQL injections. Therefore (starting with version 11g), Orakl is able to find and specially process requests that are sensitive to the values ​​of binding variables (this is called “adaptive cursor sharing”). When executing the query, the plan already in the cache is used, but the resources actually consumed are tracked and compared with the statistics of previous runs.
Let's look at some of the information from the library cache on our request:
The request is marked as sensitive to variable values ​​(bind sensitive). Buffer_gets - the number of read data blocks.
If it is found that the request was executed worse with other values, then the next time it is executed it will be marked as bind aware.
Perform the same query with a different flag field value:
Let us make sure that the request was executed with a plan from the cache, and at the same time we will demonstrate the possibility of outputting in the plan not only expected, but also actual values ​​(for this purpose, the statistics_level parameter was first set):
There is a discrepancy between the expected number of lines (135) and real (99900). In addition, it is clear that for execution I had to read significantly more data than the first time (buffer_gets column):
Run the query again:
Now the new plan is already used, built for the new value of the binding variable (note the changed child number and the peeked binds section):
This time, the optimizer correctly estimated the number of rows (99856, with a small error) and chose a full table scan. And the library cache now has two versions of the plan for the same query:
Striving to minimize the number of plans in the cache forces the optimizer to "stumble" before deciding whether to have different plans for a single query. Note that this can be avoided by manually giving a hint to the optimizer in advance.
Postgres has no global cache of parsed queries. Moreover, if you do not make special efforts, the request will not be stored locally in the memory of the process.
In particular, if you repeat the same request, it will be fully understood every time. Of course, a process written in this way will not work optimally, but at least it does not directly affect other processes.
Consider an example:
Run the following PL / pgSQL code:
In order to save the results of the analysis, the request must be prepared, and then the saved request can be reused:
This is exactly what happens if in a PL / pgSQL block you invoke a SQL command without using execute, as in the first example. In our case, this gives a speed gain of 3.5 times:
(And the correct version — one SQL command — runs three more times faster.)
The general scheme for parsing a request consists of the following steps:
When preparing a request, it is analyzed and rewritten. Optimization is performed again each time it is executed — in this way, for each value of the binding variables, a “private” plan is constructed.
Consider an example of non-uniform data distribution (instead of a character variable, we can use the logical type):
The necessary histogram will be automatically constructed when analyzing the table:
Prepare the request:
To find out which execution plan will be selected for the true value of the flag, you must use the explain command. In Postgres, she is aware of the meaning and type of the binding variables and shows exactly the plan with which the command will be executed:
The optimizer intends to select 110 lines (also with a small error) and uses index access.
The explain command is also convenient because it allows you not only to build a plan, but also to execute a command and immediately get both the expected and actual cardinality values. Let's demonstrate this for another flag value:
In this case, the optimizer expects to get 99890 rows (actually 99900) and adequately selects the full reading of the table.
Here a problem arises that is the opposite of that which Oracle encounters: what if the plan does not depend on the values ​​of the binding variables? In this case, it would be beneficial not to optimize the query every time.
Indeed, Postgres can move from “private” plans to a “generic plan”, but does not do it right away. The first five times the request is optimized in any case, and then preference is given to the general plan if its cost (as estimated by the optimizer) does not exceed the average cost of private plans. The number five here is a certain compromise: a small value does not provide sufficient cost statistics for different values ​​of the binding variables, and a large value negates the optimization itself.
Consider this mechanism for example with a uniform distribution of data:
This is a private plan, as can be seen from the “Index Cond: (id = 1)” condition — a specific number is indicated here.
However, if you call explain or simply execute the query four more times with any variable values, it will switch to the general plan:
Here in the condition “Index Cond: (id = $ 1)” instead of a specific value, the number of the binding variable is indicated - this is a sign of a general plan. Its cost in this case coincides with the cost of private plans.
Now, a ready plan will be used for the request, which increases the efficiency of implementation (although it may lead to a problem in case of an error in calculating the cost or if the first five times turn out to be “not indicative”).
The decision to use the global cache of parsed queries in Orakle leads to the desire not to write to it more than is absolutely necessary - both because of the limited size and the danger of crowding out useful plans, and because of the competition of parallel processes for accessing the cache. Therefore, Orakl starts with one general plan for the request and only if necessary passes to several private ones.
On the contrary, the decision not to use the global cache in Postgrese makes it easier to treat unnecessary parsing. Postgres, on the contrary, begins with private plans and then, if possible, moves on to the general.
Orakl automatically caches query plans. The developer in this regard is only required to remember to use bind variables, which is primarily dictated by the global cache constraints. Because of the seriousness of the problem, Orakl even provides the parameter cursor_sharing, forcibly replacing all constants with variables.
Postgres completely gives the decision about the need to save the parsed query in the hands of the developer - or a development tool. The use of binding variables does not play such a dramatic role in productivity in Postgres (although the security issues from SQL injections are equally relevant for both systems).
If several processes use the same request, in Orakle it will be parsed only one - the first - time. The remaining processes will take advantage of a ready-made plan in the global cache.
In Postgres, each process will have to parse the request itself. But one-time requests are executed without the overhead of placing the plan in the cache.
Each of the solutions has its advantages and disadvantages; in any case, these features should be considered by developers and administrators who design, implement and maintain application systems.
But there is only one significant difference that radically changes the whole approach to processing. Speech, of course, that Orakl uses a global cache of the disassembled requests, and Postgres saves requests locally.
In the article we will try to trace how, due to the difference in one architectural solution, a completely different ideology of work in queries in two DBMS follows logically.
')
The examples given (which were run on versions of Oracle 11.2 XE and PostgreSQL 9.4) contain query execution times. We are only interested in relative values: how many times the execution time has changed after making certain changes in the query. In this case, the absolute figures may differ by orders of magnitude depending on the equipment, load and settings. In order not to give rise to meaningless conclusions based on them, all absolute values ​​in the article are scaled so that one of the requests is 10 seconds in both systems.
Orakl
Orakl uses a cache of disassembled queries global for the entire instance (library cache, library cache). The plan of any executed query is guaranteed to be in the cache: either the query is executed with the already prepared plan from the cache, or a new plan is built and stored in the cache - and this happens automatically.
Simplifiedly, the general query execution scheme can be represented as follows:
- Parsing a query (whether the SQL command was spelled correctly).
- Semantic analysis (whether the specified objects exist and whether there is access to them).
- If the finished plan is in the cache, then use it; otherwise, further.
- Transformation (rewriting a query according to heuristic rules).
- Optimization (choice of execution plan with minimal cost).
- The location of the selected plan in the cache.
The same request, repeated two times in a row, will be processed differently. The first time there will be a so-called full parse (hard parse) - from the first to the last item. The second time, only partial parsing (soft parse) will be performed - syntactic and semantic analysis - after which a ready plan will be found and used in the cache, which is much more efficient.
The presence of a global cache pushes to minimize the number of entries in it. One reason is that a large stream of “one-time” requests can force out useful plans from the cache, while these requests themselves will never be repeated. But most importantly, parallel processes access the shared cache, therefore, it must be protected by locks and writing to it can become a bottleneck.
Indeed, a process that performs many complete parses becomes a problem for the entire instance. Consider this situation with the following example:
create table t(id number primary key,n number not null);insert into t(id, n)select level, 1from dualconnect by rownum <= 100000;exec dbms_stats.gather_table_stats(user,'T');alter session set statistics_level=all;Here we create a table, insert hundreds of thousands of rows into it (the “dual connect by rowid <= N” construction is an idiom to generate a sample of N rows) and collect statistics.
Let us execute the PL / SQL code below, which updates the table line by line in a loop, using dynamically generated update queries (perhaps the example looks far-fetched, but in practice it is not like this):
beginfor i in (select id from t) loopexecute immediate 'update t set n = n + 1 where id = '||i.id;end loop;commit;end;/If you run a trace, here's what you can find:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 100003 92.63 95.40 0 2837 0 0Execute 100003 13.57 14.29 0 200002 102225 100000Fetch 1002 0.87 0.75 0 10173 0 100000------- ------ -------- ---------- ---------- ---------- ---------- ----------total 201008 107.08 110.46 0 213012 102225 200000Misses in library cache during parse: 100001Information on all SQL queries initiated from a code block is shown here. The elapsed column shows the total elapsed time (which is made up of cpu and different expectations), and the lines parse, execute, fetch correspond to the stages of parsing, executing and receiving the results of the query. As you can see, most of the time (95 seconds out of 110, column elapsed) went into the analysis of one hundred thousand (column count) of the same type of requests and placing their one-time plans in the cache. If you run several similar processes at the same time, expectations such as “latch: shared pool” and “latch: row cache objects” will start to appear (the names change from version to version), indicating competition for access to the library cache.
To avoid this, in Oracle it is common to use bind variables. For example:
beginfor i in (select id from t) loopexecute immediate 'update t set n = n + 1 where id = :A' using i.id;end loop;commit;end;/Or simply, without dynamic SQL, since PL / SQL automatically converts its variables to database bind variables:
beginfor i in (select id from t) loopupdate t set n = n + 1 where id = i.id;end loop;commit;end;/Here is what tracing will show in this case:
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 3 0.02 0.03 0 297 0 0Execute 100002 9.08 9.28 0 201694 102315 100000Fetch 1001 0.77 0.68 0 10173 0 100000------- ------ -------- ---------- ---------- ---------- ---------- ----------total 101006 9.87 10.00 0 212164 102315 200000The parsing time has been reduced to the minimum - all update requests now look the same for the DBMS. "Same", that is, in fact, the key for the cache, is determined by two values:
- sql_id - hash code of the query text (that is, queries that differ in any character are different queries),
- child_number is some additional number, the necessity of which is caused at least by the fact that syntactically identical queries (with the same sql_id) may be semantically different and must have different plans.
Thus, the update request is parsed only once (the number 3 in the count column corresponds to the PL / SQL block parses, the select query in the for clause, and the update query in the body of the loop). His plan is placed in the cache and then everything works relatively quickly.
(Why “relative”? Because the correct way is to perform the update with one command “update t set n = n + 1”, which is performed an order of magnitude faster.)
However, a “general” query plan, constructed without taking into account the values ​​of variables, will be adequate only for evenly distributed data.
Let's change the table: add and index the flag field that is equal to “Y” for 0.1% of the rows and “N” for the remaining 99.9%.
alter table t add (flag char(1) check (flag in ('Y','N')));update tset flag = case when mod(id,1000)=0 then 'Y' else 'N' end;create index t_flag on t(flag);In order for the optimizer to take note of the irregularity of the data in the flag field, it is necessary to assemble a histogram on this field. For example:
exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns flag size 2');Interestingly, the explain plan command (the result of which is available using the dbms_xplan.display function) will still show a plan built on the assumption of uniformity, as if the optimizer is expecting to get half of the table:
explain plan for select * from t where flag = :f;select * from table(dbms_xplan.display);--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 50000 | 488K| 76 (2)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T | 50000 | 488K| 76 (2)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("FLAG"=:F)This means only that by and large the explain plan command in Orakle cannot be used. It does not take into account either the values ​​of the variables or their types, and the plan generated by it does not fall into the cache and is not used in any way.
In fact, when executing the query, Orakl “spies” the values ​​of the binding variables (this is called “bind peeking”) and builds a plan based on these values. The real plan needs to be looked at directly in the cache when the request has already been sent for execution and parsed. To do this, use the dbms_xplan.display_cursor function; with the parameters specified in the example, it displays the plan of the last executed query and information about the bind variables:
var f char(1)exec :f := 'Y'select * from t where flag = :f;...100 rows selected.select * from table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));SQL_ID 6pncxxhknwgqc, child number 0--------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)| || 1 | TABLE ACCESS BY INDEX ROWID| T | 135 | 1350 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | T_FLAG | 135 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------Peeked Binds (identified by position):--------------------------------------1 - :F (CHAR(30), CSID=873): 'Y'Predicate Information (identified by operation id):---------------------------------------------------2 - filter("FLAG"=:F)Now it is clear that the optimizer took into account the value of the variable (section peeked binds), adequately estimated the number of lines (135; the error does not affect the result) and chose access by index.
The problem is that the built “private” plan gets into the cache and will be reused for the same requests - without taking into account the values ​​of variables. This is not always good: in our example, access by index will be extremely inefficient for the value of 'N'. Traditionally, the solution was to use dynamic SQL with literals pasted into the query text - but this solution is not a good one: in addition to the minuses discussed above, this approach is also dangerous with the possibility of SQL injections. Therefore (starting with version 11g), Orakl is able to find and specially process requests that are sensitive to the values ​​of binding variables (this is called “adaptive cursor sharing”). When executing the query, the plan already in the cache is used, but the resources actually consumed are tracked and compared with the statistics of previous runs.
Let's look at some of the information from the library cache on our request:
select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS------------ ----------------- ------------- ---------- -----------0 Y N 1 128The request is marked as sensitive to variable values ​​(bind sensitive). Buffer_gets - the number of read data blocks.
If it is found that the request was executed worse with other values, then the next time it is executed it will be marked as bind aware.
Perform the same query with a different flag field value:
exec :f := 'N'select * from t where flag = :f;...99900 rows selected.Let us make sure that the request was executed with a plan from the cache, and at the same time we will demonstrate the possibility of outputting in the plan not only expected, but also actual values ​​(for this purpose, the statistics_level parameter was first set):
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));SQL_ID 6pncxxhknwgqc, child number 0-----------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |-----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 99900 | 41368 || 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 135 | 99900 | 41368 ||* 2 | INDEX RANGE SCAN | T_FLAG | 1 | 135 | 99900 | 6842 |-----------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("FLAG"=:F)There is a discrepancy between the expected number of lines (135) and real (99900). In addition, it is clear that for execution I had to read significantly more data than the first time (buffer_gets column):
select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS------------ ----------------- ------------- ---------- -----------0 Y N 2 41496Run the query again:
select * from t where flag = :f;...99900 rows selected.Now the new plan is already used, built for the new value of the binding variable (note the changed child number and the peeked binds section):
select * from table(dbms_xplan.display_cursor(format=>'typical +peeked_binds'));SQL_ID 6pncxxhknwgqc, child number 1--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 77 (100)| ||* 1 | TABLE ACCESS FULL| T | 99856 | 975K| 77 (3)| 00:00:01 |--------------------------------------------------------------------------Peeked Binds (identified by position):--------------------------------------1 - :F (CHAR(30), CSID=873): 'N'Predicate Information (identified by operation id):---------------------------------------------------1 - filter("FLAG"=:F)This time, the optimizer correctly estimated the number of rows (99856, with a small error) and chose a full table scan. And the library cache now has two versions of the plan for the same query:
select child_number, is_bind_sensitive, is_bind_aware, executions, buffer_gets from v$sql where sql_id='6pncxxhknwgqc';CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE EXECUTIONS BUFFER_GETS------------ ----------------- ------------- ---------- -----------0 Y N 2 414961 Y Y 1 6922Striving to minimize the number of plans in the cache forces the optimizer to "stumble" before deciding whether to have different plans for a single query. Note that this can be avoided by manually giving a hint to the optimizer in advance.
Postgres
Postgres has no global cache of parsed queries. Moreover, if you do not make special efforts, the request will not be stored locally in the memory of the process.
In particular, if you repeat the same request, it will be fully understood every time. Of course, a process written in this way will not work optimally, but at least it does not directly affect other processes.
Consider an example:
create table t(id serial primary key,n numeric not null);insert into t(n)select 1 from generate_series(1,100000);analyze t;Run the following PL / pgSQL code:
\timing ondo $$declarei record;beginfor i in (select id from t) loopexecute 'update t set n = n + 1 where id = '||i.id;end loop;end;$$ language plpgsql;DOTime: 36164,377 msIn order to save the results of the analysis, the request must be prepared, and then the saved request can be reused:
prepare u(integer) as update t set n = n + 1 where id = $1;execute u(1);execute u(2);...execute u(100000);This is exactly what happens if in a PL / pgSQL block you invoke a SQL command without using execute, as in the first example. In our case, this gives a speed gain of 3.5 times:
do $$declarei record;beginfor i in (select id from t) loopupdate t set n = n + 1 where id = i.id;end loop;end;$$ language plpgsql;DOTime: 10000,000 ms(And the correct version — one SQL command — runs three more times faster.)
The general scheme for parsing a request consists of the following steps:
- Parsing;
- Semantic analysis;
- Rewriting the request (according to the rules, and both system and user);
- Optimization.
When preparing a request, it is analyzed and rewritten. Optimization is performed again each time it is executed — in this way, for each value of the binding variables, a “private” plan is constructed.
Consider an example of non-uniform data distribution (instead of a character variable, we can use the logical type):
alter table t add columnflag boolean;update tset flag = mod(id,1000)=0;create index on t(flag);The necessary histogram will be automatically constructed when analyzing the table:
analyze t;Prepare the request:
prepare s1(boolean) as select * from t where flag = $1;To find out which execution plan will be selected for the true value of the flag, you must use the explain command. In Postgres, she is aware of the meaning and type of the binding variables and shows exactly the plan with which the command will be executed:
explain execute s1(true);QUERY PLAN------------------------------------------------------------------------Index Scan using t_flag_idx on t (cost=0.29..14.31 rows=110 width=10)Index Cond: (flag = true)Filter: flagThe optimizer intends to select 110 lines (also with a small error) and uses index access.
The explain command is also convenient because it allows you not only to build a plan, but also to execute a command and immediately get both the expected and actual cardinality values. Let's demonstrate this for another flag value:
explain analyze execute s1(false);QUERY PLAN------------------------------------------------------------------------------------------------------Seq Scan on t (cost=0.00..2958.00 rows=99890 width=10) (actual time=0.043..265.272 rows=99900 loops=1)Filter: (NOT flag)Rows Removed by Filter: 100Execution time: 385.455 msIn this case, the optimizer expects to get 99890 rows (actually 99900) and adequately selects the full reading of the table.
Here a problem arises that is the opposite of that which Oracle encounters: what if the plan does not depend on the values ​​of the binding variables? In this case, it would be beneficial not to optimize the query every time.
Indeed, Postgres can move from “private” plans to a “generic plan”, but does not do it right away. The first five times the request is optimized in any case, and then preference is given to the general plan if its cost (as estimated by the optimizer) does not exceed the average cost of private plans. The number five here is a certain compromise: a small value does not provide sufficient cost statistics for different values ​​of the binding variables, and a large value negates the optimization itself.
Consider this mechanism for example with a uniform distribution of data:
prepare s2(integer) as select * from t where id = $1;explain execute s2(1);QUERY PLAN-----------------------------------------------------------------Index Scan using t_pkey on t (cost=0.42..8.44 rows=1 width=10)Index Cond: (id = 1)This is a private plan, as can be seen from the “Index Cond: (id = 1)” condition — a specific number is indicated here.
However, if you call explain or simply execute the query four more times with any variable values, it will switch to the general plan:
execute s2(2);...execute s2(3);...execute s2(4);...execute s2(5);...explain execute s2(6);QUERY PLAN-----------------------------------------------------------------Index Scan using t_pkey on t (cost=0.42..8.44 rows=1 width=10)Index Cond: (id = $1)Here in the condition “Index Cond: (id = $ 1)” instead of a specific value, the number of the binding variable is indicated - this is a sign of a general plan. Its cost in this case coincides with the cost of private plans.
Now, a ready plan will be used for the request, which increases the efficiency of implementation (although it may lead to a problem in case of an error in calculating the cost or if the first five times turn out to be “not indicative”).
Conclusion
The decision to use the global cache of parsed queries in Orakle leads to the desire not to write to it more than is absolutely necessary - both because of the limited size and the danger of crowding out useful plans, and because of the competition of parallel processes for accessing the cache. Therefore, Orakl starts with one general plan for the request and only if necessary passes to several private ones.
On the contrary, the decision not to use the global cache in Postgrese makes it easier to treat unnecessary parsing. Postgres, on the contrary, begins with private plans and then, if possible, moves on to the general.
Orakl automatically caches query plans. The developer in this regard is only required to remember to use bind variables, which is primarily dictated by the global cache constraints. Because of the seriousness of the problem, Orakl even provides the parameter cursor_sharing, forcibly replacing all constants with variables.
Postgres completely gives the decision about the need to save the parsed query in the hands of the developer - or a development tool. The use of binding variables does not play such a dramatic role in productivity in Postgres (although the security issues from SQL injections are equally relevant for both systems).
If several processes use the same request, in Orakle it will be parsed only one - the first - time. The remaining processes will take advantage of a ready-made plan in the global cache.
In Postgres, each process will have to parse the request itself. But one-time requests are executed without the overhead of placing the plan in the cache.
Each of the solutions has its advantages and disadvantages; in any case, these features should be considered by developers and administrators who design, implement and maintain application systems.
Source: https://habr.com/ru/post/275755/
All Articles