public inbox for [email protected]
help / color / mirror / Atom feedSignificant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
22+ messages / 6 participants
[nested] [flat]
* Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
@ 2024-08-26 21:49 nikhil raj <[email protected]>
2024-08-26 22:10 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Adrian Klaver <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
0 siblings, 2 replies; 22+ messages in thread
From: nikhil raj @ 2024-08-26 21:49 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>; +Cc: NIKITA PATEL <[email protected]>; Patel Khushbu <[email protected]>
Hi All,
I've encountered a noticeable difference in execution time and query
execution plan row counts between PostgreSQL 13 and PostgreSQL 16 when
running a query on information_schema tables. Surprisingly, PostgreSQL 16
is performing slower than PostgreSQL 13.
The query executed on both versions is as follows:
SELECT DISTINCT "tc"."constraint_name" AS "ConstraintName",
"ccu"."column_name" AS "ColumnName"
FROM
information_schema.constraint_column_usage AS "ccu" right join
information_schema.table_constraints AS "tc"
ON "tc"."constraint_catalog" = "ccu"."constraint_catalog"
AND "tc"."constraint_name" = "ccu"."constraint_name"
WHERE "tc"."constraint_type" = 'PRIMARY KEY'
AND "ccu"."table_name" = 't_c56ng1_repository'
Here are the details of the PostgreSQL versions and the execution plans:
*4PostgreSQL 13.14 (PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by
gcc 11.4.0, 64-bit)*
Execution plan: PG13.14 Execution Plan
<https://explain.dalibo.com/plan/ag1a62a9d47dg29d;
*PostgreSQL 16.4 (PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by gcc
11.4.0, 64-bit)*
Execution plan: PG16.4 Execution Plan
<https://explain.dalibo.com/plan/4c66fdfbf2hf9ed2;
Has anyone else experienced similar behavior or could provide insights into
why PostgreSQL 16 might be slower for this query? Any advice or suggestions
for optimization would be greatly appreciated.
Thank you!
NOTE:- PFA the raw file of explain and analyze below.
[
{
"Plan": {
"Node Type": "Unique",
"Parallel Aware": false,
"Startup Cost": 119.03,
"Total Cost": 119.04,
"Plan Rows": 1,
"Plan Width": 128,
"Actual Startup Time": 367.918,
"Actual Total Time": 367.930,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["\"*SELECT* 1\".constraint_name", "((\"*SELECT* 1_1\".attname)::information_schema.sql_identifier)"],
"Shared Hit Blocks": 34639,
"Shared Read Blocks": 50,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 52.372,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 119.03,
"Total Cost": 119.04,
"Plan Rows": 1,
"Plan Width": 128,
"Actual Startup Time": 367.669,
"Actual Total Time": 367.680,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["\"*SELECT* 1\".constraint_name", "((\"*SELECT* 1_1\".attname)::information_schema.sql_identifier)"],
"Sort Key": ["\"*SELECT* 1\".constraint_name", "((\"*SELECT* 1_1\".attname)::information_schema.sql_identifier)"],
"Sort Method": "quicksort",
"Sort Space Used": 25,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 34639,
"Shared Read Blocks": 50,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 52.372,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 9.54,
"Total Cost": 119.02,
"Plan Rows": 1,
"Plan Width": 128,
"Actual Startup Time": 14.418,
"Actual Total Time": 367.601,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["\"*SELECT* 1\".constraint_name", "(\"*SELECT* 1_1\".attname)::information_schema.sql_identifier"],
"Inner Unique": false,
"Join Filter": "((\"*SELECT* 1\".constraint_name)::name = \"*SELECT* 1_1\".conname)",
"Rows Removed by Join Filter": 935,
"Shared Hit Blocks": 34636,
"Shared Read Blocks": 50,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 52.372,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Alias": "*SELECT* 1",
"Startup Cost": 0.42,
"Total Cost": 63.99,
"Plan Rows": 1,
"Plan Width": 64,
"Actual Startup Time": 1.337,
"Actual Total Time": 50.081,
"Actual Rows": 936,
"Actual Loops": 1,
"Output": ["\"*SELECT* 1\".constraint_name"],
"Shared Hit Blocks": 4688,
"Shared Read Blocks": 18,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 40.971,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Result",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 0.42,
"Total Cost": 63.98,
"Plan Rows": 1,
"Plan Width": 512,
"Actual Startup Time": 1.335,
"Actual Total Time": 49.922,
"Actual Rows": 936,
"Actual Loops": 1,
"Output": ["NULL::information_schema.sql_identifier", "NULL::information_schema.sql_identifier", "(c.conname)::information_schema.sql_identifier", "NULL::information_schema.sql_identifier", "NULL::information_schema.sql_identifier", "NULL::information_schema.sql_identifier", "NULL::information_schema.character_data", "NULL::information_schema.yes_or_no", "NULL::information_schema.yes_or_no", "NULL::information_schema.yes_or_no"],
"One-Time Filter": "(current_database() = current_database())",
"Shared Hit Blocks": 4688,
"Shared Read Blocks": 18,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 40.971,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.42,
"Total Cost": 63.98,
"Plan Rows": 1,
"Plan Width": 64,
"Actual Startup Time": 1.327,
"Actual Total Time": 49.635,
"Actual Rows": 936,
"Actual Loops": 1,
"Output": ["c.conname"],
"Inner Unique": true,
"Shared Hit Blocks": 4688,
"Shared Read Blocks": 18,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 40.971,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.28,
"Total Cost": 63.81,
"Plan Rows": 1,
"Plan Width": 68,
"Actual Startup Time": 1.290,
"Actual Total Time": 48.143,
"Actual Rows": 936,
"Actual Loops": 1,
"Output": ["c.conname", "r.relnamespace"],
"Inner Unique": true,
"Join Filter": "(c.connamespace = nc.oid)",
"Rows Removed by Join Filter": 2808,
"Shared Hit Blocks": 3749,
"Shared Read Blocks": 18,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 40.971,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.28,
"Total Cost": 62.72,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 1.278,
"Actual Total Time": 45.844,
"Actual Rows": 936,
"Actual Loops": 1,
"Output": ["c.conname", "c.connamespace", "r.relnamespace"],
"Inner Unique": true,
"Shared Hit Blocks": 2813,
"Shared Read Blocks": 18,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 40.971,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "pg_constraint",
"Schema": "pg_catalog",
"Alias": "c",
"Startup Cost": 0.00,
"Total Cost": 50.05,
"Plan Rows": 5,
"Plan Width": 72,
"Actual Startup Time": 1.238,
"Actual Total Time": 1.637,
"Actual Rows": 936,
"Actual Loops": 1,
"Output": ["c.oid", "c.conname", "c.connamespace", "c.contype", "c.condeferrable", "c.condeferred", "c.convalidated", "c.conrelid", "c.contypid", "c.conindid", "c.conparentid", "c.confrelid", "c.confupdtype", "c.confdeltype", "c.confmatchtype", "c.conislocal", "c.coninhcount", "c.connoinherit", "c.conkey", "c.confkey", "c.conpfeqop", "c.conppeqop", "c.conffeqop", "c.conexclop", "c.conbin"],
"Filter": "((c.contype <> ALL ('{t,x}'::\"char\"[])) AND ((CASE c.contype WHEN 'c'::\"char\" THEN 'CHECK'::text WHEN 'f'::\"char\" THEN 'FOREIGN KEY'::text WHEN 'p'::\"char\" THEN 'PRIMARY KEY'::text WHEN 'u'::\"char\" THEN 'UNIQUE'::text ELSE NULL::text END)::text = 'PRIMARY KEY'::text))",
"Rows Removed by Filter": 146,
"Shared Hit Blocks": 22,
"Shared Read Blocks": 1,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 1.207,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pg_class_oid_index",
"Relation Name": "pg_class",
"Schema": "pg_catalog",
"Alias": "r",
"Startup Cost": 0.28,
"Total Cost": 2.53,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.046,
"Actual Total Time": 0.046,
"Actual Rows": 1,
"Actual Loops": 936,
"Output": ["r.oid", "r.relname", "r.relnamespace", "r.reltype", "r.reloftype", "r.relowner", "r.relam", "r.relfilenode", "r.reltablespace", "r.relpages", "r.reltuples", "r.relallvisible", "r.reltoastrelid", "r.relhasindex", "r.relisshared", "r.relpersistence", "r.relkind", "r.relnatts", "r.relchecks", "r.relhasrules", "r.relhastriggers", "r.relhassubclass", "r.relrowsecurity", "r.relforcerowsecurity", "r.relispopulated", "r.relreplident", "r.relispartition", "r.relrewrite", "r.relfrozenxid", "r.relminmxid", "r.relacl", "r.reloptions", "r.relpartbound"],
"Index Cond": "(r.oid = c.conrelid)",
"Rows Removed by Index Recheck": 0,
"Filter": "((r.relkind = ANY ('{r,p}'::\"char\"[])) AND (pg_has_role(r.relowner, 'USAGE'::text) OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES'::text)))",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 2791,
"Shared Read Blocks": 17,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 39.763,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nc",
"Startup Cost": 0.00,
"Total Cost": 1.04,
"Plan Rows": 4,
"Plan Width": 4,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.001,
"Actual Rows": 4,
"Actual Loops": 936,
"Output": ["nc.oid", "nc.nspname", "nc.nspowner", "nc.nspacl"],
"Shared Hit Blocks": 936,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pg_namespace_oid_index",
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nr",
"Startup Cost": 0.13,
"Total Cost": 0.15,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 1,
"Actual Loops": 936,
"Output": ["nr.oid"],
"Index Cond": "(nr.oid = r.relnamespace)",
"Rows Removed by Index Recheck": 0,
"Filter": "(NOT pg_is_other_temp_schema(nr.oid))",
"Rows Removed by Filter": 0,
"Heap Fetches": 0,
"Shared Hit Blocks": 939,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
},
{
"Node Type": "Append",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 9.12,
"Total Cost": 55.01,
"Plan Rows": 2,
"Plan Width": 128,
"Actual Startup Time": 0.019,
"Actual Total Time": 0.338,
"Actual Rows": 1,
"Actual Loops": 936,
"Shared Hit Blocks": 29948,
"Shared Read Blocks": 32,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 11.402,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Subplans Removed": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Alias": "*SELECT* 1_1",
"Startup Cost": 9.12,
"Total Cost": 9.15,
"Plan Rows": 1,
"Plan Width": 128,
"Actual Startup Time": 0.008,
"Actual Total Time": 0.008,
"Actual Rows": 0,
"Actual Loops": 936,
"Output": ["\"*SELECT* 1_1\".attname", "\"*SELECT* 1_1\".conname"],
"Shared Hit Blocks": 13,
"Shared Read Blocks": 8,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 6.426,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Unique",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 9.12,
"Total Cost": 9.14,
"Plan Rows": 1,
"Plan Width": 324,
"Actual Startup Time": 0.007,
"Actual Total Time": 0.007,
"Actual Rows": 0,
"Actual Loops": 936,
"Output": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "a.attname", "nc_1.nspname", "c_1.conname"],
"Shared Hit Blocks": 13,
"Shared Read Blocks": 8,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 6.426,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 9.12,
"Total Cost": 9.13,
"Plan Rows": 1,
"Plan Width": 324,
"Actual Startup Time": 0.007,
"Actual Total Time": 0.007,
"Actual Rows": 0,
"Actual Loops": 936,
"Output": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "a.attname", "nc_1.nspname", "c_1.conname"],
"Sort Key": ["nr_1.nspname", "r_1.relowner", "a.attname", "nc_1.nspname", "c_1.conname"],
"Sort Method": "quicksort",
"Sort Space Used": 25,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 13,
"Shared Read Blocks": 8,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 6.426,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 1.27,
"Total Cost": 9.11,
"Plan Rows": 1,
"Plan Width": 324,
"Actual Startup Time": 6.707,
"Actual Total Time": 6.712,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "a.attname", "nc_1.nspname", "c_1.conname"],
"Inner Unique": true,
"Shared Hit Blocks": 10,
"Shared Read Blocks": 8,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 6.426,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 1.14,
"Total Cost": 8.38,
"Plan Rows": 1,
"Plan Width": 264,
"Actual Startup Time": 6.706,
"Actual Total Time": 6.710,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "a.attname", "c_1.conname", "c_1.connamespace"],
"Inner Unique": true,
"Shared Hit Blocks": 10,
"Shared Read Blocks": 8,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 6.426,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.86,
"Total Cost": 8.05,
"Plan Rows": 1,
"Plan Width": 200,
"Actual Startup Time": 4.723,
"Actual Total Time": 6.666,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "a.attname", "d.objid"],
"Inner Unique": true,
"Join Filter": "(r_1.oid = a.attrelid)",
"Rows Removed by Join Filter": 0,
"Shared Hit Blocks": 7,
"Shared Read Blocks": 8,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 6.426,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.57,
"Total Cost": 7.15,
"Plan Rows": 1,
"Plan Width": 148,
"Actual Startup Time": 2.837,
"Actual Total Time": 4.778,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "r_1.oid", "d.refobjid", "d.refobjsubid", "d.objid"],
"Inner Unique": false,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 6,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 4.637,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.28,
"Total Cost": 3.60,
"Plan Rows": 1,
"Plan Width": 136,
"Actual Startup Time": 0.044,
"Actual Total Time": 0.047,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "r_1.oid"],
"Inner Unique": true,
"Join Filter": "(nr_1.oid = r_1.relnamespace)",
"Rows Removed by Join Filter": 3,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 1,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.011,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pg_class_relname_nsp_index",
"Relation Name": "pg_class",
"Schema": "pg_catalog",
"Alias": "r_1",
"Startup Cost": 0.28,
"Total Cost": 2.51,
"Plan Rows": 1,
"Plan Width": 76,
"Actual Startup Time": 0.038,
"Actual Total Time": 0.041,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["r_1.oid", "r_1.relname", "r_1.relnamespace", "r_1.reltype", "r_1.reloftype", "r_1.relowner", "r_1.relam", "r_1.relfilenode", "r_1.reltablespace", "r_1.relpages", "r_1.reltuples", "r_1.relallvisible", "r_1.reltoastrelid", "r_1.relhasindex", "r_1.relisshared", "r_1.relpersistence", "r_1.relkind", "r_1.relnatts", "r_1.relchecks", "r_1.relhasrules", "r_1.relhastriggers", "r_1.relhassubclass", "r_1.relrowsecurity", "r_1.relforcerowsecurity", "r_1.relispopulated", "r_1.relreplident", "r_1.relispartition", "r_1.relrewrite", "r_1.relfrozenxid", "r_1.relminmxid", "r_1.relacl", "r_1.reloptions", "r_1.relpartbound"],
"Index Cond": "(r_1.relname = 't_c56ng1_repository'::name)",
"Rows Removed by Index Recheck": 0,
"Filter": "((r_1.relkind = ANY ('{r,p}'::\"char\"[])) AND pg_has_role(r_1.relowner, 'USAGE'::text))",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 2,
"Shared Read Blocks": 1,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.011,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nr_1",
"Startup Cost": 0.00,
"Total Cost": 1.04,
"Plan Rows": 4,
"Plan Width": 68,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 4,
"Actual Loops": 1,
"Output": ["nr_1.oid", "nr_1.nspname", "nr_1.nspowner", "nr_1.nspacl"],
"Shared Hit Blocks": 1,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pg_depend_reference_index",
"Relation Name": "pg_depend",
"Schema": "pg_catalog",
"Alias": "d",
"Startup Cost": 0.29,
"Total Cost": 3.54,
"Plan Rows": 1,
"Plan Width": 12,
"Actual Startup Time": 2.789,
"Actual Total Time": 4.723,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["d.classid", "d.objid", "d.objsubid", "d.refclassid", "d.refobjid", "d.refobjsubid", "d.deptype"],
"Index Cond": "((d.refclassid = '1259'::oid) AND (d.refobjid = r_1.oid))",
"Rows Removed by Index Recheck": 0,
"Filter": "(d.classid = '2606'::oid)",
"Rows Removed by Filter": 4,
"Shared Hit Blocks": 0,
"Shared Read Blocks": 5,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 4.626,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pg_attribute_relid_attnum_index",
"Relation Name": "pg_attribute",
"Schema": "pg_catalog",
"Alias": "a",
"Startup Cost": 0.29,
"Total Cost": 0.89,
"Plan Rows": 1,
"Plan Width": 70,
"Actual Startup Time": 1.877,
"Actual Total Time": 1.877,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["a.attrelid", "a.attname", "a.atttypid", "a.attstattarget", "a.attlen", "a.attnum", "a.attndims", "a.attcacheoff", "a.atttypmod", "a.attbyval", "a.attstorage", "a.attalign", "a.attnotnull", "a.atthasdef", "a.atthasmissing", "a.attidentity", "a.attgenerated", "a.attisdropped", "a.attislocal", "a.attinhcount", "a.attcollation", "a.attacl", "a.attoptions", "a.attfdwoptions", "a.attmissingval"],
"Index Cond": "((a.attrelid = d.refobjid) AND (a.attnum = d.refobjsubid))",
"Rows Removed by Index Recheck": 0,
"Filter": "(NOT a.attisdropped)",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 4,
"Shared Read Blocks": 2,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 1.789,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pg_constraint_oid_index",
"Relation Name": "pg_constraint",
"Schema": "pg_catalog",
"Alias": "c_1",
"Startup Cost": 0.28,
"Total Cost": 0.32,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 0.037,
"Actual Total Time": 0.037,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["c_1.oid", "c_1.conname", "c_1.connamespace", "c_1.contype", "c_1.condeferrable", "c_1.condeferred", "c_1.convalidated", "c_1.conrelid", "c_1.contypid", "c_1.conindid", "c_1.conparentid", "c_1.confrelid", "c_1.confupdtype", "c_1.confdeltype", "c_1.confmatchtype", "c_1.conislocal", "c_1.coninhcount", "c_1.connoinherit", "c_1.conkey", "c_1.confkey", "c_1.conpfeqop", "c_1.conppeqop", "c_1.conffeqop", "c_1.conexclop", "c_1.conbin"],
"Index Cond": "(c_1.oid = d.objid)",
"Rows Removed by Index Recheck": 0,
"Filter": "(c_1.contype = 'c'::\"char\")",
"Rows Removed by Filter": 1,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pg_namespace_oid_index",
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nc_1",
"Startup Cost": 0.13,
"Total Cost": 0.56,
"Plan Rows": 1,
"Plan Width": 68,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.000,
"Actual Rows": 0,
"Actual Loops": 0,
"Output": ["nc_1.oid", "nc_1.nspname", "nc_1.nspowner", "nc_1.nspacl"],
"Index Cond": "(nc_1.oid = c_1.connamespace)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
},
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Alias": "*SELECT* 2",
"Startup Cost": 2.94,
"Total Cost": 45.85,
"Plan Rows": 1,
"Plan Width": 128,
"Actual Startup Time": 0.011,
"Actual Total Time": 0.330,
"Actual Rows": 1,
"Actual Loops": 936,
"Output": ["\"*SELECT* 2\".attname", "\"*SELECT* 2\".conname"],
"Shared Hit Blocks": 29935,
"Shared Read Blocks": 24,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 4.976,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2.94,
"Total Cost": 45.84,
"Plan Rows": 1,
"Plan Width": 324,
"Actual Startup Time": 0.011,
"Actual Total Time": 0.329,
"Actual Rows": 1,
"Actual Loops": 936,
"Output": ["NULL::name", "NULL::name", "NULL::oid", "a_1.attname", "NULL::name", "c_2.conname"],
"Inner Unique": true,
"Shared Hit Blocks": 29935,
"Shared Read Blocks": 24,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 4.976,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2.81,
"Total Cost": 45.68,
"Plan Rows": 1,
"Plan Width": 132,
"Actual Startup Time": 0.010,
"Actual Total Time": 0.328,
"Actual Rows": 1,
"Actual Loops": 936,
"Output": ["a_1.attname", "c_2.conname", "c_2.connamespace"],
"Inner Unique": false,
"Join Filter": "(r_2.oid = a_1.attrelid)",
"Rows Removed by Join Filter": 0,
"Shared Hit Blocks": 28998,
"Shared Read Blocks": 24,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 4.976,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2.52,
"Total Cost": 44.44,
"Plan Rows": 1,
"Plan Width": 136,
"Actual Startup Time": 0.004,
"Actual Total Time": 0.317,
"Actual Rows": 1,
"Actual Loops": 936,
"Output": ["r_2.oid", "c_2.conname", "c_2.connamespace", "c_2.contype", "c_2.confrelid", "c_2.conrelid", "c_2.confkey", "c_2.conkey"],
"Inner Unique": true,
"Join Filter": "(r_2.relnamespace = nr_2.oid)",
"Rows Removed by Join Filter": 3,
"Shared Hit Blocks": 22448,
"Shared Read Blocks": 22,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 4.191,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 2.52,
"Total Cost": 43.35,
"Plan Rows": 1,
"Plan Width": 140,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.315,
"Actual Rows": 1,
"Actual Loops": 936,
"Output": ["r_2.relnamespace", "r_2.oid", "c_2.conname", "c_2.connamespace", "c_2.contype", "c_2.confrelid", "c_2.conrelid", "c_2.confkey", "c_2.conkey"],
"Inner Unique": true,
"Hash Cond": "(CASE c_2.contype WHEN 'f'::\"char\" THEN c_2.confrelid ELSE c_2.conrelid END = r_2.oid)",
"Shared Hit Blocks": 21512,
"Shared Read Blocks": 22,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 4.191,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "pg_constraint",
"Schema": "pg_catalog",
"Alias": "c_2",
"Startup Cost": 0.00,
"Total Cost": 37.88,
"Plan Rows": 1074,
"Plan Width": 132,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.154,
"Actual Rows": 1074,
"Actual Loops": 936,
"Output": ["c_2.oid", "c_2.conname", "c_2.connamespace", "c_2.contype", "c_2.condeferrable", "c_2.condeferred", "c_2.convalidated", "c_2.conrelid", "c_2.contypid", "c_2.conindid", "c_2.conparentid", "c_2.confrelid", "c_2.confupdtype", "c_2.confdeltype", "c_2.confmatchtype", "c_2.conislocal", "c_2.coninhcount", "c_2.connoinherit", "c_2.conkey", "c_2.confkey", "c_2.conpfeqop", "c_2.conppeqop", "c_2.conffeqop", "c_2.conexclop", "c_2.conbin"],
"Filter": "(c_2.contype = ANY ('{p,u,f}'::\"char\"[]))",
"Rows Removed by Filter": 8,
"Shared Hit Blocks": 21506,
"Shared Read Blocks": 22,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 4.191,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 2.51,
"Total Cost": 2.51,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.042,
"Actual Total Time": 0.043,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["r_2.relnamespace", "r_2.oid"],
"Hash Buckets": 1024,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 9,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pg_class_relname_nsp_index",
"Relation Name": "pg_class",
"Schema": "pg_catalog",
"Alias": "r_2",
"Startup Cost": 0.28,
"Total Cost": 2.51,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.028,
"Actual Total Time": 0.029,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["r_2.relnamespace", "r_2.oid"],
"Index Cond": "(r_2.relname = 't_c56ng1_repository'::name)",
"Rows Removed by Index Recheck": 0,
"Filter": "((r_2.relkind = ANY ('{r,p}'::\"char\"[])) AND pg_has_role(r_2.relowner, 'USAGE'::text))",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 3,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nr_2",
"Startup Cost": 0.00,
"Total Cost": 1.04,
"Plan Rows": 4,
"Plan Width": 4,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.001,
"Actual Rows": 4,
"Actual Loops": 936,
"Output": ["nr_2.oid", "nr_2.nspname", "nr_2.nspowner", "nr_2.nspacl"],
"Shared Hit Blocks": 936,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pg_attribute_relid_attnum_index",
"Relation Name": "pg_attribute",
"Schema": "pg_catalog",
"Alias": "a_1",
"Startup Cost": 0.29,
"Total Cost": 1.23,
"Plan Rows": 1,
"Plan Width": 70,
"Actual Startup Time": 0.005,
"Actual Total Time": 0.011,
"Actual Rows": 1,
"Actual Loops": 936,
"Output": ["a_1.attrelid", "a_1.attname", "a_1.atttypid", "a_1.attstattarget", "a_1.attlen", "a_1.attnum", "a_1.attndims", "a_1.attcacheoff", "a_1.atttypmod", "a_1.attbyval", "a_1.attstorage", "a_1.attalign", "a_1.attnotnull", "a_1.atthasdef", "a_1.atthasmissing", "a_1.attidentity", "a_1.attgenerated", "a_1.attisdropped", "a_1.attislocal", "a_1.attinhcount", "a_1.attcollation", "a_1.attacl", "a_1.attoptions", "a_1.attfdwoptions", "a_1.attmissingval"],
"Index Cond": "(a_1.attrelid = CASE c_2.contype WHEN 'f'::\"char\" THEN c_2.confrelid ELSE c_2.conrelid END)",
"Rows Removed by Index Recheck": 0,
"Filter": "((NOT a_1.attisdropped) AND (a_1.attnum = ANY (CASE c_2.contype WHEN 'f'::\"char\" THEN c_2.confkey ELSE c_2.conkey END)))",
"Rows Removed by Filter": 18,
"Shared Hit Blocks": 6550,
"Shared Read Blocks": 2,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.784,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Scan Direction": "Forward",
"Index Name": "pg_namespace_oid_index",
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nc_2",
"Startup Cost": 0.13,
"Total Cost": 0.15,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.000,
"Actual Rows": 1,
"Actual Loops": 936,
"Output": ["nc_2.oid"],
"Index Cond": "(nc_2.oid = c_2.connamespace)",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 0,
"Shared Hit Blocks": 937,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
}
]
}
]
},
"Settings": {
"effective_cache_size": "36GB",
"effective_io_concurrency": "400",
"jit": "off",
"max_parallel_workers": "4",
"random_page_cost": "1.1",
"work_mem": "16MB"
},
"Planning": {
"Shared Hit Blocks": 651,
"Shared Read Blocks": 59,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 70.080,
"I/O Write Time": 0.000
},
"Planning Time": 80.888,
"Triggers": [
],
"Execution Time": 369.328
}
]
[
{
"Plan": {
"Node Type": "Unique",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 538.23,
"Total Cost": 538.24,
"Plan Rows": 1,
"Plan Width": 128,
"Actual Startup Time": 14303.709,
"Actual Total Time": 14303.725,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["\"*SELECT* 1_1\".constraint_name", "((\"*SELECT* 1\".attname)::information_schema.sql_identifier)"],
"Shared Hit Blocks": 5153046,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 538.23,
"Total Cost": 538.23,
"Plan Rows": 1,
"Plan Width": 128,
"Actual Startup Time": 14303.707,
"Actual Total Time": 14303.723,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["\"*SELECT* 1_1\".constraint_name", "((\"*SELECT* 1\".attname)::information_schema.sql_identifier)"],
"Sort Key": ["\"*SELECT* 1_1\".constraint_name", "((\"*SELECT* 1\".attname)::information_schema.sql_identifier)"],
"Sort Method": "quicksort",
"Sort Space Used": 25,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 5153046,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 62.84,
"Total Cost": 538.22,
"Plan Rows": 1,
"Plan Width": 128,
"Actual Startup Time": 1971.500,
"Actual Total Time": 14303.694,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["\"*SELECT* 1_1\".constraint_name", "((\"*SELECT* 1\".attname)::information_schema.sql_identifier)"],
"Inner Unique": false,
"Join Filter": "(\"*SELECT* 1\".conname = (\"*SELECT* 1_1\".constraint_name)::name)",
"Rows Removed by Join Filter": 997,
"Shared Hit Blocks": 5153046,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Alias": "*SELECT* 1_1",
"Startup Cost": 0.42,
"Total Cost": 71.27,
"Plan Rows": 1,
"Plan Width": 64,
"Actual Startup Time": 0.080,
"Actual Total Time": 46.686,
"Actual Rows": 998,
"Actual Loops": 1,
"Output": ["\"*SELECT* 1_1\".constraint_name"],
"Shared Hit Blocks": 6013,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Result",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.42,
"Total Cost": 71.26,
"Plan Rows": 1,
"Plan Width": 544,
"Actual Startup Time": 0.078,
"Actual Total Time": 45.877,
"Actual Rows": 998,
"Actual Loops": 1,
"Output": ["NULL::information_schema.sql_identifier", "NULL::information_schema.sql_identifier", "(c.conname)::information_schema.sql_identifier", "NULL::information_schema.sql_identifier", "NULL::information_schema.sql_identifier", "NULL::information_schema.sql_identifier", "NULL::information_schema.character_data", "NULL::information_schema.yes_or_no", "NULL::information_schema.yes_or_no", "NULL::information_schema.yes_or_no", "NULL::information_schema.yes_or_no"],
"One-Time Filter": "(current_database() = current_database())",
"Shared Hit Blocks": 6013,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.42,
"Total Cost": 71.26,
"Plan Rows": 1,
"Plan Width": 64,
"Actual Startup Time": 0.070,
"Actual Total Time": 44.470,
"Actual Rows": 998,
"Actual Loops": 1,
"Output": ["c.conname"],
"Inner Unique": true,
"Shared Hit Blocks": 6013,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.28,
"Total Cost": 71.09,
"Plan Rows": 1,
"Plan Width": 68,
"Actual Startup Time": 0.058,
"Actual Total Time": 34.322,
"Actual Rows": 998,
"Actual Loops": 1,
"Output": ["c.conname", "r.relnamespace"],
"Inner Unique": true,
"Join Filter": "(nc.oid = c.connamespace)",
"Rows Removed by Join Filter": 2870,
"Shared Hit Blocks": 4017,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.28,
"Total Cost": 70.00,
"Plan Rows": 1,
"Plan Width": 72,
"Actual Startup Time": 0.048,
"Actual Total Time": 19.368,
"Actual Rows": 998,
"Actual Loops": 1,
"Output": ["c.conname", "c.connamespace", "r.relnamespace"],
"Inner Unique": true,
"Shared Hit Blocks": 3019,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "pg_constraint",
"Schema": "pg_catalog",
"Alias": "c",
"Startup Cost": 0.00,
"Total Cost": 54.80,
"Plan Rows": 6,
"Plan Width": 72,
"Actual Startup Time": 0.023,
"Actual Total Time": 3.497,
"Actual Rows": 998,
"Actual Loops": 1,
"Output": ["c.oid", "c.conname", "c.connamespace", "c.contype", "c.condeferrable", "c.condeferred", "c.convalidated", "c.conrelid", "c.contypid", "c.conindid", "c.conparentid", "c.confrelid", "c.confupdtype", "c.confdeltype", "c.confmatchtype", "c.conislocal", "c.coninhcount", "c.connoinherit", "c.conkey", "c.confkey", "c.conpfeqop", "c.conppeqop", "c.conffeqop", "c.confdelsetcols", "c.conexclop", "c.conbin"],
"Filter": "((c.contype <> ALL ('{t,x}'::\"char\"[])) AND ((CASE c.contype WHEN 'c'::\"char\" THEN 'CHECK'::text WHEN 'f'::\"char\" THEN 'FOREIGN KEY'::text WHEN 'p'::\"char\" THEN 'PRIMARY KEY'::text WHEN 'u'::\"char\" THEN 'UNIQUE'::text ELSE NULL::text END)::text = 'PRIMARY KEY'::text))",
"Rows Removed by Filter": 194,
"Shared Hit Blocks": 25,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "pg_class_oid_index",
"Relation Name": "pg_class",
"Schema": "pg_catalog",
"Alias": "r",
"Startup Cost": 0.28,
"Total Cost": 2.53,
"Plan Rows": 1,
"Plan Width": 8,
"Actual Startup Time": 0.012,
"Actual Total Time": 0.012,
"Actual Rows": 1,
"Actual Loops": 998,
"Output": ["r.oid", "r.relname", "r.relnamespace", "r.reltype", "r.reloftype", "r.relowner", "r.relam", "r.relfilenode", "r.reltablespace", "r.relpages", "r.reltuples", "r.relallvisible", "r.reltoastrelid", "r.relhasindex", "r.relisshared", "r.relpersistence", "r.relkind", "r.relnatts", "r.relchecks", "r.relhasrules", "r.relhastriggers", "r.relhassubclass", "r.relrowsecurity", "r.relforcerowsecurity", "r.relispopulated", "r.relreplident", "r.relispartition", "r.relrewrite", "r.relfrozenxid", "r.relminmxid", "r.relacl", "r.reloptions", "r.relpartbound"],
"Index Cond": "(r.oid = c.conrelid)",
"Rows Removed by Index Recheck": 0,
"Filter": "((r.relkind = ANY ('{r,p}'::\"char\"[])) AND (pg_has_role(r.relowner, 'USAGE'::text) OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES'::text)))",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 2994,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nc",
"Startup Cost": 0.00,
"Total Cost": 1.04,
"Plan Rows": 4,
"Plan Width": 4,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 4,
"Actual Loops": 998,
"Output": ["nc.oid", "nc.nspname", "nc.nspowner", "nc.nspacl"],
"Shared Hit Blocks": 998,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "pg_namespace_oid_index",
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nr",
"Startup Cost": 0.13,
"Total Cost": 0.15,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.007,
"Actual Total Time": 0.007,
"Actual Rows": 1,
"Actual Loops": 998,
"Output": ["nr.oid"],
"Index Cond": "(nr.oid = r.relnamespace)",
"Rows Removed by Index Recheck": 0,
"Filter": "(NOT pg_is_other_temp_schema(nr.oid))",
"Rows Removed by Filter": 0,
"Heap Fetches": 998,
"Shared Hit Blocks": 1996,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
},
{
"Node Type": "Append",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 62.42,
"Total Cost": 466.92,
"Plan Rows": 2,
"Plan Width": 128,
"Actual Startup Time": 2.112,
"Actual Total Time": 14.279,
"Actual Rows": 1,
"Actual Loops": 998,
"Shared Hit Blocks": 5147033,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Subplans Removed": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Alias": "*SELECT* 1",
"Startup Cost": 62.42,
"Total Cost": 62.45,
"Plan Rows": 1,
"Plan Width": 128,
"Actual Startup Time": 0.010,
"Actual Total Time": 0.010,
"Actual Rows": 0,
"Actual Loops": 998,
"Output": ["\"*SELECT* 1\".conname", "(\"*SELECT* 1\".attname)::information_schema.sql_identifier"],
"Filter": "(((\"*SELECT* 1\".relname)::information_schema.sql_identifier)::name = 't_c56ng1_repository'::name)",
"Rows Removed by Filter": 6,
"Shared Hit Blocks": 157,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Unique",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 62.42,
"Total Cost": 62.44,
"Plan Rows": 1,
"Plan Width": 324,
"Actual Startup Time": 0.003,
"Actual Total Time": 0.009,
"Actual Rows": 6,
"Actual Loops": 998,
"Output": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "a.attname", "nc_1.nspname", "c_1.conname"],
"Shared Hit Blocks": 157,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 62.42,
"Total Cost": 62.42,
"Plan Rows": 1,
"Plan Width": 324,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.003,
"Actual Rows": 12,
"Actual Loops": 998,
"Output": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "a.attname", "nc_1.nspname", "c_1.conname"],
"Sort Key": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "a.attname", "nc_1.nspname", "c_1.conname"],
"Sort Method": "quicksort",
"Sort Space Used": 29,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 157,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.99,
"Total Cost": 62.41,
"Plan Rows": 1,
"Plan Width": 324,
"Actual Startup Time": 0.121,
"Actual Total Time": 0.595,
"Actual Rows": 12,
"Actual Loops": 1,
"Output": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "a.attname", "nc_1.nspname", "c_1.conname"],
"Inner Unique": true,
"Join Filter": "(nc_1.oid = c_1.connamespace)",
"Rows Removed by Join Filter": 36,
"Shared Hit Blocks": 157,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.99,
"Total Cost": 61.32,
"Plan Rows": 1,
"Plan Width": 264,
"Actual Startup Time": 0.114,
"Actual Total Time": 0.547,
"Actual Rows": 12,
"Actual Loops": 1,
"Output": ["nr_1.nspname", "r_1.relname", "r_1.relowner", "a.attname", "c_1.conname", "c_1.connamespace"],
"Inner Unique": true,
"Shared Hit Blocks": 145,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.86,
"Total Cost": 61.15,
"Plan Rows": 1,
"Plan Width": 204,
"Actual Startup Time": 0.107,
"Actual Total Time": 0.513,
"Actual Rows": 12,
"Actual Loops": 1,
"Output": ["r_1.relname", "r_1.relowner", "r_1.relnamespace", "a.attname", "c_1.conname", "c_1.connamespace"],
"Inner Unique": true,
"Shared Hit Blocks": 121,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.58,
"Total Cost": 60.84,
"Plan Rows": 1,
"Plan Width": 140,
"Actual Startup Time": 0.092,
"Actual Total Time": 0.449,
"Actual Rows": 12,
"Actual Loops": 1,
"Output": ["a.attname", "a.attrelid", "d.refobjid", "c_1.conname", "c_1.connamespace"],
"Inner Unique": true,
"Shared Hit Blocks": 85,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 0.29,
"Total Cost": 60.06,
"Plan Rows": 1,
"Plan Width": 76,
"Actual Startup Time": 0.076,
"Actual Total Time": 0.366,
"Actual Rows": 12,
"Actual Loops": 1,
"Output": ["d.refobjid", "d.refobjsubid", "c_1.conname", "c_1.connamespace"],
"Inner Unique": false,
"Shared Hit Blocks": 49,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "pg_constraint",
"Schema": "pg_catalog",
"Alias": "c_1",
"Startup Cost": 0.00,
"Total Cost": 39.90,
"Plan Rows": 8,
"Plan Width": 72,
"Actual Startup Time": 0.038,
"Actual Total Time": 0.295,
"Actual Rows": 8,
"Actual Loops": 1,
"Output": ["c_1.oid", "c_1.conname", "c_1.connamespace", "c_1.contype", "c_1.condeferrable", "c_1.condeferred", "c_1.convalidated", "c_1.conrelid", "c_1.contypid", "c_1.conindid", "c_1.conparentid", "c_1.confrelid", "c_1.confupdtype", "c_1.confdeltype", "c_1.confmatchtype", "c_1.conislocal", "c_1.coninhcount", "c_1.connoinherit", "c_1.conkey", "c_1.confkey", "c_1.conpfeqop", "c_1.conppeqop", "c_1.conffeqop", "c_1.confdelsetcols", "c_1.conexclop", "c_1.conbin"],
"Filter": "(c_1.contype = 'c'::\"char\")",
"Rows Removed by Filter": 1184,
"Shared Hit Blocks": 25,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "pg_depend_depender_index",
"Relation Name": "pg_depend",
"Schema": "pg_catalog",
"Alias": "d",
"Startup Cost": 0.29,
"Total Cost": 2.51,
"Plan Rows": 1,
"Plan Width": 12,
"Actual Startup Time": 0.006,
"Actual Total Time": 0.007,
"Actual Rows": 2,
"Actual Loops": 8,
"Output": ["d.classid", "d.objid", "d.objsubid", "d.refclassid", "d.refobjid", "d.refobjsubid", "d.deptype"],
"Index Cond": "((d.classid = '2606'::oid) AND (d.objid = c_1.oid))",
"Rows Removed by Index Recheck": 0,
"Filter": "(d.refclassid = '1259'::oid)",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 24,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "pg_attribute_relid_attnum_index",
"Relation Name": "pg_attribute",
"Schema": "pg_catalog",
"Alias": "a",
"Startup Cost": 0.29,
"Total Cost": 0.78,
"Plan Rows": 1,
"Plan Width": 70,
"Actual Startup Time": 0.006,
"Actual Total Time": 0.006,
"Actual Rows": 1,
"Actual Loops": 12,
"Output": ["a.attrelid", "a.attname", "a.atttypid", "a.attlen", "a.attnum", "a.attcacheoff", "a.atttypmod", "a.attndims", "a.attbyval", "a.attalign", "a.attstorage", "a.attcompression", "a.attnotnull", "a.atthasdef", "a.atthasmissing", "a.attidentity", "a.attgenerated", "a.attisdropped", "a.attislocal", "a.attinhcount", "a.attstattarget", "a.attcollation", "a.attacl", "a.attoptions", "a.attfdwoptions", "a.attmissingval"],
"Index Cond": "((a.attrelid = d.refobjid) AND (a.attnum = d.refobjsubid))",
"Rows Removed by Index Recheck": 0,
"Filter": "(NOT a.attisdropped)",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "pg_class_oid_index",
"Relation Name": "pg_class",
"Schema": "pg_catalog",
"Alias": "r_1",
"Startup Cost": 0.28,
"Total Cost": 0.31,
"Plan Rows": 1,
"Plan Width": 76,
"Actual Startup Time": 0.004,
"Actual Total Time": 0.004,
"Actual Rows": 1,
"Actual Loops": 12,
"Output": ["r_1.oid", "r_1.relname", "r_1.relnamespace", "r_1.reltype", "r_1.reloftype", "r_1.relowner", "r_1.relam", "r_1.relfilenode", "r_1.reltablespace", "r_1.relpages", "r_1.reltuples", "r_1.relallvisible", "r_1.reltoastrelid", "r_1.relhasindex", "r_1.relisshared", "r_1.relpersistence", "r_1.relkind", "r_1.relnatts", "r_1.relchecks", "r_1.relhasrules", "r_1.relhastriggers", "r_1.relhassubclass", "r_1.relrowsecurity", "r_1.relforcerowsecurity", "r_1.relispopulated", "r_1.relreplident", "r_1.relispartition", "r_1.relrewrite", "r_1.relfrozenxid", "r_1.relminmxid", "r_1.relacl", "r_1.reloptions", "r_1.relpartbound"],
"Index Cond": "(r_1.oid = a.attrelid)",
"Rows Removed by Index Recheck": 0,
"Filter": "((r_1.relkind = ANY ('{r,p}'::\"char\"[])) AND pg_has_role(r_1.relowner, 'USAGE'::text))",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 36,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "pg_namespace_oid_index",
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nr_1",
"Startup Cost": 0.13,
"Total Cost": 0.16,
"Plan Rows": 1,
"Plan Width": 68,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 12,
"Output": ["nr_1.oid", "nr_1.nspname", "nr_1.nspowner", "nr_1.nspacl"],
"Index Cond": "(nr_1.oid = r_1.relnamespace)",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 24,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Seq Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nc_1",
"Startup Cost": 0.00,
"Total Cost": 1.04,
"Plan Rows": 4,
"Plan Width": 68,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.001,
"Actual Rows": 4,
"Actual Loops": 12,
"Output": ["nc_1.oid", "nc_1.nspname", "nc_1.nspowner", "nc_1.nspacl"],
"Shared Hit Blocks": 12,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
},
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Alias": "*SELECT* 2",
"Startup Cost": 276.16,
"Total Cost": 404.46,
"Plan Rows": 1,
"Plan Width": 128,
"Actual Startup Time": 2.102,
"Actual Total Time": 14.267,
"Actual Rows": 1,
"Actual Loops": 998,
"Output": ["\"*SELECT* 2\".conname", "(\"*SELECT* 2\".attname)::information_schema.sql_identifier"],
"Filter": "(((\"*SELECT* 2\".relname)::information_schema.sql_identifier)::name = 't_c56ng1_repository'::name)",
"Rows Removed by Filter": 1784,
"Shared Hit Blocks": 5146876,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 276.16,
"Total Cost": 404.21,
"Plan Rows": 20,
"Plan Width": 324,
"Actual Startup Time": 0.018,
"Actual Total Time": 14.078,
"Actual Rows": 1785,
"Actual Loops": 998,
"Output": ["NULL::name", "r_2.relname", "NULL::oid", "a_1.attname", "NULL::name", "c_2.conname"],
"Inner Unique": true,
"Shared Hit Blocks": 5146876,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 276.02,
"Total Cost": 403.19,
"Plan Rows": 20,
"Plan Width": 196,
"Actual Startup Time": 0.017,
"Actual Total Time": 13.356,
"Actual Rows": 1785,
"Actual Loops": 998,
"Output": ["r_2.relname", "a_1.attname", "c_2.conname", "c_2.connamespace"],
"Inner Unique": true,
"Shared Hit Blocks": 5146872,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 275.88,
"Total Cost": 402.00,
"Plan Rows": 20,
"Plan Width": 200,
"Actual Startup Time": 0.017,
"Actual Total Time": 12.644,
"Actual Rows": 1785,
"Actual Loops": 998,
"Output": ["r_2.relname", "r_2.relnamespace", "a_1.attname", "c_2.conname", "c_2.connamespace"],
"Inner Unique": false,
"Join Filter": "(r_2.oid = a_1.attrelid)",
"Rows Removed by Join Filter": 0,
"Shared Hit Blocks": 5146868,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Hash Join",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Join Type": "Inner",
"Startup Cost": 275.59,
"Total Cost": 320.23,
"Plan Rows": 69,
"Plan Width": 205,
"Actual Startup Time": 0.008,
"Actual Total Time": 0.881,
"Actual Rows": 1184,
"Actual Loops": 998,
"Output": ["r_2.relname", "r_2.relnamespace", "r_2.oid", "c_2.conname", "c_2.connamespace", "c_2.contype", "c_2.confrelid", "c_2.conrelid", "c_2.confkey", "c_2.conkey"],
"Inner Unique": true,
"Hash Cond": "(CASE c_2.contype WHEN 'f'::\"char\" THEN c_2.confrelid ELSE c_2.conrelid END = r_2.oid)",
"Shared Hit Blocks": 25132,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "pg_constraint",
"Schema": "pg_catalog",
"Alias": "c_2",
"Startup Cost": 0.00,
"Total Cost": 41.39,
"Plan Rows": 1184,
"Plan Width": 133,
"Actual Startup Time": 0.004,
"Actual Total Time": 0.294,
"Actual Rows": 1184,
"Actual Loops": 998,
"Output": ["c_2.oid", "c_2.conname", "c_2.connamespace", "c_2.contype", "c_2.condeferrable", "c_2.condeferred", "c_2.convalidated", "c_2.conrelid", "c_2.contypid", "c_2.conindid", "c_2.conparentid", "c_2.confrelid", "c_2.confupdtype", "c_2.confdeltype", "c_2.confmatchtype", "c_2.conislocal", "c_2.coninhcount", "c_2.connoinherit", "c_2.conkey", "c_2.confkey", "c_2.conpfeqop", "c_2.conppeqop", "c_2.conffeqop", "c_2.confdelsetcols", "c_2.conexclop", "c_2.conbin"],
"Filter": "(c_2.contype = ANY ('{p,u,f}'::\"char\"[]))",
"Rows Removed by Filter": 8,
"Shared Hit Blocks": 24950,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Hash",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 271.24,
"Total Cost": 271.24,
"Plan Rows": 348,
"Plan Width": 72,
"Actual Startup Time": 2.542,
"Actual Total Time": 2.543,
"Actual Rows": 1045,
"Actual Loops": 1,
"Output": ["r_2.relname", "r_2.relnamespace", "r_2.oid"],
"Hash Buckets": 2048,
"Original Hash Buckets": 1024,
"Hash Batches": 1,
"Original Hash Batches": 1,
"Peak Memory Usage": 123,
"Shared Hit Blocks": 182,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "pg_class",
"Schema": "pg_catalog",
"Alias": "r_2",
"Startup Cost": 0.00,
"Total Cost": 271.24,
"Plan Rows": 348,
"Plan Width": 72,
"Actual Startup Time": 0.009,
"Actual Total Time": 2.238,
"Actual Rows": 1045,
"Actual Loops": 1,
"Output": ["r_2.relname", "r_2.relnamespace", "r_2.oid"],
"Filter": "((r_2.relkind = ANY ('{r,p}'::\"char\"[])) AND pg_has_role(r_2.relowner, 'USAGE'::text))",
"Rows Removed by Filter": 4904,
"Shared Hit Blocks": 182,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "pg_attribute_relid_attnum_index",
"Relation Name": "pg_attribute",
"Schema": "pg_catalog",
"Alias": "a_1",
"Startup Cost": 0.29,
"Total Cost": 1.17,
"Plan Rows": 1,
"Plan Width": 70,
"Actual Startup Time": 0.005,
"Actual Total Time": 0.009,
"Actual Rows": 2,
"Actual Loops": 1181632,
"Output": ["a_1.attrelid", "a_1.attname", "a_1.atttypid", "a_1.attlen", "a_1.attnum", "a_1.attcacheoff", "a_1.atttypmod", "a_1.attndims", "a_1.attbyval", "a_1.attalign", "a_1.attstorage", "a_1.attcompression", "a_1.attnotnull", "a_1.atthasdef", "a_1.atthasmissing", "a_1.attidentity", "a_1.attgenerated", "a_1.attisdropped", "a_1.attislocal", "a_1.attinhcount", "a_1.attstattarget", "a_1.attcollation", "a_1.attacl", "a_1.attoptions", "a_1.attfdwoptions", "a_1.attmissingval"],
"Index Cond": "(a_1.attrelid = CASE c_2.contype WHEN 'f'::\"char\" THEN c_2.confrelid ELSE c_2.conrelid END)",
"Rows Removed by Index Recheck": 0,
"Filter": "((NOT a_1.attisdropped) AND (a_1.attnum = ANY (CASE c_2.contype WHEN 'f'::\"char\" THEN c_2.confkey ELSE c_2.conkey END)))",
"Rows Removed by Filter": 17,
"Shared Hit Blocks": 5121736,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
},
{
"Node Type": "Memoize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.14,
"Total Cost": 0.17,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.000,
"Actual Rows": 1,
"Actual Loops": 1781430,
"Output": ["nr_2.oid"],
"Cache Key": "r_2.relnamespace",
"Cache Mode": "logical",
"Cache Hits": 1781428,
"Cache Misses": 2,
"Cache Evictions": 0,
"Cache Overflows": 0,
"Peak Memory Usage": 1,
"Shared Hit Blocks": 4,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "pg_namespace_oid_index",
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nr_2",
"Startup Cost": 0.13,
"Total Cost": 0.16,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.004,
"Actual Total Time": 0.004,
"Actual Rows": 1,
"Actual Loops": 2,
"Output": ["nr_2.oid"],
"Index Cond": "(nr_2.oid = r_2.relnamespace)",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 2,
"Shared Hit Blocks": 4,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
},
{
"Node Type": "Memoize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.14,
"Total Cost": 0.16,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.000,
"Actual Total Time": 0.000,
"Actual Rows": 1,
"Actual Loops": 1781430,
"Output": ["nc_2.oid"],
"Cache Key": "c_2.connamespace",
"Cache Mode": "logical",
"Cache Hits": 1781428,
"Cache Misses": 2,
"Cache Evictions": 0,
"Cache Overflows": 0,
"Peak Memory Usage": 1,
"Shared Hit Blocks": 4,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Index Only Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Forward",
"Index Name": "pg_namespace_oid_index",
"Relation Name": "pg_namespace",
"Schema": "pg_catalog",
"Alias": "nc_2",
"Startup Cost": 0.13,
"Total Cost": 0.15,
"Plan Rows": 1,
"Plan Width": 4,
"Actual Startup Time": 0.002,
"Actual Total Time": 0.002,
"Actual Rows": 1,
"Actual Loops": 2,
"Output": ["nc_2.oid"],
"Index Cond": "(nc_2.oid = c_2.connamespace)",
"Rows Removed by Index Recheck": 0,
"Heap Fetches": 2,
"Shared Hit Blocks": 4,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
"Settings": {
"work_mem": "16MB",
"hash_mem_multiplier": "1",
"effective_io_concurrency": "400",
"max_parallel_workers": "4",
"random_page_cost": "1.1",
"effective_cache_size": "36GB",
"jit": "off"
},
"Query Identifier": 7256322301156103390,
"Planning": {
"Shared Hit Blocks": 76,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"I/O Read Time": 0.000,
"I/O Write Time": 0.000,
"Temp I/O Read Time": 0.000,
"Temp I/O Write Time": 0.000
},
"Planning Time": 6.945,
"Triggers": [
],
"Execution Time": 14304.353
}
]
Attachments:
[text/plain] PG13_32644.txt (80.1K, 3-PG13_32644.txt)
download
[text/plain] PG16_32644.txt (92.0K, 4-PG16_32644.txt)
download
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
@ 2024-08-26 22:10 ` Adrian Klaver <[email protected]>
2024-08-26 22:41 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
1 sibling, 1 reply; 22+ messages in thread
From: Adrian Klaver @ 2024-08-26 22:10 UTC (permalink / raw)
To: nikhil raj <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>; +Cc: NIKITA PATEL <[email protected]>; Patel Khushbu <[email protected]>
On 8/26/24 14:49, nikhil raj wrote:
> Hi All,
>
> I've encountered a noticeable difference in execution time and query
> execution plan row counts between PostgreSQL 13 and PostgreSQL 16 when
> running a query on |information_schema| tables. Surprisingly, PostgreSQL
> 16 is performing slower than PostgreSQL 13.
Did you run ANALYZE on the Postgres 16 instance?
> *4PostgreSQL 13.14 (PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by
> gcc 11.4.0, 64-bit)*
> Execution plan: PG13.14 Execution Plan
> <https://explain.dalibo.com/plan/ag1a62a9d47dg29d;
>
> *PostgreSQL 16.4 (PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by
> gcc 11.4.0, 64-bit)*
> Execution plan: PG16.4 Execution Plan
> <https://explain.dalibo.com/plan/4c66fdfbf2hf9ed2;
Use:
https://explain.depesz.com/
It is easier to follow it's output.
>
>
> Has anyone else experienced similar behavior or could provide insights
> into why PostgreSQL 16 might be slower for this query? Any advice or
> suggestions for optimization would be greatly appreciated.
Yes when ANALYZE was not run on a new instance.
>
> Thank you!
>
> NOTE:- PFA the raw file of explain and analyze below.
>
>
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-26 22:10 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Adrian Klaver <[email protected]>
@ 2024-08-26 22:41 ` nikhil raj <[email protected]>
0 siblings, 0 replies; 22+ messages in thread
From: nikhil raj @ 2024-08-26 22:41 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>; NIKITA PATEL <[email protected]>; Patel Khushbu <[email protected]>
Hi Adrian,
Thanks for the quick response.
I've already performed a vacuum, reindex, and analyze on the entire
database, but the issue persists. As you can see from the execution plan,
the time difference in PostgreSQL 16 is still significantly higher, even
after all maintenance activities have been completed.
It seems there might be a bug in PostgreSQL 16 where the performance of
queries on *information_schema* tables is degraded. As both the tables are
postgres system tables
https://explain.depesz.com/s/bdO6b :-PG13
<https://explain.depesz.com/s/bdO6b;
https://explain.depesz.com/s/bpAU :- PG16
<https://explain.depesz.com/s/bpAU;
On Tue 27 Aug, 2024, 3:40 AM Adrian Klaver, <[email protected]>
wrote:
> On 8/26/24 14:49, nikhil raj wrote:
> > Hi All,
> >
> > I've encountered a noticeable difference in execution time and query
> > execution plan row counts between PostgreSQL 13 and PostgreSQL 16 when
> > running a query on |information_schema| tables. Surprisingly, PostgreSQL
> > 16 is performing slower than PostgreSQL 13.
>
> Did you run ANALYZE on the Postgres 16 instance?
>
> > *4PostgreSQL 13.14 (PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by
> > gcc 11.4.0, 64-bit)*
> > Execution plan: PG13.14 Execution Plan
> > <https://explain.dalibo.com/plan/ag1a62a9d47dg29d;
> >
> > *PostgreSQL 16.4 (PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by
> > gcc 11.4.0, 64-bit)*
> > Execution plan: PG16.4 Execution Plan
> > <https://explain.dalibo.com/plan/4c66fdfbf2hf9ed2;
>
>
> Use:
>
> https://explain.depesz.com/
>
> It is easier to follow it's output.
>
> >
> >
> > Has anyone else experienced similar behavior or could provide insights
> > into why PostgreSQL 16 might be slower for this query? Any advice or
> > suggestions for optimization would be greatly appreciated.
>
> Yes when ANALYZE was not run on a new instance.
>
> >
> > Thank you!
> >
> > NOTE:- PFA the raw file of explain and analyze below.
> >
> >
> >
> >
>
> --
> Adrian Klaver
> [email protected]
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
@ 2024-08-27 01:40 ` Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
1 sibling, 1 reply; 22+ messages in thread
From: Tom Lane @ 2024-08-27 01:40 UTC (permalink / raw)
To: nikhil raj <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>; NIKITA PATEL <[email protected]>; Patel Khushbu <[email protected]>
nikhil raj <[email protected]> writes:
> I've encountered a noticeable difference in execution time and query
> execution plan row counts between PostgreSQL 13 and PostgreSQL 16 when
> running a query on information_schema tables. Surprisingly, PostgreSQL 16
> is performing slower than PostgreSQL 13.
Yeah, it looks like that condition on "table_name" is not getting
pushed down to the scan level anymore. I'm not sure why not,
but will look closer tomorrow.
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
@ 2024-08-27 01:50 ` David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 06:00 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Justin Clift <[email protected]>
0 siblings, 2 replies; 22+ messages in thread
From: David Rowley @ 2024-08-27 01:50 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: nikhil raj <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>; NIKITA PATEL <[email protected]>; Patel Khushbu <[email protected]>
On Tue, 27 Aug 2024 at 13:40, Tom Lane <[email protected]> wrote:
> Yeah, it looks like that condition on "table_name" is not getting
> pushed down to the scan level anymore. I'm not sure why not,
> but will look closer tomorrow.
I was looking for the offending commit as at first I thought it might
be related to Memoize. It does not seem to be.
I get the following up until 2489d76c, and from then on, it's a subquery filter.
-> Index Scan using pg_class_relname_nsp_index on pg_class r_2
(cost=0.27..8.30 rows=1 width=8) (actual time=0.004..0.004 rows=0
loops=1)
Index Cond: (relname = 't_c56ng1_repository'::name)
Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND
pg_has_role(relowner, 'USAGE'::text))
So looks like it was the "Make Vars be outer-join-aware." commit that
changed this.
David
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
@ 2024-08-27 02:03 ` Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
1 sibling, 1 reply; 22+ messages in thread
From: Tom Lane @ 2024-08-27 02:03 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: nikhil raj <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>; NIKITA PATEL <[email protected]>; Patel Khushbu <[email protected]>
David Rowley <[email protected]> writes:
> On Tue, 27 Aug 2024 at 13:40, Tom Lane <[email protected]> wrote:
>> Yeah, it looks like that condition on "table_name" is not getting
>> pushed down to the scan level anymore. I'm not sure why not,
>> but will look closer tomorrow.
> So looks like it was the "Make Vars be outer-join-aware." commit that
> changed this.
Yeah, I got that same result by bisecting. It seems like it's
somehow related to the cast to information_schema.sql_identifier:
we are able to get rid of that normally but seem to fail to do so
in this query.
There was a smaller increase in the runtime at dfb75e478 "Add primary
keys and unique constraints to system catalogs", but that seems to
just be due to there being more rows in the relevant catalogs.
(That's from testing the query in an empty database; probably the
effect of dfb75e478 would be swamped in a production DB anyway.)
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
@ 2024-08-27 11:03 ` David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
0 siblings, 1 reply; 22+ messages in thread
From: David Rowley @ 2024-08-27 11:03 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: nikhil raj <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>; NIKITA PATEL <[email protected]>; Patel Khushbu <[email protected]>
On Tue, 27 Aug 2024 at 14:03, Tom Lane <[email protected]> wrote:
> Yeah, I got that same result by bisecting. It seems like it's
> somehow related to the cast to information_schema.sql_identifier:
> we are able to get rid of that normally but seem to fail to do so
> in this query.
In case it saves you a bit of time, I stripped as much of the
unrelated stuff out as I could and got:
create table t (a name, b int);
explain select * from (select a::varchar,b from (select distinct a,b
from t) st) t right join t t2 on t.b=t2.b where t.a='test';
getting rid of the cast or swapping to INNER JOIN rather than RIGHT
JOIN means that qual_is_pushdown_safe() gets a Var rather than a
PlaceHolderVar.
David
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
@ 2024-08-27 16:15 ` Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-28 07:08 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
0 siblings, 2 replies; 22+ messages in thread
From: Tom Lane @ 2024-08-27 16:15 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: nikhil raj <[email protected]>; [email protected]
[ switching to -hackers list ]
David Rowley <[email protected]> writes:
> In case it saves you a bit of time, I stripped as much of the
> unrelated stuff out as I could and got:
> create table t (a name, b int);
> explain select * from (select a::varchar,b from (select distinct a,b
> from t) st) t right join t t2 on t.b=t2.b where t.a='test';
> getting rid of the cast or swapping to INNER JOIN rather than RIGHT
> JOIN means that qual_is_pushdown_safe() gets a Var rather than a
> PlaceHolderVar.
Thanks. So it seems that what's happening is that we stick a
PlaceHolderVar on the intermediate subquery's output ("a::varchar"),
and then later when we realize that the RIGHT JOIN can be reduced to
an inner join we run around and remove the right join from the
PlaceHolderVar's nullingrels, leaving a useless PHV with no
nullingrels. remove_nulling_relids explains
* Note: it might seem desirable to remove the PHV altogether if
* phnullingrels goes to empty. Currently we dare not do that
* because we use PHVs in some cases to enforce separate identity
* of subexpressions; see wrap_non_vars usages in prepjointree.c.
However, then when we consider whether the upper WHERE condition
can be pushed down into the unflattened lower subquery,
qual_is_pushdown_safe punts:
* XXX Punt if we find any PlaceHolderVars in the restriction clause.
* It's not clear whether a PHV could safely be pushed down, and even
* less clear whether such a situation could arise in any cases of
* practical interest anyway. So for the moment, just refuse to push
* down.
We didn't see this particular behavior before 2489d76c49 because
pullup_replace_vars avoided inserting a PHV:
* If it contains a Var of the subquery being pulled up, and
* does not contain any non-strict constructs, then it's
* certainly nullable so we don't need to insert a
* PlaceHolderVar.
I dropped that case in 2489d76c49 because now we need to attach
nullingrels to the expression. You could imagine attaching the
nullingrels to the contained Var(s) instead of putting a PHV on top,
but that seems like a mess and I'm not quite sure it's semantically
the same. In any case it wouldn't fix adjacent cases where there is
a non-strict construct in the subquery output expression.
So it seems like we need to fix one or the other of these
implementation shortcuts to restore the previous behavior.
I'm wondering if it'd be okay for qual_is_pushdown_safe to accept
PHVs that have no nullingrels. I'm not really thrilled about trying
to back-patch any such fix though --- the odds of introducing new bugs
seem nontrivial, and the problem case seems rather narrow. If we
are willing to accept a HEAD-only fix, it'd likely be better to
attack the other end and make it possible to remove no-op PHVs.
I think that'd require marking PHVs that need to be kept because
they are serving to isolate subexpressions.
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
@ 2024-08-27 21:52 ` Tom Lane <[email protected]>
2024-08-27 23:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-28 03:30 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
1 sibling, 2 replies; 22+ messages in thread
From: Tom Lane @ 2024-08-27 21:52 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: nikhil raj <[email protected]>; [email protected]
I wrote:
> We didn't see this particular behavior before 2489d76c49 because
> pullup_replace_vars avoided inserting a PHV:
> * If it contains a Var of the subquery being pulled up, and
> * does not contain any non-strict constructs, then it's
> * certainly nullable so we don't need to insert a
> * PlaceHolderVar.
> I dropped that case in 2489d76c49 because now we need to attach
> nullingrels to the expression. You could imagine attaching the
> nullingrels to the contained Var(s) instead of putting a PHV on top,
> but that seems like a mess and I'm not quite sure it's semantically
> the same. In any case it wouldn't fix adjacent cases where there is
> a non-strict construct in the subquery output expression.
I realized that actually we do have the mechanism for making that
work: we could apply add_nulling_relids to the expression, if it
meets those same conditions. This is a kluge really, but it would
restore the status quo ante in a fairly localized fashion that
seems like it might be safe enough to back-patch into v16.
Here's a WIP patch that does it like that. One problem with it
is that it requires rcon->relids to be calculated in cases where
we didn't need that before, which is probably not *that* expensive
but it's annoying. If we go forward with this, I'm thinking about
changing add_nulling_relids' API contract to say "if target_relid
is NULL then all level-zero Vars/PHVs are modified", so that we
don't need that relid set in non-LATERAL cases.
The other problem with this is that it breaks one test case in
memoize.sql: a query that formerly generated a memoize plan
now does not use memoize. I am not sure why not --- does that
mean anything to you?
regards, tom lane
Attachments:
[text/x-diff] avoid-unnecessary-PHV-during-pullup-wip.patch (4.6K, 2-avoid-unnecessary-PHV-during-pullup-wip.patch)
download | inline diff:
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 969e257f70..3a12a52440 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -48,7 +48,7 @@ typedef struct pullup_replace_vars_context
List *targetlist; /* tlist of subquery being pulled up */
RangeTblEntry *target_rte; /* RTE of subquery */
Relids relids; /* relids within subquery, as numbered after
- * pullup (set only if target_rte->lateral) */
+ * pullup */
bool *outer_hasSubLinks; /* -> outer query's hasSubLinks */
int varno; /* varno of subquery */
bool wrap_non_vars; /* do we need all non-Var outputs to be PHVs? */
@@ -1163,11 +1163,8 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
rvcontext.root = root;
rvcontext.targetlist = subquery->targetList;
rvcontext.target_rte = rte;
- if (rte->lateral)
- rvcontext.relids = get_relids_in_jointree((Node *) subquery->jointree,
- true, true);
- else /* won't need relids */
- rvcontext.relids = NULL;
+ rvcontext.relids = get_relids_in_jointree((Node *) subquery->jointree,
+ true, true);
rvcontext.outer_hasSubLinks = &parse->hasSubLinks;
rvcontext.varno = varno;
/* this flag will be set below, if needed */
@@ -1713,7 +1710,7 @@ pull_up_simple_values(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte)
rvcontext.root = root;
rvcontext.targetlist = tlist;
rvcontext.target_rte = rte;
- rvcontext.relids = NULL;
+ rvcontext.relids = NULL; /* XXX */
rvcontext.outer_hasSubLinks = &parse->hasSubLinks;
rvcontext.varno = varno;
rvcontext.wrap_non_vars = false;
@@ -1877,7 +1874,7 @@ pull_up_constant_function(PlannerInfo *root, Node *jtnode,
* lateral references, even if it's marked as LATERAL. This means we
* don't need to fill relids.
*/
- rvcontext.relids = NULL;
+ rvcontext.relids = NULL; /* XXX */
rvcontext.outer_hasSubLinks = &parse->hasSubLinks;
rvcontext.varno = ((RangeTblRef *) jtnode)->rtindex;
@@ -2490,14 +2487,48 @@ pullup_replace_vars_callback(Var *var,
else
wrap = false;
}
+ else if (rcon->wrap_non_vars)
+ {
+ /* Caller told us to wrap all non-Vars in a PlaceHolderVar */
+ wrap = true;
+ }
else
{
/*
- * Must wrap, either because we need a place to insert
- * varnullingrels or because caller told us to wrap
- * everything.
+ * If the node contains Var(s) or PlaceHolderVar(s) of the
+ * subquery being pulled up, and does not contain any
+ * non-strict constructs, then instead of adding a PHV on top
+ * we can add the required nullingrels to those Vars/PHVs.
+ * (This is fundamentally a generalization of the above cases
+ * for bare Vars and PHVs.)
+ *
+ * This test is somewhat expensive, but it avoids pessimizing
+ * the plan in cases where the nullingrels get removed again
+ * later by outer join reduction.
+ *
+ * This analysis could be tighter: in particular, a non-strict
+ * construct hidden within a lower-level PlaceHolderVar is not
+ * reason to add another PHV. But for now it doesn't seem
+ * worth the code to be more exact.
+ *
+ * For a LATERAL subquery, we have to check the actual var
+ * membership of the node, but if it's non-lateral then any
+ * level-zero var must belong to the subquery.
*/
- wrap = true;
+ if ((rcon->target_rte->lateral ?
+ bms_overlap(pull_varnos(rcon->root, newnode),
+ rcon->relids) :
+ contain_vars_of_level(newnode, 0)) &&
+ !contain_nonstrict_functions(newnode))
+ {
+ /* No wrap needed */
+ wrap = false;
+ }
+ else
+ {
+ /* Else wrap it in a PlaceHolderVar */
+ wrap = true;
+ }
}
if (wrap)
@@ -2522,7 +2553,7 @@ pullup_replace_vars_callback(Var *var,
if (var->varlevelsup > 0)
IncrementVarSublevelsUp(newnode, var->varlevelsup, 0);
- /* Propagate any varnullingrels into the replacement Var or PHV */
+ /* Propagate any varnullingrels into the replacement expression */
if (var->varnullingrels != NULL)
{
if (IsA(newnode, Var))
@@ -2542,7 +2573,15 @@ pullup_replace_vars_callback(Var *var,
var->varnullingrels);
}
else
- elog(ERROR, "failed to wrap a non-Var");
+ {
+ /* There should be lower-level Vars/PHVs we can modify */
+ newnode = add_nulling_relids(newnode,
+ rcon->relids,
+ var->varnullingrels);
+ /* Assert we did put the varnullingrels into the expression */
+ Assert(bms_is_subset(var->varnullingrels,
+ pull_varnos(rcon->root, newnode)));
+ }
}
return newnode;
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
@ 2024-08-27 23:03 ` David Rowley <[email protected]>
2024-08-27 23:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
1 sibling, 1 reply; 22+ messages in thread
From: David Rowley @ 2024-08-27 23:03 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Richard Guo <[email protected]>; +Cc: nikhil raj <[email protected]>; [email protected]
On Wed, 28 Aug 2024 at 09:52, Tom Lane <[email protected]> wrote:
> The other problem with this is that it breaks one test case in
> memoize.sql: a query that formerly generated a memoize plan
> now does not use memoize. I am not sure why not --- does that
> mean anything to you?
The reason it works in master is that get_memoize_path() calls
extract_lateral_vars_from_PHVs() and finds PlaceHolderVars to use as
the Memoize keys. With your patch PlannerInfo.placeholder_list is
empty.
The commit that made this work is 069d0ff02. Richard might be able to
explain better. I don't quite understand why RelOptInfo.lateral_vars
don't contain these in the first place.
David
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
@ 2024-08-27 23:15 ` Tom Lane <[email protected]>
2024-08-27 23:37 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
0 siblings, 1 reply; 22+ messages in thread
From: Tom Lane @ 2024-08-27 23:15 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Richard Guo <[email protected]>; nikhil raj <[email protected]>; [email protected]
David Rowley <[email protected]> writes:
> On Wed, 28 Aug 2024 at 09:52, Tom Lane <[email protected]> wrote:
>> The other problem with this is that it breaks one test case in
>> memoize.sql: a query that formerly generated a memoize plan
>> now does not use memoize. I am not sure why not --- does that
>> mean anything to you?
> The reason it works in master is that get_memoize_path() calls
> extract_lateral_vars_from_PHVs() and finds PlaceHolderVars to use as
> the Memoize keys. With your patch PlannerInfo.placeholder_list is
> empty.
That seems like a pretty fishy way to do it. Are you saying that
Memoize is never applicable if there aren't outer joins in the
query? Without OJs there probably won't be any PHVs.
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 23:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
@ 2024-08-27 23:37 ` Tom Lane <[email protected]>
2024-08-27 23:57 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
0 siblings, 1 reply; 22+ messages in thread
From: Tom Lane @ 2024-08-27 23:37 UTC (permalink / raw)
To: ; +Cc: David Rowley <[email protected]>; Richard Guo <[email protected]>; nikhil raj <[email protected]>; [email protected]
I wrote:
> That seems like a pretty fishy way to do it. Are you saying that
> Memoize is never applicable if there aren't outer joins in the
> query? Without OJs there probably won't be any PHVs.
Oh, scratch that, I see you mean this is an additional way to do it
not the only way to do it. But I'm confused why it works for
t1.two+1 AS c1
but not
t1.two+t2.two AS c1
Those ought to look pretty much the same for this purpose.
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 23:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:37 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
@ 2024-08-27 23:57 ` David Rowley <[email protected]>
2024-08-28 07:31 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
0 siblings, 1 reply; 22+ messages in thread
From: David Rowley @ 2024-08-27 23:57 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Richard Guo <[email protected]>; nikhil raj <[email protected]>; [email protected]
On Wed, 28 Aug 2024 at 11:37, Tom Lane <[email protected]> wrote:
> Oh, scratch that, I see you mean this is an additional way to do it
> not the only way to do it. But I'm confused why it works for
> t1.two+1 AS c1
> but not
> t1.two+t2.two AS c1
> Those ought to look pretty much the same for this purpose.
The bms_overlap(pull_varnos(rcon->root, newnode), rcon->relids) test
is false with t1.two+1. Looks like there needs to be a Var from t2
for the bms_overlap to be true
David
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 23:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:37 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:57 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
@ 2024-08-28 07:31 ` Richard Guo <[email protected]>
2024-08-28 20:47 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
0 siblings, 1 reply; 22+ messages in thread
From: Richard Guo @ 2024-08-28 07:31 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Tom Lane <[email protected]>; nikhil raj <[email protected]>; [email protected]
On Wed, Aug 28, 2024 at 7:58 AM David Rowley <[email protected]> wrote:
> On Wed, 28 Aug 2024 at 11:37, Tom Lane <[email protected]> wrote:
> > Oh, scratch that, I see you mean this is an additional way to do it
> > not the only way to do it. But I'm confused why it works for
> > t1.two+1 AS c1
> > but not
> > t1.two+t2.two AS c1
> > Those ought to look pretty much the same for this purpose.
>
> The bms_overlap(pull_varnos(rcon->root, newnode), rcon->relids) test
> is false with t1.two+1. Looks like there needs to be a Var from t2
> for the bms_overlap to be true
Exactly. What Tom's patch does is that if the expression contains
Vars/PHVs that belong to the subquery, and does not contain any
non-strict constructs, then it can escape being wrapped.
In expression 't1.two+t2.two', 't2.two' is a Var that belongs to the
subquery, and '+' is strict, so it can escape being wrapped.
The expression 't1.two+1' does not meet these conditions, so it is
wrapped into a PHV, and the PHV contains lateral reference to t1,
which results in a nestloop join with a parameterized inner path.
That's why Memoize can work in this query.
Thanks
Richard
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 23:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:37 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:57 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-28 07:31 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
@ 2024-08-28 20:47 ` Tom Lane <[email protected]>
2024-08-29 01:54 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
0 siblings, 1 reply; 22+ messages in thread
From: Tom Lane @ 2024-08-28 20:47 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: David Rowley <[email protected]>; nikhil raj <[email protected]>; [email protected]
Richard Guo <[email protected]> writes:
> Exactly. What Tom's patch does is that if the expression contains
> Vars/PHVs that belong to the subquery, and does not contain any
> non-strict constructs, then it can escape being wrapped.
> In expression 't1.two+t2.two', 't2.two' is a Var that belongs to the
> subquery, and '+' is strict, so it can escape being wrapped.
> The expression 't1.two+1' does not meet these conditions, so it is
> wrapped into a PHV, and the PHV contains lateral reference to t1,
> which results in a nestloop join with a parameterized inner path.
> That's why Memoize can work in this query.
Yeah. (I'd missed that t1.two is a lateral reference and t2.two is
not; sorry for the noise.)
What happens as of HEAD is that, because we wrap this subquery output
in a PHV marked as due to be evaluated at t2, the entire clause
(t1.two+t2.two) = t2.unique1
becomes a base restriction clause for t2, so that when we generate
a path for t2 it will include this as a path qual (forcing the path
to be laterally dependent on t1). Without the PHV, it's just an
ordinary join clause and it will not be evaluated at scan level
unless it can be turned into an indexqual --- which it can't.
The preceding regression-test case with "t1.two+1 = t2.unique1"
can be made into a parameterized indexscan on t2.unique1, so it is,
and then memoize can trigger off that.
I'm inclined to think that treating such a clause as a join clause
is strictly better than what happens now, so I'm not going to
apologize for the PHV not being there. If you wanted to cast
blame, you could look to set_plain_rel_pathlist, where it says
* We don't support pushing join clauses into the quals of a seqscan, but
* it could still have required parameterization due to LATERAL refs in
* its tlist.
(This comment could stand some work, as it fails to note that
labeling the path with required parameterization can result in
"join clauses" being evaluated there anyway.)
In the normal course of things I'd be dubious about the value of
pushing join clauses into a seqscan, but maybe the possibility of a
memoize'd join has moved the goalposts enough that we should
consider that. Alternatively, maybe get_memoized_path should take
more responsibility for devising plausible subpaths rather than
assuming they'll be handed to it on a platter. (I don't remember
all the conditions checked in add_path, but I wonder if we are
missing some potential memoize applications because suitable paths
fail to survive the scan rel's add_path tournament.)
In the meantime, I think this test case is mighty artificial,
and it wouldn't bother me any to just take it out again for the
time being.
regards, tom lane
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 23:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:37 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:57 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-28 07:31 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
2024-08-28 20:47 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
@ 2024-08-29 01:54 ` Richard Guo <[email protected]>
2024-08-29 20:53 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
0 siblings, 1 reply; 22+ messages in thread
From: Richard Guo @ 2024-08-29 01:54 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David Rowley <[email protected]>; nikhil raj <[email protected]>; [email protected]
On Thu, Aug 29, 2024 at 4:47 AM Tom Lane <[email protected]> wrote:
> In the meantime, I think this test case is mighty artificial,
> and it wouldn't bother me any to just take it out again for the
> time being.
Yeah, I think we can remove the 't1.two+t2.two' test case if we go
with your proposed patch to address this performance regression.
Thanks
Richard
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 23:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:37 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:57 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-28 07:31 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
2024-08-28 20:47 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-29 01:54 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
@ 2024-08-29 20:53 ` Tom Lane <[email protected]>
2024-09-18 06:19 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
0 siblings, 1 reply; 22+ messages in thread
From: Tom Lane @ 2024-08-29 20:53 UTC (permalink / raw)
To: Richard Guo <[email protected]>; +Cc: David Rowley <[email protected]>; nikhil raj <[email protected]>; [email protected]
Richard Guo <[email protected]> writes:
> On Thu, Aug 29, 2024 at 4:47 AM Tom Lane <[email protected]> wrote:
>> In the meantime, I think this test case is mighty artificial,
>> and it wouldn't bother me any to just take it out again for the
>> time being.
> Yeah, I think we can remove the 't1.two+t2.two' test case if we go
> with your proposed patch to address this performance regression.
Here's a polished-up patchset for that. I made the memoize test
removal a separate patch because (a) it only applies to master
and (b) it seems worth calling out as something we might be able
to revert later.
I found one bug in the draft patch: add_nulling_relids only processes
Vars of level zero, so we have to apply it before not after adjusting
the Vars' levelsup. An alternative could be to add a levelsup
parameter to add_nulling_relids, but that seemed like unnecessary
complication.
regards, tom lane
Attachments:
[text/x-diff] v1-0001-Remove-one-memoize-test-case-added-by-commit-069d.patch (4.0K, 2-v1-0001-Remove-one-memoize-test-case-added-by-commit-069d.patch)
download | inline diff:
From 8fafdc4852b8f2164286e6863219eb6b4d267639 Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
Date: Thu, 29 Aug 2024 16:25:23 -0400
Subject: [PATCH v1 1/2] Remove one memoize test case added by commit
069d0ff02.
This test case turns out to depend on the assumption that a non-Var
subquery output that's underneath an outer join will always get
wrapped in a PlaceHolderVar. But that behavior causes performance
regressions in some cases compared to what happened before v16.
The next commit will avoid inserting a PHV in the same cases where
pre-v16 did, and that causes get_memoized_path to not detect that
a memoize plan could be used.
Commit this separately, in hopes that we can restore the test after
making get_memoized_path smarter. (It's failing to find memoize
plans in adjacent cases where no PHV was ever inserted, so there
is definitely room for improvement there.)
Discussion: https://postgr.es/m/CAG1ps1xvnTZceKK24OUfMKLPvDP2vjT-d+F2AOCWbw_v3KeEgg@mail.gmail.com
---
src/test/regress/expected/memoize.out | 30 ---------------------------
src/test/regress/sql/memoize.sql | 11 ----------
2 files changed, 41 deletions(-)
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index df2ca5ba4e..9ee09fe2f5 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -160,36 +160,6 @@ WHERE s.c1 = s.c2 AND t1.unique1 < 1000;
1000 | 9.5000000000000000
(1 row)
--- Try with LATERAL references within PlaceHolderVars
-SELECT explain_memoize('
-SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
-LATERAL (SELECT t1.two+t2.two AS c1, t2.unique1 AS c2 FROM tenk1 t2) s ON TRUE
-WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
- explain_memoize
---------------------------------------------------------------------------------------
- Aggregate (actual rows=1 loops=N)
- -> Nested Loop (actual rows=1000 loops=N)
- -> Seq Scan on tenk1 t1 (actual rows=1000 loops=N)
- Filter: (unique1 < 1000)
- Rows Removed by Filter: 9000
- -> Memoize (actual rows=1 loops=N)
- Cache Key: t1.two
- Cache Mode: binary
- Hits: 998 Misses: 2 Evictions: Zero Overflows: 0 Memory Usage: NkB
- -> Seq Scan on tenk1 t2 (actual rows=1 loops=N)
- Filter: ((t1.two + two) = unique1)
- Rows Removed by Filter: 9999
-(12 rows)
-
--- And check we get the expected results.
-SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
-LATERAL (SELECT t1.two+t2.two AS c1, t2.unique1 AS c2 FROM tenk1 t2) s ON TRUE
-WHERE s.c1 = s.c2 AND t1.unique1 < 1000;
- count | avg
--------+--------------------
- 1000 | 9.0000000000000000
-(1 row)
-
-- Ensure we do not omit the cache keys from PlaceHolderVars
SELECT explain_memoize('
SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql
index 059bec5f4f..2eaeb1477a 100644
--- a/src/test/regress/sql/memoize.sql
+++ b/src/test/regress/sql/memoize.sql
@@ -85,17 +85,6 @@ SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
LATERAL (SELECT t1.two+1 AS c1, t2.unique1 AS c2 FROM tenk1 t2) s ON TRUE
WHERE s.c1 = s.c2 AND t1.unique1 < 1000;
--- Try with LATERAL references within PlaceHolderVars
-SELECT explain_memoize('
-SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
-LATERAL (SELECT t1.two+t2.two AS c1, t2.unique1 AS c2 FROM tenk1 t2) s ON TRUE
-WHERE s.c1 = s.c2 AND t1.unique1 < 1000;', false);
-
--- And check we get the expected results.
-SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
-LATERAL (SELECT t1.two+t2.two AS c1, t2.unique1 AS c2 FROM tenk1 t2) s ON TRUE
-WHERE s.c1 = s.c2 AND t1.unique1 < 1000;
-
-- Ensure we do not omit the cache keys from PlaceHolderVars
SELECT explain_memoize('
SELECT COUNT(*), AVG(t1.twenty) FROM tenk1 t1 LEFT JOIN
--
2.43.5
[text/x-diff] v1-0002-Avoid-inserting-PlaceHolderVars-in-cases-where-pr.patch (9.0K, 3-v1-0002-Avoid-inserting-PlaceHolderVars-in-cases-where-pr.patch)
download | inline diff:
From 7b48394838797489e7ab869f97ca06449fdbcee3 Mon Sep 17 00:00:00 2001
From: Tom Lane <[email protected]>
Date: Thu, 29 Aug 2024 16:43:35 -0400
Subject: [PATCH v1 2/2] Avoid inserting PlaceHolderVars in cases where pre-v16
PG did not.
Commit 2489d76c4 removed some logic from pullup_replace_vars()
that avoided wrapping a PlaceHolderVar around a pulled-up
subquery output expression if the expression could be proven
to go to NULL anyway (because it contained Vars or PHVs of the
pulled-up relation and did not contain non-strict constructs).
But removing that logic turns out to cause performance regressions
in some cases, because the extra PHV prevents subexpression folding,
and will do so even if outer-join reduction later turns it into a
no-op with no phnullingrels bits.
The reason for always adding a PHV was to ensure we had someplace
to put the varnullingrels marker bits of the Var being replaced.
However, it turns out we can optimize in exactly the same cases that
the previous code did, because we can attach the needed varnullingrels
bits to the contained Var(s)/PHV(s).
This is not a complete solution --- it would be even better if we
could remove PHVs after reducing them to no-ops. It doesn't look
practical to back-patch such an improvement, but this change seems
safe and at least gets rid of the performance-regression cases.
Per complaint from Nikhil Raj. Back-patch to v16 where the
problem appeared.
Discussion: https://postgr.es/m/CAG1ps1xvnTZceKK24OUfMKLPvDP2vjT-d+F2AOCWbw_v3KeEgg@mail.gmail.com
---
src/backend/optimizer/prep/prepjointree.c | 66 ++++++++++++++++++-----
src/backend/rewrite/rewriteManip.c | 9 ++--
src/test/regress/expected/subselect.out | 29 ++++++++++
src/test/regress/sql/subselect.sql | 18 +++++++
4 files changed, 107 insertions(+), 15 deletions(-)
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 969e257f70..34fbf8ee23 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -2490,14 +2490,48 @@ pullup_replace_vars_callback(Var *var,
else
wrap = false;
}
+ else if (rcon->wrap_non_vars)
+ {
+ /* Caller told us to wrap all non-Vars in a PlaceHolderVar */
+ wrap = true;
+ }
else
{
/*
- * Must wrap, either because we need a place to insert
- * varnullingrels or because caller told us to wrap
- * everything.
+ * If the node contains Var(s) or PlaceHolderVar(s) of the
+ * subquery being pulled up, and does not contain any
+ * non-strict constructs, then instead of adding a PHV on top
+ * we can add the required nullingrels to those Vars/PHVs.
+ * (This is fundamentally a generalization of the above cases
+ * for bare Vars and PHVs.)
+ *
+ * This test is somewhat expensive, but it avoids pessimizing
+ * the plan in cases where the nullingrels get removed again
+ * later by outer join reduction.
+ *
+ * This analysis could be tighter: in particular, a non-strict
+ * construct hidden within a lower-level PlaceHolderVar is not
+ * reason to add another PHV. But for now it doesn't seem
+ * worth the code to be more exact.
+ *
+ * For a LATERAL subquery, we have to check the actual var
+ * membership of the node, but if it's non-lateral then any
+ * level-zero var must belong to the subquery.
*/
- wrap = true;
+ if ((rcon->target_rte->lateral ?
+ bms_overlap(pull_varnos(rcon->root, newnode),
+ rcon->relids) :
+ contain_vars_of_level(newnode, 0)) &&
+ !contain_nonstrict_functions(newnode))
+ {
+ /* No wrap needed */
+ wrap = false;
+ }
+ else
+ {
+ /* Else wrap it in a PlaceHolderVar */
+ wrap = true;
+ }
}
if (wrap)
@@ -2518,18 +2552,14 @@ pullup_replace_vars_callback(Var *var,
}
}
- /* Must adjust varlevelsup if replaced Var is within a subquery */
- if (var->varlevelsup > 0)
- IncrementVarSublevelsUp(newnode, var->varlevelsup, 0);
-
- /* Propagate any varnullingrels into the replacement Var or PHV */
+ /* Propagate any varnullingrels into the replacement expression */
if (var->varnullingrels != NULL)
{
if (IsA(newnode, Var))
{
Var *newvar = (Var *) newnode;
- Assert(newvar->varlevelsup == var->varlevelsup);
+ Assert(newvar->varlevelsup == 0);
newvar->varnullingrels = bms_add_members(newvar->varnullingrels,
var->varnullingrels);
}
@@ -2537,14 +2567,26 @@ pullup_replace_vars_callback(Var *var,
{
PlaceHolderVar *newphv = (PlaceHolderVar *) newnode;
- Assert(newphv->phlevelsup == var->varlevelsup);
+ Assert(newphv->phlevelsup == 0);
newphv->phnullingrels = bms_add_members(newphv->phnullingrels,
var->varnullingrels);
}
else
- elog(ERROR, "failed to wrap a non-Var");
+ {
+ /* There should be lower-level Vars/PHVs we can modify */
+ newnode = add_nulling_relids(newnode,
+ NULL, /* modify all Vars/PHVs */
+ var->varnullingrels);
+ /* Assert we did put the varnullingrels into the expression */
+ Assert(bms_is_subset(var->varnullingrels,
+ pull_varnos(rcon->root, newnode)));
+ }
}
+ /* Must adjust varlevelsup if replaced Var is within a subquery */
+ if (var->varlevelsup > 0)
+ IncrementVarSublevelsUp(newnode, var->varlevelsup, 0);
+
return newnode;
}
diff --git a/src/backend/rewrite/rewriteManip.c b/src/backend/rewrite/rewriteManip.c
index 191f2dc0b1..b20625fbd2 100644
--- a/src/backend/rewrite/rewriteManip.c
+++ b/src/backend/rewrite/rewriteManip.c
@@ -1141,7 +1141,8 @@ AddInvertedQual(Query *parsetree, Node *qual)
/*
* add_nulling_relids() finds Vars and PlaceHolderVars that belong to any
* of the target_relids, and adds added_relids to their varnullingrels
- * and phnullingrels fields.
+ * and phnullingrels fields. If target_relids is NULL, all level-zero
+ * Vars and PHVs are modified.
*/
Node *
add_nulling_relids(Node *node,
@@ -1170,7 +1171,8 @@ add_nulling_relids_mutator(Node *node,
Var *var = (Var *) node;
if (var->varlevelsup == context->sublevels_up &&
- bms_is_member(var->varno, context->target_relids))
+ (context->target_relids == NULL ||
+ bms_is_member(var->varno, context->target_relids)))
{
Relids newnullingrels = bms_union(var->varnullingrels,
context->added_relids);
@@ -1188,7 +1190,8 @@ add_nulling_relids_mutator(Node *node,
PlaceHolderVar *phv = (PlaceHolderVar *) node;
if (phv->phlevelsup == context->sublevels_up &&
- bms_overlap(phv->phrels, context->target_relids))
+ (context->target_relids == NULL ||
+ bms_overlap(phv->phrels, context->target_relids)))
{
Relids newnullingrels = bms_union(phv->phnullingrels,
context->added_relids);
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 9eecdc1e92..2d35de3fad 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1721,6 +1721,35 @@ fetch backward all in c1;
(2 rows)
commit;
+--
+-- Verify that we correctly flatten cases involving a subquery output
+-- expression that doesn't need to be wrapped in a PlaceHolderVar
+--
+explain (costs off)
+select tname, attname from (
+select relname::information_schema.sql_identifier as tname, * from
+ (select * from pg_class c) ss1) ss2
+ right join pg_attribute a on a.attrelid = ss2.oid
+where tname = 'tenk1' and attnum = 1;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Nested Loop
+ -> Index Scan using pg_class_relname_nsp_index on pg_class c
+ Index Cond: (relname = 'tenk1'::name)
+ -> Index Scan using pg_attribute_relid_attnum_index on pg_attribute a
+ Index Cond: ((attrelid = c.oid) AND (attnum = 1))
+(5 rows)
+
+select tname, attname from (
+select relname::information_schema.sql_identifier as tname, * from
+ (select * from pg_class c) ss1) ss2
+ right join pg_attribute a on a.attrelid = ss2.oid
+where tname = 'tenk1' and attnum = 1;
+ tname | attname
+-------+---------
+ tenk1 | unique1
+(1 row)
+
--
-- Tests for CTE inlining behavior
--
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 75a9b718b2..af6e157aca 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -890,6 +890,24 @@ fetch backward all in c1;
commit;
+--
+-- Verify that we correctly flatten cases involving a subquery output
+-- expression that doesn't need to be wrapped in a PlaceHolderVar
+--
+
+explain (costs off)
+select tname, attname from (
+select relname::information_schema.sql_identifier as tname, * from
+ (select * from pg_class c) ss1) ss2
+ right join pg_attribute a on a.attrelid = ss2.oid
+where tname = 'tenk1' and attnum = 1;
+
+select tname, attname from (
+select relname::information_schema.sql_identifier as tname, * from
+ (select * from pg_class c) ss1) ss2
+ right join pg_attribute a on a.attrelid = ss2.oid
+where tname = 'tenk1' and attnum = 1;
+
--
-- Tests for CTE inlining behavior
--
--
2.43.5
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 23:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:37 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 23:57 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-28 07:31 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
2024-08-28 20:47 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-29 01:54 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
2024-08-29 20:53 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
@ 2024-09-18 06:19 ` nikhil raj <[email protected]>
0 siblings, 0 replies; 22+ messages in thread
From: nikhil raj @ 2024-09-18 06:19 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Richard Guo <[email protected]>; David Rowley <[email protected]>; [email protected]
Hi All,
I hope you're doing well.
I'm writing to kindly requesting if there is a bug tracker ID or any
reference number associated with this issue, I would appreciate it if you
could share it with me.
Thank you for your time and assistance. Please let me know if there's any
additional information you need from me.
Best regards,
Nikhil
On Fri, 30 Aug, 2024, 2:23 am Tom Lane, <[email protected]> wrote:
> Richard Guo <[email protected]> writes:
> > On Thu, Aug 29, 2024 at 4:47 AM Tom Lane <[email protected]> wrote:
> >> In the meantime, I think this test case is mighty artificial,
> >> and it wouldn't bother me any to just take it out again for the
> >> time being.
>
> > Yeah, I think we can remove the 't1.two+t2.two' test case if we go
> > with your proposed patch to address this performance regression.
>
> Here's a polished-up patchset for that. I made the memoize test
> removal a separate patch because (a) it only applies to master
> and (b) it seems worth calling out as something we might be able
> to revert later.
>
> I found one bug in the draft patch: add_nulling_relids only processes
> Vars of level zero, so we have to apply it before not after adjusting
> the Vars' levelsup. An alternative could be to add a levelsup
> parameter to add_nulling_relids, but that seemed like unnecessary
> complication.
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
@ 2024-08-28 03:30 ` Richard Guo <[email protected]>
2024-08-28 03:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
1 sibling, 1 reply; 22+ messages in thread
From: Richard Guo @ 2024-08-28 03:30 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David Rowley <[email protected]>; nikhil raj <[email protected]>; [email protected]
On Wed, Aug 28, 2024 at 5:52 AM Tom Lane <[email protected]> wrote:
> I realized that actually we do have the mechanism for making that
> work: we could apply add_nulling_relids to the expression, if it
> meets those same conditions.
I think this should work, as long as we apply add_nulling_relids only
to Vars/PHVs that belong to the subquery in this case, because only
those Vars/PHVs would be nulled by the outer joins contained in the
nullingrels.
> If we go forward with this, I'm thinking about
> changing add_nulling_relids' API contract to say "if target_relid
> is NULL then all level-zero Vars/PHVs are modified", so that we
> don't need that relid set in non-LATERAL cases.
+1. In LATERAL case, we can always find the subquery's relids in
rcon->relids. In non-lateral case, any level-zero Vars/PHVs must
belong to the subquery - so if we change add_nulling_relids' API to be
so, we do not need to have rcon->relids set.
Thanks
Richard
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 21:52 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-28 03:30 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Richard Guo <[email protected]>
@ 2024-08-28 03:52 ` Richard Guo <[email protected]>
0 siblings, 0 replies; 22+ messages in thread
From: Richard Guo @ 2024-08-28 03:52 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David Rowley <[email protected]>; nikhil raj <[email protected]>; [email protected]
On Wed, Aug 28, 2024 at 11:30 AM Richard Guo <[email protected]> wrote:
> On Wed, Aug 28, 2024 at 5:52 AM Tom Lane <[email protected]> wrote:
> > I realized that actually we do have the mechanism for making that
> > work: we could apply add_nulling_relids to the expression, if it
> > meets those same conditions.
>
> I think this should work, as long as we apply add_nulling_relids only
> to Vars/PHVs that belong to the subquery in this case, because only
> those Vars/PHVs would be nulled by the outer joins contained in the
> nullingrels.
To be more concrete, I know theoretically it is the whole expression
that is nullable by the outer joins, not its individual vars. But in
this case if the contained vars (that belong to the subquery) become
NULL, the whole expression would be NULL too, because it does not
contain any non-strict constructs. That's why I think this approach
should work.
Thanks
Richard
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 02:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 11:03 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
2024-08-27 16:15 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
@ 2024-08-28 07:08 ` Richard Guo <[email protected]>
1 sibling, 0 replies; 22+ messages in thread
From: Richard Guo @ 2024-08-28 07:08 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David Rowley <[email protected]>; nikhil raj <[email protected]>; [email protected]
On Wed, Aug 28, 2024 at 12:15 AM Tom Lane <[email protected]> wrote:
> If we
> are willing to accept a HEAD-only fix, it'd likely be better to
> attack the other end and make it possible to remove no-op PHVs.
> I think that'd require marking PHVs that need to be kept because
> they are serving to isolate subexpressions.
I think it's always desirable to remove no-op PHVs, even if we end up
with a different approach to fix the issue discussed here. Doing that
could potentially open up opportunities for optimization in other
cases. For example:
explain (costs off)
select * from t t1 left join
lateral (select t1.a as x, * from t t2) s on true
where t1.a = s.a;
QUERY PLAN
----------------------------
Nested Loop
-> Seq Scan on t t1
-> Seq Scan on t t2
Filter: (t1.a = a)
(4 rows)
The target entry s.x is wrapped in a PHV that contains lateral
reference to t1, which forces us to resort to nestloop join. However,
since the left join has been reduced to an inner join, and it is
removed from the PHV's nullingrels, leaving the nullingrels being
empty, we should be able to remove this PHV and use merge or hash
joins, depending on which is cheaper.
I think there may be more cases where no-op PHVs constrain
optimization opportunities.
In [1] when working on the fix-grouping-sets patch, I included a
mechanism in 0003 to remove no-op PHVs by including a flag in
PlaceHolderVar to indicate whether it is safe to remove the PHV when
its phnullingrels becomes empty. In that patch this flag is only set
in cases where the PHV is used to carry the nullingrel bit that
represents the grouping step. Maybe we can extend its use to remove
all no-op PHVs, except those that are serving to isolate
subexpressions.
Any thoughts on this?
[1] https://postgr.es/m/CAMbWs4_2t2pqqCFdS3NYJLwMMkAzYQKBOhKweFt-wE3YOi7rGg@mail.gmail.com
Thanks
Richard
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-27 01:40 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. Tom Lane <[email protected]>
2024-08-27 01:50 ` Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. David Rowley <[email protected]>
@ 2024-08-27 06:00 ` Justin Clift <[email protected]>
1 sibling, 0 replies; 22+ messages in thread
From: Justin Clift @ 2024-08-27 06:00 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Tom Lane <[email protected]>; nikhil raj <[email protected]>; pgsql-generallists.postgresql.org <[email protected]>; NIKITA PATEL <[email protected]>; Patel Khushbu <[email protected]>
On 2024-08-27 11:50, David Rowley wrote:
> On Tue, 27 Aug 2024 at 13:40, Tom Lane <[email protected]> wrote:
>> Yeah, it looks like that condition on "table_name" is not getting
>> pushed down to the scan level anymore. I'm not sure why not,
>> but will look closer tomorrow.
>
> I was looking for the offending commit as at first I thought it might
> be related to Memoize. It does not seem to be.
As a general thought, seeing that this might be an actual problem
should some kind of automated testing be added that checks for
performance regressions like this?
Regards and best wishes,
Justin Clift
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2024-09-18 06:19 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-26 21:49 Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. nikhil raj <[email protected]>
2024-08-26 22:10 ` Adrian Klaver <[email protected]>
2024-08-26 22:41 ` nikhil raj <[email protected]>
2024-08-27 01:40 ` Tom Lane <[email protected]>
2024-08-27 01:50 ` David Rowley <[email protected]>
2024-08-27 02:03 ` Tom Lane <[email protected]>
2024-08-27 11:03 ` David Rowley <[email protected]>
2024-08-27 16:15 ` Tom Lane <[email protected]>
2024-08-27 21:52 ` Tom Lane <[email protected]>
2024-08-27 23:03 ` David Rowley <[email protected]>
2024-08-27 23:15 ` Tom Lane <[email protected]>
2024-08-27 23:37 ` Tom Lane <[email protected]>
2024-08-27 23:57 ` David Rowley <[email protected]>
2024-08-28 07:31 ` Richard Guo <[email protected]>
2024-08-28 20:47 ` Tom Lane <[email protected]>
2024-08-29 01:54 ` Richard Guo <[email protected]>
2024-08-29 20:53 ` Tom Lane <[email protected]>
2024-09-18 06:19 ` nikhil raj <[email protected]>
2024-08-28 03:30 ` Richard Guo <[email protected]>
2024-08-28 03:52 ` Richard Guo <[email protected]>
2024-08-28 07:08 ` Richard Guo <[email protected]>
2024-08-27 06:00 ` Justin Clift <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox