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

Commitbaf17ad

Browse files
committed
Repair performance regression in information_schema.triggers view.
Commit32ff269 introduced use of rank() into the triggers view tocalculate the spec-mandated action_order column. As written, thisprevents query constraints on the table-name column from being pushedbelow the window aggregate step. That's bad for performance of thistypical usage pattern, since the view now has to be evaluated for alltables not just the one(s) the user wants to see. It's also the causeof some recent buildfarm failures, in which trying to evaluate the viewrows for triggers in process of being dropped resulted in "cache lookupfailed for function NNN" errors. Those rows aren't of interest to thetest script doing the query, but the filter that would eliminate themis being applied too late. None of this happened before the rank()call was there, so it's a regression compared to v10 and before.We can improve matters by changing the rank() call so that instead ofpartitioning by OIDs, it partitions by nspname and relname, castingthose to sql_identifier so that they match the respective view outputcolumns exactly. The planner has enough intelligence to know thatconstraints on partitioning columns are safe to push down, so thiseliminates the performance problem and the regression test failurerisk. We could make the other partitioning columns match view outputsas well, but it'd be more complicated and the performance benefitsare questionable.Side note: as this stands, the planner will push down constraints onevent_object_table and trigger_schema, but not on event_object_schema,because it checks for ressortgroupref matches not expressionequivalence. That might be worth improving someday, but it's notnecessary to fix the immediate concern.Back-patch to v11 where the rank() call was added. Ordinarily we'd notchange information_schema in released branches, but the test failure hasbeen seen in v12 and presumably could happen in v11 as well, so we needto do this to keep the buildfarm happy. The change is harmless so faras users are concerned. Some might wish to apply it to existinginstallations if performance of this type of query is of concern,but those who don't are no worse off.I bumped catversion in HEAD as a pro forma matter (there's nocatalog incompatibility that would really require a re-initdb).Obviously that can't be done in the back branches.Discussion:https://postgr.es/m/5891.1587594470@sss.pgh.pa.us
1 parent4cac3a4 commitbaf17ad

File tree

2 files changed

+10
-3
lines changed

2 files changed

+10
-3
lines changed

‎src/backend/catalog/information_schema.sql

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2046,8 +2046,15 @@ CREATE VIEW triggers AS
20462046
CAST(
20472047
-- To determine action order, partition by schema, table,
20482048
-- event_manipulation (INSERT/DELETE/UPDATE), ROW/STATEMENT (1),
2049-
-- BEFORE/AFTER (66), then order by trigger name
2050-
rank() OVER (PARTITION BYn.oid,c.oid,em.num,t.tgtype &1,t.tgtype &66ORDER BYt.tgname)
2049+
-- BEFORE/AFTER (66), then order by trigger name. It's preferable
2050+
-- to partition by view output columns, so that query constraints
2051+
-- can be pushed down below the window function.
2052+
rank() OVER (PARTITION BY CAST(n.nspnameAS sql_identifier),
2053+
CAST(c.relnameAS sql_identifier),
2054+
em.num,
2055+
t.tgtype &1,
2056+
t.tgtype &66
2057+
ORDER BYt.tgname)
20512058
AS cardinal_number)AS action_order,
20522059
CAST(
20532060
CASE WHEN pg_has_role(c.relowner,'USAGE')

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO202004074
56+
#defineCATALOG_VERSION_NO202004241
5757

5858
#endif

0 commit comments

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