Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5bf748b

Browse files
Enhance nbtree ScalarArrayOp execution.
Commit9e8da0f taught nbtree to handle ScalarArrayOpExpr qualsnatively. This works by pushing down the full context (the array keys)to the nbtree index AM, enabling it to execute multiple primitive indexscans that the planner treats as one continuous index scan/index path.This earlier enhancement enabled nbtree ScalarArrayOp index-only scans.It also allowed scans with ScalarArrayOp quals to return ordered results(with some notable restrictions, described further down).Take this general approach a lot further: teach nbtree SAOP index scansto decide how to execute ScalarArrayOp scans (when and where to startthe next primitive index scan) based on physical index characteristics.This can be far more efficient. All SAOP scans will now reliably avoidduplicative leaf page accesses (just like any other nbtree index scan).SAOP scans whose array keys are naturally clustered together now requirefar fewer index descents, since we'll reliably avoid starting a newprimitive scan just to get to a later offset from the same leaf page.The scan's arrays now advance using binary searches for the arrayelement that best matches the next tuple's attribute value. Requiredscan key arrays (i.e. arrays from scan keys that can terminate the scan)ratchet forward in lockstep with the index scan. Non-required arrays(i.e. arrays from scan keys that can only exclude non-matching tuples)"advance" without the process ever rolling over to a higher-order array.Naturally, only required SAOP scan keys trigger skipping over leaf pages(non-required arrays cannot safely end or start primitive index scans).Consequently, even index scans of a composite index with a high-orderinequality scan key (which we'll mark required) and a low-order SAOPscan key (which we won't mark required) now avoid repeating leaf pageaccesses -- that benefit isn't limited to simpler equality-only cases.In general, all nbtree index scans now output tuples as if they were onecontinuous index scan -- even scans that mix a high-order inequalitywith lower-order SAOP equalities reliably output tuples in index order.This allows us to remove a couple of special cases that were appliedwhen building index paths with SAOP clauses during planning.Bugfix commit807a40c taught the planner to avoid generating unsafepath keys: path keys on a multicolumn index path, with a SAOP clause onany attribute beyond the first/most significant attribute. These casesare now all safe, so we go back to generating path keys without regardfor the presence of SAOP clauses (just like with any other clause type).Affected queries can now exploit scan output order in all the usual ways(e.g., certain "ORDER BY ... LIMIT n" queries can now terminate early).Also undo changes from follow-up bugfix commita4523c5, which taughtthe planner to produce alternative index paths, with path keys, butwithout low-order SAOP index quals (filter quals were used instead).We'll no longer generate these alternative paths, since they can nolonger offer any meaningful advantages over standard index qual paths.Affected queries thereby avoid all of the disadvantages that come fromusing filter quals within index scan nodes. They can avoid extra heappage accesses from using filter quals to exclude non-matching tuples(index quals will never have that problem). They can also skip overirrelevant sections of the index in more cases (though only when nbtreedetermines that starting another primitive scan actually makes sense).There is a theoretical risk that removing restrictions on SAOP indexpaths from the planner will break compatibility with amcanorder-basedindex AMs maintained as extensions. Such an index AM could have thesame limitations around ordered SAOP scans as nbtree had up until now.Adding a pro forma incompatibility item about the issue to the Postgres17 release notes seems like a good idea.Author: Peter Geoghegan <pg@bowt.ie>Author: Matthias van de Meent <boekewurm+postgres@gmail.com>Reviewed-By: Heikki Linnakangas <hlinnaka@iki.fi>Reviewed-By: Matthias van de Meent <boekewurm+postgres@gmail.com>Reviewed-By: Tomas Vondra <tomas.vondra@enterprisedb.com>Discussion:https://postgr.es/m/CAH2-Wz=ksvN_sjcnD1+Bt-WtifRA5ok48aDYnq3pkKhxgMQpcw@mail.gmail.com
1 parentddd9e43 commit5bf748b

File tree

22 files changed

+3487
-579
lines changed

22 files changed

+3487
-579
lines changed

‎doc/src/sgml/indexam.sgml

Lines changed: 9 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -809,14 +809,22 @@ amrestrpos (IndexScanDesc scan);
809809
<para>
810810
<programlisting>
811811
Size
812-
amestimateparallelscan (void);
812+
amestimateparallelscan (int nkeys,
813+
int norderbys);
813814
</programlisting>
814815
Estimate and return the number of bytes of dynamic shared memory which
815816
the access method will be needed to perform a parallel scan. (This number
816817
is in addition to, not in lieu of, the amount of space needed for
817818
AM-independent data in <structname>ParallelIndexScanDescData</structname>.)
818819
</para>
819820

821+
<para>
822+
The <literal>nkeys</literal> and <literal>norderbys</literal>
823+
parameters indicate the number of quals and ordering operators that will be
824+
used in the scan; the same values will be passed to <function>amrescan</function>.
825+
Note that the actual values of the scan keys aren't provided yet.
826+
</para>
827+
820828
<para>
821829
It is not necessary to implement this function for access methods which
822830
do not support parallel scans or for which the number of additional bytes

‎doc/src/sgml/monitoring.sgml

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4064,6 +4064,19 @@ description | Waiting for a newly initialized WAL file to reach durable storage
40644064
</para>
40654065
</note>
40664066

4067+
<note>
4068+
<para>
4069+
Queries that use certain <acronym>SQL</acronym> constructs to search for
4070+
rows matching any value out of a list or array of multiple scalar values
4071+
(see <xref linkend="functions-comparisons"/>) perform multiple
4072+
<quote>primitive</quote> index scans (up to one primitive scan per scalar
4073+
value) during query execution. Each internal primitive index scan
4074+
increments <structname>pg_stat_all_indexes</structname>.<structfield>idx_scan</structfield>,
4075+
so it's possible for the count of index scans to significantly exceed the
4076+
total number of index scan executor node executions.
4077+
</para>
4078+
</note>
4079+
40674080
</sect2>
40684081

40694082
<sect2 id="monitoring-pg-statio-all-tables-view">

‎src/backend/access/index/indexam.c

Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -449,13 +449,10 @@ index_restrpos(IndexScanDesc scan)
449449

450450
/*
451451
* index_parallelscan_estimate - estimate shared memory for parallel scan
452-
*
453-
* Currently, we don't pass any information to the AM-specific estimator,
454-
* so it can probably only return a constant. In the future, we might need
455-
* to pass more information.
456452
*/
457453
Size
458-
index_parallelscan_estimate(RelationindexRelation,Snapshotsnapshot)
454+
index_parallelscan_estimate(RelationindexRelation,intnkeys,intnorderbys,
455+
Snapshotsnapshot)
459456
{
460457
Sizenbytes;
461458

@@ -474,7 +471,8 @@ index_parallelscan_estimate(Relation indexRelation, Snapshot snapshot)
474471
*/
475472
if (indexRelation->rd_indam->amestimateparallelscan!=NULL)
476473
nbytes=add_size(nbytes,
477-
indexRelation->rd_indam->amestimateparallelscan());
474+
indexRelation->rd_indam->amestimateparallelscan(nkeys,
475+
norderbys));
478476

479477
returnnbytes;
480478
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp