AlexGacon commented on issue #909:
URL:
https://github.com/apache/incubator-baremaps/issues/909#issuecomment-2572870483
The execution plan for the rewriten query:
```json
[
{
"Plan": {
"Node Type": "Aggregate",
"Strategy": "Plain",
"Partial Mode": "Simple",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 118.48,
"Total Cost": 118.49,
"Plan Rows": 1,
"Plan Width": 32,
"Actual Startup Time": 10.813,
"Actual Total Time": 10.816,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["st_asmvt(ROW(st_asmvtgeom(toto.geom,
'BOX(291530.8258796632 6223044.220809339,291683.69993623346
6223197.094865911)'::box2d, 4096, 256, true), ((jsonb_build_object('id',
toto.addok_id, 'numero', toto.numero)) - 'id'::text), ((row_number() OVER
(?))::integer)), 'ban_numero'::text)"],
"Shared Hit Blocks": 5,
"Shared Read Blocks": 11,
"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": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 86.81,
"Total Cost": 87.08,
"Plan Rows": 12,
"Plan Width": 85,
"Actual Startup Time": 10.667,
"Actual Total Time": 10.727,
"Actual Rows": 22,
"Actual Loops": 1,
"Output": ["(row_number() OVER (?))::integer",
"jsonb_build_object('id', toto.addok_id, 'numero', toto.numero)", "toto.geom",
"toto.addok_id"],
"Shared Hit Blocks": 5,
"Shared Read Blocks": 11,
"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": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 86.81,
"Total Cost": 86.84,
"Plan Rows": 12,
"Plan Width": 51,
"Actual Startup Time": 10.646,
"Actual Total Time": 10.651,
"Actual Rows": 22,
"Actual Loops": 1,
"Output": ["toto.addok_id", "toto.numero", "toto.geom"],
"Sort Key": ["toto.addok_id DESC"],
"Sort Method": "quicksort",
"Sort Space Used": 26,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 5,
"Shared Read Blocks": 11,
"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": "Append",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Startup Cost": 0.28,
"Total Cost": 86.59,
"Plan Rows": 12,
"Plan Width": 51,
"Actual Startup Time": 4.468,
"Actual Total Time": 10.584,
"Actual Rows": 22,
"Actual Loops": 1,
"Shared Hit Blocks": 5,
"Shared Read Blocks": 11,
"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": "NoMovement",
"Index Name": "ban_numero_ban37_geom_idx",
"Relation Name": "ban_numero_ban37",
"Schema": "ign",
"Alias": "toto_1",
"Startup Cost": 0.28,
"Total Cost": 8.30,
"Plan Rows": 1,
"Plan Width": 51,
"Actual Startup Time": 0.829,
"Actual Total Time": 0.829,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["toto_1.addok_id", "toto_1.numero",
"toto_1.geom"],
"Index Cond": "((toto_1.geom IS NOT NULL) AND
(toto_1.geom &&
'0103000020110F000001000000050000004043B7BF21CB114154FB417530BD57414043B7BF21CB1141A20AF2DE57BD57414038B85A98CD1141A20AF2DE57BD57414038B85A98CD114154FB417530BD57414043B7BF21CB114154FB417530BD5741'::geometry))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 0,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "NoMovement",
"Index Name": "ban_numero_ban75_geom_idx",
"Relation Name": "ban_numero_ban75",
"Schema": "ign",
"Alias": "toto_2",
"Startup Cost": 0.28,
"Total Cost": 8.30,
"Plan Rows": 1,
"Plan Width": 51,
"Actual Startup Time": 0.763,
"Actual Total Time": 0.763,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["toto_2.addok_id", "toto_2.numero",
"toto_2.geom"],
"Index Cond": "((toto_2.geom IS NOT NULL) AND
(toto_2.geom &&
'0103000020110F000001000000050000004043B7BF21CB114154FB417530BD57414043B7BF21CB1141A20AF2DE57BD57414038B85A98CD1141A20AF2DE57BD57414038B85A98CD114154FB417530BD57414043B7BF21CB114154FB417530BD5741'::geometry))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 0,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Bitmap Heap Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Relation Name": "ban_numero_ban77",
"Schema": "ign",
"Alias": "toto_3",
"Startup Cost": 4.33,
"Total Cost": 20.12,
"Plan Rows": 4,
"Plan Width": 51,
"Actual Startup Time": 2.875,
"Actual Total Time": 2.887,
"Actual Rows": 22,
"Actual Loops": 1,
"Output": ["toto_3.addok_id", "toto_3.numero",
"toto_3.geom"],
"Recheck Cond": "((toto_3.geom IS NOT NULL) AND
(toto_3.geom &&
'0103000020110F000001000000050000004043B7BF21CB114154FB417530BD57414043B7BF21CB1141A20AF2DE57BD57414038B85A98CD1141A20AF2DE57BD57414038B85A98CD114154FB417530BD57414043B7BF21CB114154FB417530BD5741'::geometry))",
"Rows Removed by Index Recheck": 0,
"Exact Heap Blocks": 5,
"Lossy Heap Blocks": 0,
"Shared Hit Blocks": 5,
"Shared Read Blocks": 3,
"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": "Bitmap Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Async Capable": false,
"Index Name": "ban_numero_ban77_geom_idx",
"Startup Cost": 0.00,
"Total Cost": 4.33,
"Plan Rows": 4,
"Plan Width": 0,
"Actual Startup Time": 2.865,
"Actual Total Time": 2.865,
"Actual Rows": 22,
"Actual Loops": 1,
"Index Cond": "((toto_3.geom IS NOT NULL) AND
(toto_3.geom &&
'0103000020110F000001000000050000004043B7BF21CB114154FB417530BD57414043B7BF21CB1141A20AF2DE57BD57414038B85A98CD1141A20AF2DE57BD57414038B85A98CD114154FB417530BD57414043B7BF21CB114154FB417530BD5741'::geometry))",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 3,
"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": "NoMovement",
"Index Name": "ban_numero_ban78_geom_idx",
"Relation Name": "ban_numero_ban78",
"Schema": "ign",
"Alias": "toto_4",
"Startup Cost": 0.28,
"Total Cost": 8.30,
"Plan Rows": 1,
"Plan Width": 51,
"Actual Startup Time": 1.067,
"Actual Total Time": 1.067,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["toto_4.addok_id", "toto_4.numero",
"toto_4.geom"],
"Index Cond": "((toto_4.geom IS NOT NULL) AND
(toto_4.geom &&
'0103000020110F000001000000050000004043B7BF21CB114154FB417530BD57414043B7BF21CB1141A20AF2DE57BD57414038B85A98CD1141A20AF2DE57BD57414038B85A98CD114154FB417530BD57414043B7BF21CB114154FB417530BD5741'::geometry))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 0,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "NoMovement",
"Index Name": "ban_numero_ban91_geom_idx",
"Relation Name": "ban_numero_ban91",
"Schema": "ign",
"Alias": "toto_5",
"Startup Cost": 0.28,
"Total Cost": 8.30,
"Plan Rows": 1,
"Plan Width": 51,
"Actual Startup Time": 0.873,
"Actual Total Time": 0.873,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["toto_5.addok_id", "toto_5.numero",
"toto_5.geom"],
"Index Cond": "((toto_5.geom IS NOT NULL) AND
(toto_5.geom &&
'0103000020110F000001000000050000004043B7BF21CB114154FB417530BD57414043B7BF21CB1141A20AF2DE57BD57414038B85A98CD1141A20AF2DE57BD57414038B85A98CD114154FB417530BD57414043B7BF21CB114154FB417530BD5741'::geometry))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 0,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "NoMovement",
"Index Name": "ban_numero_ban92_geom_idx",
"Relation Name": "ban_numero_ban92",
"Schema": "ign",
"Alias": "toto_6",
"Startup Cost": 0.28,
"Total Cost": 8.30,
"Plan Rows": 1,
"Plan Width": 52,
"Actual Startup Time": 1.066,
"Actual Total Time": 1.066,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["toto_6.addok_id", "toto_6.numero",
"toto_6.geom"],
"Index Cond": "((toto_6.geom IS NOT NULL) AND
(toto_6.geom &&
'0103000020110F000001000000050000004043B7BF21CB114154FB417530BD57414043B7BF21CB1141A20AF2DE57BD57414038B85A98CD1141A20AF2DE57BD57414038B85A98CD114154FB417530BD57414043B7BF21CB114154FB417530BD5741'::geometry))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 0,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "NoMovement",
"Index Name": "ban_numero_ban93_geom_idx",
"Relation Name": "ban_numero_ban93",
"Schema": "ign",
"Alias": "toto_7",
"Startup Cost": 0.28,
"Total Cost": 8.30,
"Plan Rows": 1,
"Plan Width": 52,
"Actual Startup Time": 1.026,
"Actual Total Time": 1.026,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["toto_7.addok_id", "toto_7.numero",
"toto_7.geom"],
"Index Cond": "((toto_7.geom IS NOT NULL) AND
(toto_7.geom &&
'0103000020110F000001000000050000004043B7BF21CB114154FB417530BD57414043B7BF21CB1141A20AF2DE57BD57414038B85A98CD1141A20AF2DE57BD57414038B85A98CD114154FB417530BD57414043B7BF21CB114154FB417530BD5741'::geometry))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 0,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "NoMovement",
"Index Name": "ban_numero_ban94_geom_idx",
"Relation Name": "ban_numero_ban94",
"Schema": "ign",
"Alias": "toto_8",
"Startup Cost": 0.28,
"Total Cost": 8.30,
"Plan Rows": 1,
"Plan Width": 52,
"Actual Startup Time": 1.008,
"Actual Total Time": 1.008,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["toto_8.addok_id", "toto_8.numero",
"toto_8.geom"],
"Index Cond": "((toto_8.geom IS NOT NULL) AND
(toto_8.geom &&
'0103000020110F000001000000050000004043B7BF21CB114154FB417530BD57414043B7BF21CB1141A20AF2DE57BD57414038B85A98CD1141A20AF2DE57BD57414038B85A98CD114154FB417530BD57414043B7BF21CB114154FB417530BD5741'::geometry))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 0,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
},
{
"Node Type": "Index Scan",
"Parent Relationship": "Member",
"Parallel Aware": false,
"Async Capable": false,
"Scan Direction": "NoMovement",
"Index Name": "ban_numero_ban95_geom_idx",
"Relation Name": "ban_numero_ban95",
"Schema": "ign",
"Alias": "toto_9",
"Startup Cost": 0.28,
"Total Cost": 8.30,
"Plan Rows": 1,
"Plan Width": 51,
"Actual Startup Time": 1.054,
"Actual Total Time": 1.054,
"Actual Rows": 0,
"Actual Loops": 1,
"Output": ["toto_9.addok_id", "toto_9.numero",
"toto_9.geom"],
"Index Cond": "((toto_9.geom IS NOT NULL) AND
(toto_9.geom &&
'0103000020110F000001000000050000004043B7BF21CB114154FB417530BD57414043B7BF21CB1141A20AF2DE57BD57414038B85A98CD1141A20AF2DE57BD57414038B85A98CD114154FB417530BD57414043B7BF21CB114154FB417530BD5741'::geometry))",
"Rows Removed by Index Recheck": 0,
"Shared Hit Blocks": 0,
"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,
"WAL Records": 0,
"WAL FPI": 0,
"WAL Bytes": 0
}
]
}
]
}
]
}
]
},
"Settings": {
"effective_cache_size": "5400MB",
"max_parallel_workers": "4",
"work_mem": "12MB"
},
"Query Identifier": 170389169440180166,
"Planning": {
"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
},
"Planning Time": 1.596,
"Triggers": [
],
"Execution Time": 11.137
}
]
```
--
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]
