Guarantee that your plans will never switch.
Overview
You’re employed very annoying to tune your queries; to to find the actual are looking ahead to execution opinion
that satisfies your performance wants.
Nonetheless a while later, after either the tips has changed suffiently, or the
statistics indulge in changed sufficiently, Postgres planner chooses to with out notice purchase
a are looking ahead to execution opinion that makes your software stare unresponsive. This shows
up to your dashboard as an outage. Now you peep your self working against the
clock, to rewrite the are looking ahead to, take a look at it, switch the software/ORM, and deploy the
adjustments, and quiet just hoping that the manufacturing database accepts your
choices, and executes your are looking ahead to just acceptably ample, so that you want to most definitely be ready to return to
bed.
Nonetheless at the same time as you happen to exercise the pg_plan_guarantee extension, you want to most definitely never face any such
dilemma; we are in a position to thunder that. This extension gives a mechanism, the exercise of
which you want to most definitely be ready to construct your execution opinion in stone; that’s, Postgres will attain
the opinion you give it, or this could most definitely just throw an error, but this could most definitely just never strive to bet
a opinion for you.
As a result of the tips and/or statistics switch incessantly over time, you want to most definitely well most definitely just even be fairly
assured that the guaraneed-opinion will continue to abet your wants in a
stunning formulation. That is, your guaranted-opinion will degrade gracefully, as
opposed to with out notice, at basically the most inopportune time.
Display camouflage: the term pgpg in the textual vow material below, and in the code, stands for/is an
acronym of the extension’s name, PG_Plan_Guarantee.
Installation
Command the extension in the extensions directory below the Postgres
installation directory. Then add the name of the extension, pg_plan_guarantee,
to the share_preload_libraries variable in the postgresql.conf file.
Disabling the Extension
When you happen to ever take to disable the extension, you want to most definitely be ready to attain so by adding the parameter
pg_plan_guarantee.enabled, and environment its payment to erroneous. This map you
develop no longer truly indulge in to protect end the extension from the filesystem, or from the
shared_preload_libraries parameter.
The option to Employ
The pg_plan_guarantee extension is designed to be in style in two steps. In the
first step, you are looking ahead to the extension to generate the assured-opinion. In the second
step, you exercise are looking ahead to the extension to exercise the assured-opinion. Please peep the
Demo allotment, below, to stare these steps in action.
To generate the assured-opinion, embed your are looking ahead to in the first string of the
following SQL tell:
purchase $pgpg$ your are looking ahead to $pgpg$
as are looking ahead to,
'' as opinion;
The output of the above tell will plan the assured-opinion in the second
column of the -build.
To make exercise of the assured-opinion produded by the first step, you embed the
assured-opinion in the second string of the same SQL tell, like so:
purchase $pgpg$ your are looking ahead to $pgpg$
as are looking ahead to,
$pgpg$ assured-opinion $pgpg$
as opinion;
Demo
Generate the assured-opinion.
=# purchase $pgpg$purchase relkind from pg_class where relname='pg_class'$pgpg$
as are looking ahead to,
''
as opinion;
-[ RECORD 1 ]-----
are looking ahead to | purchase relkind from pg_class where relname='pg_class'
opinion | {PLANNEDSTMT :commandType 1 :queryId 0 :hasReturning erroneous :hasModifyingCTE erroneous :canSetTag factual :transientPlan erroneous :dependsOnRole erroneous :parallelModeNeeded erroneous :jitFlags 0 :planTree {INDEXSCAN :startup_cost 0.27 :total_cost 8.29 :plan_rows 1 :plan_width 1 :parallel_aware erroneous :parallel_safe factual :async_capable erroneous :plan_node_id 0 :targetlist ({TARGETENTRY :expr {VAR :varno 1 :varattno 17 :vartype 18 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 17 :space 7} :resno 1 :resname relkind :ressortgroupref 0 :resorigtbl 1259 :resorigcol 17 :resjunk erroneous}) :qual :lefttree :righttree :initPlan :extParam (b) :allParam (b) :scanrelid 1 :indexid 2663 :indexqual ({OPEXPR :opno 93 :opfuncid 62 :opresulttype 16 :opretset erroneous :opcollid 0 :inputcollid 950 :args ({VAR :varno -3 :varattno 1 :vartype 19 :vartypmod -1 :varcollid 950 :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :space 35} {CONST :consttype 19 :consttypmod -1 :constcollid 950 :constlen 64 :constbyval erroneous :constisnull erroneous :space 45 :constvalue 64 [ 112 103 95 99 108 97 115 115 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ]}) :space 43}) :indexqualorig ({OPEXPR :opno 93 :opfuncid 62 :opresulttype 16 :opretset erroneous :opcollid 0 :inputcollid 950 :args ({VAR :varno 1 :varattno 2 :vartype 19 :vartypmod -1 :varcollid 950 :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :space 35} {CONST :consttype 19 :consttypmod -1 :constcollid 950 :constlen 64 :constbyval erroneous :constisnull erroneous :space 45 :constvalue 64 [ 112 103 95 99 108 97 115 115 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ]}) :space 43}) :indexorderby :indexorderbyorig :indexorderbyops :indexorderdir 1} :rtable ({RANGETBLENTRY :alias :eref {ALIAS :aliasname pg_class :colnames ("oid" "relname" "relnamespace" "reltype" "reloftype" "relowner" "relam" "relfilenode" "reltablespace" "relpages" "reltuples" "relallvisible" "reltoastrelid" "relhasindex" "relisshared" "relpersistence" "relkind" "relnatts" "relchecks" "relhasrules" "relhastriggers" "relhassubclass" "relrowsecurity" "relforcerowsecurity" "relispopulated" "relreplident" "relispartition" "relrewrite" "relfrozenxid" "relminmxid" "relacl" "reloptions" "relpartbound")} :rtekind 0 :relid 1259 :relkind r :rellockmode 1 :tablesample :lateral erroneous :inh erroneous :inFromCl factual :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9 24) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals }) :resultRelations :appendRelations :subplans :rewindPlanIDs (b) :rowMarks :relationOids (o 1259) :invalItems :paramExecTypes :utilityStmt :stmt_location 0 :stmt_len 0}
Employ the assured opinion.
as opinion;
relkind
———
r
(1 row)”>
=# purchase $pgpg$purchase relkind from pg_class where relname='pg_class'$pgpg$
as are looking ahead to,
$pgpg${PLANNEDSTMT :commandType 1 :queryId 0 :hasReturning erroneous :hasModifyingCTE erroneous :canSetTag factual :transientPlan erroneous :dependsOnRole erroneous :parallelModeNeeded erroneous :jitFlags 0 :planTree {INDEXSCAN :startup_cost 0.27 :total_cost 8.29 :plan_rows 1 :plan_width 1 :parallel_aware erroneous :parallel_safe factual :async_capable erroneous :plan_node_id 0 :targetlist ({TARGETENTRY :expr {VAR :varno 1 :varattno 17 :vartype 18 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 17 :space 7} :resno 1 :resname relkind :ressortgroupref 0 :resorigtbl 1259 :resorigcol 17 :resjunk erroneous}) :qual :lefttree :righttree :initPlan :extParam (b) :allParam (b) :scanrelid 1 :indexid 2663 :indexqual ({OPEXPR :opno 93 :opfuncid 62 :opresulttype 16 :opretset erroneous :opcollid 0 :inputcollid 950 :args ({VAR :varno -3 :varattno 1 :vartype 19 :vartypmod -1 :varcollid 950 :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :space 35} {CONST :consttype 19 :consttypmod -1 :constcollid 950 :constlen 64 :constbyval erroneous :constisnull erroneous :space 45 :constvalue 64 [ 112 103 95 99 108 97 115 115 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ]}) :space 43}) :indexqualorig ({OPEXPR :opno 93 :opfuncid 62 :opresulttype 16 :opretset erroneous :opcollid 0 :inputcollid 950 :args ({VAR :varno 1 :varattno 2 :vartype 19 :vartypmod -1 :varcollid 950 :varlevelsup 0 :varnosyn 1 :varattnosyn 2 :space 35} {CONST :consttype 19 :consttypmod -1 :constcollid 950 :constlen 64 :constbyval erroneous :constisnull erroneous :space 45 :constvalue 64 [ 112 103 95 99 108 97 115 115 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ]}) :space 43}) :indexorderby :indexorderbyorig :indexorderbyops :indexorderdir 1} :rtable ({RANGETBLENTRY :alias :eref {ALIAS :aliasname pg_class :colnames ("oid" "relname" "relnamespace" "reltype" "reloftype" "relowner" "relam" "relfilenode" "reltablespace" "relpages" "reltuples" "relallvisible" "reltoastrelid" "relhasindex" "relisshared" "relpersistence" "relkind" "relnatts" "relchecks" "relhasrules" "relhastriggers" "relhassubclass" "relrowsecurity" "relforcerowsecurity" "relispopulated" "relreplident" "relispartition" "relrewrite" "relfrozenxid" "relminmxid" "relacl" "reloptions" "relpartbound")} :rtekind 0 :relid 1259 :relkind r :rellockmode 1 :tablesample :lateral erroneous :inh erroneous :inFromCl factual :requiredPerms 2 :checkAsUser 0 :selectedCols (b 9 24) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals }) :resultRelations :appendRelations :subplans :rewindPlanIDs (b) :rowMarks :relationOids (o 1259) :invalItems :paramExecTypes :utilityStmt :stmt_location 0 :stmt_len 0}$pgpg$
as opinion;
relkind
---------
r
(1 row)
To stare what’s occurring in the background, add the auto_explain extension to shared_preload_libraries, and build client_min_messages to log.
=# load 'auto_explain';
LOAD
=# build auto_explain.log_min_duration=0;
SET
=# build client_min_messages=log;
SET
By default, our are looking ahead to uses Index Scan.
=# purchase relkind from pg_class where relname='pg_class';
LOG:
Ask Text:
Index Scan the exercise of pg_class_relname_nsp_index on pg_class (payment=0.27..8.29 rows=1 width=1)
Index Cond: (relname='pg_class'::name)
relkind
---------
r
(1 row)
Let’s build tuning to fabricate it exercise a opinion we desire it to, suppose, sequential scan.
=# build enable_indexscan=off;
SET
=# build enable_bitmapscan=off;
SET
=# purchase relkind from pg_class where relname='pg_class';
LOG:
Ask Text:
Seq Scan on pg_class (payment=0.00..19.05 rows=1 width=1)
Filter: (relname='pg_class'::name)
relkind
---------
r
(1 row)
Now that now we indulge in our desired opinion being generated by the Ask Planner, let’s generate a assured-opinion.
=# purchase $pgpg$purchase relkind from pg_class where relname='pg_class'$pgpg$
as are looking ahead to,
''
as opinion;
LOG:
Ask Text:
Result (payment=0.00..0.01 rows=1 width=64)
-[ RECORD 1 ]-----------
are looking ahead to | purchase relkind from pg_class where relname='pg_class'
opinion | {PLANNEDSTMT :commandType 1 :queryId 0 :hasReturning erroneous :hasModifyingCTE erroneous :canSetTag factual :transientPlan erroneous :dependsOnRole erroneous :parallelModeNeeded erroneous :jitFlags 0 :planTree {SEQSCAN :startup_cost 0.00 :total_cost 19.05 :plan_rows 1 : >>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Read More