AlexGacon commented on issue #909:
URL:
https://github.com/apache/incubator-baremaps/issues/909#issuecomment-2572868999
The execution plan with the official query:
```json
[
{
"Plan": {
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 326091.02,
"Total Cost": 326091.03,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 23942.343,
"Actual Total Time": 23942.349,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["st_asmvt(ROW(st_asmvtgeom(t.geom, 'BOX(291530.8258796632
6223044.220809339,291683.69993623346 6223197.094865911)'::box2d, 4096, 256,
true), (t.tags - 'id'::text), t.id), 'ban_numero'::text)"],
"Shared Hit Blocks": 212298,
"Shared Read Blocks": 45041,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Alias": "t",
"Startup Cost": 3.94,
"Total Cost": 325479.16,
"Plan Rows": 244,
"Plan Width": 68,
"Actual Startup Time": 19181.448,
"Actual Total Time": 23942.033,
"Actual Rows": 22,
"Actual Loops": 1,
"Output": ["t.id", "t.tags", "t.geom", "ban_numero.addok_id"],
"Filter": "((t.geom IS NOT NULL) AND (t.geom &&
'0103000020110F000001000000050000004043B7BF21CB114154FB417530BD57414043B7BF21CB1141A20AF2DE57BD57414038B85A98CD1141A20AF2DE57BD57414038B85A98CD114154FB417530BD57414043B7BF21CB114154FB417530BD5741'::geometry))",
"Rows Removed by Filter": 2456432,
"Shared Hit Blocks": 212298,
"Shared Read Blocks": 45041,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 3.94,
"Total Cost": 294774.00,
"Plan Rows": 2456413,
"Plan Width": 85,
"Actual Startup Time": 22.052,
"Actual Total Time": 23253.157,
"Actual Rows": 2456454,
"Actual Loops": 1,
"Output": ["(row_number() OVER (?))::integer",
"jsonb_build_object('id', ban_numero.addok_id, 'numero', ban_numero.numero)",
"ban_numero.geom", "ban_numero.addok_id"],
"Shared Hit Blocks": 212298,
"Shared Read Blocks": 45041,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Plans": [
{
"Node Type": "Merge Append",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 3.94,
"Total Cost": 245645.74,
"Plan Rows": 2456413,
"Plan Width": 51,
"Actual Startup Time": 22.027,
"Actual Total Time": 18049.644,
"Actual Rows": 2456454,
"Actual Loops": 1,
"Sort Key": ["ban_numero.addok_id DESC"],
"Shared Hit Blocks": 212298,
"Shared Read Blocks": 45041,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0,
"Subplans Removed": 0,
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Backward",
"Index Name": "ban_numero_ban37_pkey",
"Relation Name": "ban_numero_ban37",
"Schema": "ign",
"Alias": "ban_numero_1",
"Startup Cost": 0.42,
"Total Cost": 21984.03,
"Plan Rows": 276399,
"Plan Width": 51,
"Actual Startup Time": 19.675,
"Actual Total Time": 2230.574,
"Actual Rows": 276440,
"Actual Loops": 1,
"Output": ["ban_numero_1.addok_id",
"ban_numero_1.numero", "ban_numero_1.geom"],
"Shared Hit Blocks": 17017,
"Shared Read Blocks": 5237,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Backward",
"Index Name": "ban_numero_ban75_pkey",
"Relation Name": "ban_numero_ban75",
"Schema": "ign",
"Alias": "ban_numero_2",
"Startup Cost": 0.42,
"Total Cost": 11945.52,
"Plan Rows": 151794,
"Plan Width": 51,
"Actual Startup Time": 0.760,
"Actual Total Time": 1056.292,
"Actual Rows": 151794,
"Actual Loops": 1,
"Output": ["ban_numero_2.addok_id",
"ban_numero_2.numero", "ban_numero_2.geom"],
"Shared Hit Blocks": 12772,
"Shared Read Blocks": 2479,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Backward",
"Index Name": "ban_numero_ban77_pkey",
"Relation Name": "ban_numero_ban77",
"Schema": "ign",
"Alias": "ban_numero_3",
"Startup Cost": 0.42,
"Total Cost": 35954.40,
"Plan Rows": 453582,
"Plan Width": 51,
"Actual Startup Time": 0.705,
"Actual Total Time": 4814.613,
"Actual Rows": 453582,
"Actual Loops": 1,
"Output": ["ban_numero_3.addok_id",
"ban_numero_3.numero", "ban_numero_3.geom"],
"Shared Hit Blocks": 36190,
"Shared Read Blocks": 9983,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Backward",
"Index Name": "ban_numero_ban78_pkey",
"Relation Name": "ban_numero_ban78",
"Schema": "ign",
"Alias": "ban_numero_4",
"Startup Cost": 0.42,
"Total Cost": 27341.41,
"Plan Rows": 345341,
"Plan Width": 51,
"Actual Startup Time": 0.661,
"Actual Total Time": 3267.250,
"Actual Rows": 345341,
"Actual Loops": 1,
"Output": ["ban_numero_4.addok_id",
"ban_numero_4.numero", "ban_numero_4.geom"],
"Shared Hit Blocks": 27499,
"Shared Read Blocks": 8278,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Backward",
"Index Name": "ban_numero_ban91_pkey",
"Relation Name": "ban_numero_ban91",
"Schema": "ign",
"Alias": "ban_numero_5",
"Startup Cost": 0.42,
"Total Cost": 26948.12,
"Plan Rows": 340013,
"Plan Width": 51,
"Actual Startup Time": 0.049,
"Actual Total Time": 3041.894,
"Actual Rows": 340013,
"Actual Loops": 1,
"Output": ["ban_numero_5.addok_id",
"ban_numero_5.numero", "ban_numero_5.geom"],
"Shared Hit Blocks": 32158,
"Shared Read Blocks": 6263,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Backward",
"Index Name": "ban_numero_ban92_pkey",
"Relation Name": "ban_numero_ban92",
"Schema": "ign",
"Alias": "ban_numero_6",
"Startup Cost": 0.42,
"Total Cost": 13102.78,
"Plan Rows": 166049,
"Plan Width": 52,
"Actual Startup Time": 0.032,
"Actual Total Time": 1099.042,
"Actual Rows": 166049,
"Actual Loops": 1,
"Output": ["ban_numero_6.addok_id",
"ban_numero_6.numero", "ban_numero_6.geom"],
"Shared Hit Blocks": 14674,
"Shared Read Blocks": 2637,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Backward",
"Index Name": "ban_numero_ban93_pkey",
"Relation Name": "ban_numero_ban93",
"Schema": "ign",
"Alias": "ban_numero_7",
"Startup Cost": 0.42,
"Total Cost": 18518.74,
"Plan Rows": 234555,
"Plan Width": 52,
"Actual Startup Time": 0.037,
"Actual Total Time": 345.274,
"Actual Rows": 234555,
"Actual Loops": 1,
"Output": ["ban_numero_7.addok_id",
"ban_numero_7.numero", "ban_numero_7.geom"],
"Shared Hit Blocks": 22297,
"Shared Read Blocks": 3450,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Backward",
"Index Name": "ban_numero_ban94_pkey",
"Relation Name": "ban_numero_ban94",
"Schema": "ign",
"Alias": "ban_numero_8",
"Startup Cost": 0.42,
"Total Cost": 15949.44,
"Plan Rows": 201701,
"Plan Width": 52,
"Actual Startup Time": 0.046,
"Actual Total Time": 547.246,
"Actual Rows": 201701,
"Actual Loops": 1,
"Output": ["ban_numero_8.addok_id",
"ban_numero_8.numero", "ban_numero_8.geom"],
"Shared Hit Blocks": 22762,
"Shared Read Blocks": 2558,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "Backward",
"Index Name": "ban_numero_ban95_pkey",
"Relation Name": "ban_numero_ban95",
"Schema": "ign",
"Alias": "ban_numero_9",
"Startup Cost": 0.42,
"Total Cost": 22685.86,
"Plan Rows": 286979,
"Plan Width": 51,
"Actual Startup Time": 0.053,
"Actual Total Time": 817.695,
"Actual Rows": 286979,
"Actual Loops": 1,
"Output": ["ban_numero_9.addok_id",
"ban_numero_9.numero", "ban_numero_9.geom"],
"Shared Hit Blocks": 26929,
"Shared Read Blocks": 4156,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
},
"Settings": {
"effective_cache_size": "5400MB",
"max_parallel_workers": "4",
"work_mem": "12MB"
},
"Query Identifier": -7271303740769922918,
"Planning": {
"Shared Hit Blocks": 1118,
"Shared Read Blocks": 68,
"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
},
"Planning Time": 5.777,
"Triggers": [
],
"JIT": {
"Functions": 24,
"Options": {
"Inlining": false,
"Optimization": false,
"Expressions": true,
"Deforming": true
},
"Timing": {
"Generation": 1.251,
"Inlining": 0.000,
"Optimization": 0.638,
"Emission": 18.494,
"Total": 20.383
}
},
"Execution Time": 23943.837
}
]
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
