[PostGIS] #5707: slow cb_getEdgeByFace seems like missing value for bbox parameter
PostGIS
trac at osgeo.org
Mon Apr 1 21:03:19 PDT 2024
#5707: slow cb_getEdgeByFace seems like missing value for bbox parameter
--------------------------------+---------------------------
Reporter: Lars Aksel Opsahl | Owner: strk
Type: enhancement | Status: new
Priority: medium | Milestone: PostGIS 3.4.3
Component: topology | Version: 3.4.x
Resolution: | Keywords:
--------------------------------+---------------------------
Comment (by Lars Aksel Opsahl):
Here are the table def
\d "tmp_dyrkbarjord_04_t3_d_005".edge_data;
{{{
Unlogged table
"tmp_dyrkbarjord_04_t3_d_005.edge_data"
Column | Type | Collation | Nullable |
Default
---------------------+---------------------------+-----------+----------+------------------------------------------------------------------------
edge_id | integer | | not null |
nextval('tmp_dyrkbarjord_04_t3_d_005.edge_data_edge_id_seq'::regclass)
start_node | integer | | not null |
end_node | integer | | not null |
next_left_edge | integer | | not null |
abs_next_left_edge | integer | | not null |
next_right_edge | integer | | not null |
abs_next_right_edge | integer | | not null |
left_face | integer | | not null |
right_face | integer | | not null |
geom | geometry(LineString,4258) | | not null |
Indexes:
"edge_data_pkey" PRIMARY KEY, btree (edge_id)
"edge_data_abs_next_left_edge_idx" btree (abs_next_left_edge)
"edge_data_abs_next_right_edge_idx" btree (abs_next_right_edge)
"edge_end_node_idx" btree (end_node)
"edge_gist" gist (geom)
"edge_left_face_idx" btree (left_face)
"edge_right_face_idx" btree (right_face)
"edge_start_node_idx" btree (start_node)
Foreign-key constraints:
"end_node_exists" FOREIGN KEY (end_node) REFERENCES
tmp_dyrkbarjord_04_t3_d_005.node(node_id)
"left_face_exists" FOREIGN KEY (left_face) REFERENCES
tmp_dyrkbarjord_04_t3_d_005.face(face_id)
"next_left_edge_exists" FOREIGN KEY (abs_next_left_edge) REFERENCES
tmp_dyrkbarjord_04_t3_d_005.edge_data(edge_id) DEFERRABLE INITIALLY
DEFERRED
"next_right_edge_exists" FOREIGN KEY (abs_next_right_edge) REFERENCES
tmp_dyrkbarjord_04_t3_d_005.edge_data(edge_id) DEFERRABLE INITIALLY
DEFERRED
"right_face_exists" FOREIGN KEY (right_face) REFERENCES
tmp_dyrkbarjord_04_t3_d_005.face(face_id)
"start_node_exists" FOREIGN KEY (start_node) REFERENCES
tmp_dyrkbarjord_04_t3_d_005.node(node_id)
Referenced by:
TABLE "tmp_dyrkbarjord_04_t3_d_005.edge_data" CONSTRAINT
"next_left_edge_exists" FOREIGN KEY (abs_next_left_edge) REFERENCES
tmp_dyrkbarjord_04_t3_d_005.edge_data(edge_id) DEFERRABLE INITIALLY
DEFERRED
TABLE "tmp_dyrkbarjord_04_t3_d_005.edge_data" CONSTRAINT
"next_right_edge_exists" FOREIGN KEY (abs_next_right_edge) REFERENCES
tmp_dyrkbarjord_04_t3_d_005.edge_data(edge_id) DEFERRABLE INITIALLY
DEFERRED
}}}
and yes it does not use the index with simple query either
{{{
explain analyze SELECT edge_id,left_face,right_face,geom
FROM "tmp_dyrkbarjord_04_t3_d_005".edge_data WHERE ( left_face = 0 ) OR
(right_face = 0 )
;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on edge_data (cost=0.00..30625.22 rows=94479 width=162) (actual
time=0.017..126.493 rows=78609 loops=1)
Filter: ((left_face = 0) OR (right_face = 0))
Rows Removed by Filter: 243439
Planning Time: 0.161 ms
Execution Time: 128.823 ms
(5 rows)
}}}
Just to sure I rerun a new vacuum
{{{
VACUUM (analyze, verbose) "tmp_dyrkbarjord_04_t3_d_005".edge_data;
INFO: 00000: vacuuming "tmp_dyrkbarjord_04_t3_d_005.edge_data"
LOCATION: lazy_scan_heap, vacuumlazy.c:521
INFO: 00000: scanned index "edge_data_pkey" to remove 21147 row versions
DETAIL: CPU: user: 0.04 s, system: 0.01 s, elapsed: 0.05 s
LOCATION: lazy_vacuum_index, vacuumlazy.c:1748
INFO: 00000: scanned index "edge_data_abs_next_left_edge_idx" to remove
21147 row versions
DETAIL: CPU: user: 0.02 s, system: 0.02 s, elapsed: 0.05 s
LOCATION: lazy_vacuum_index, vacuumlazy.c:1748
INFO: 00000: scanned index "edge_data_abs_next_right_edge_idx" to remove
21147 row versions
DETAIL: CPU: user: 0.02 s, system: 0.02 s, elapsed: 0.04 s
LOCATION: lazy_vacuum_index, vacuumlazy.c:1748
INFO: 00000: scanned index "edge_gist" to remove 21147 row versions
DETAIL: CPU: user: 0.03 s, system: 0.03 s, elapsed: 0.06 s
LOCATION: lazy_vacuum_index, vacuumlazy.c:1748
INFO: 00000: scanned index "edge_left_face_idx" to remove 21147 row
versions
DETAIL: CPU: user: 0.03 s, system: 0.01 s, elapsed: 0.04 s
LOCATION: lazy_vacuum_index, vacuumlazy.c:1748
INFO: 00000: scanned index "edge_right_face_idx" to remove 21147 row
versions
DETAIL: CPU: user: 0.02 s, system: 0.02 s, elapsed: 0.04 s
LOCATION: lazy_vacuum_index, vacuumlazy.c:1748
INFO: 00000: scanned index "edge_start_node_idx" to remove 21147 row
versions
DETAIL: CPU: user: 0.02 s, system: 0.01 s, elapsed: 0.04 s
LOCATION: lazy_vacuum_index, vacuumlazy.c:1748
INFO: 00000: scanned index "edge_end_node_idx" to remove 21147 row
versions
DETAIL: CPU: user: 0.01 s, system: 0.03 s, elapsed: 0.04 s
LOCATION: lazy_vacuum_index, vacuumlazy.c:1748
INFO: 00000: "edge_data": removed 21147 row versions in 860 pages
DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION: lazy_vacuum_heap, vacuumlazy.c:1557
INFO: 00000: index "edge_data_pkey" now contains 322048 row versions in
8461 pages
DETAIL: 21147 index row versions were removed.
177 index pages have been deleted, 102 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
LOCATION: lazy_cleanup_index, vacuumlazy.c:1801
INFO: 00000: index "edge_data_abs_next_left_edge_idx" now contains 322048
row versions in 8488 pages
DETAIL: 21147 index row versions were removed.
175 index pages have been deleted, 100 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
LOCATION: lazy_cleanup_index, vacuumlazy.c:1801
INFO: 00000: index "edge_data_abs_next_right_edge_idx" now contains
322048 row versions in 8429 pages
DETAIL: 21147 index row versions were removed.
170 index pages have been deleted, 94 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
LOCATION: lazy_cleanup_index, vacuumlazy.c:1801
INFO: 00000: index "edge_gist" now contains 322048 row versions in 12886
pages
DETAIL: 21147 index row versions were removed.
1298 index pages have been deleted, 1202 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
LOCATION: lazy_cleanup_index, vacuumlazy.c:1801
INFO: 00000: index "edge_left_face_idx" now contains 322048 row versions
in 8397 pages
DETAIL: 21147 index row versions were removed.
1035 index pages have been deleted, 1015 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
LOCATION: lazy_cleanup_index, vacuumlazy.c:1801
INFO: 00000: index "edge_right_face_idx" now contains 322048 row versions
in 8483 pages
DETAIL: 21147 index row versions were removed.
692 index pages have been deleted, 674 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
LOCATION: lazy_cleanup_index, vacuumlazy.c:1801
INFO: 00000: index "edge_start_node_idx" now contains 322048 row versions
in 8452 pages
DETAIL: 21147 index row versions were removed.
178 index pages have been deleted, 102 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
LOCATION: lazy_cleanup_index, vacuumlazy.c:1801
INFO: 00000: index "edge_end_node_idx" now contains 322048 row versions
in 8472 pages
DETAIL: 21147 index row versions were removed.
167 index pages have been deleted, 93 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
LOCATION: lazy_cleanup_index, vacuumlazy.c:1801
INFO: 00000: "edge_data": found 207 removable, 8790 nonremovable row
versions in 871 out of 51589 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 533943927
There were 7489 unused item identifiers.
Skipped 0 pages due to buffer pins, 20492 frozen pages.
0 pages are entirely empty.
CPU: user: 0.21 s, system: 0.19 s, elapsed: 0.41 s.
LOCATION: lazy_scan_heap, vacuumlazy.c:1488
INFO: 00000: "edge_data": truncated 51589 to 50631 pages
DETAIL: CPU: user: 0.10 s, system: 0.01 s, elapsed: 0.11 s
LOCATION: lazy_truncate_heap, vacuumlazy.c:1969
INFO: 00000: vacuuming "pg_toast.pg_toast_2235142925"
LOCATION: lazy_scan_heap, vacuumlazy.c:521
INFO: 00000: index "pg_toast_2235142925_index" now contains 0 row
versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
LOCATION: lazy_cleanup_index, vacuumlazy.c:1801
INFO: 00000: "pg_toast_2235142925": found 0 removable, 0 nonremovable row
versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 533943928
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
LOCATION: lazy_scan_heap, vacuumlazy.c:1488
INFO: 00000: analyzing "tmp_dyrkbarjord_04_t3_d_005.edge_data"
LOCATION: do_analyze_rel, analyze.c:327
INFO: 00000: "edge_data": scanned 50631 of 50631 pages, containing 322048
live rows and 0 dead rows; 300000 rows in sample, 322048 estimated total
rows
LOCATION: acquire_sample_rows, analyze.c:1132
}}}
and rerun this simple sql still with seq scan
{{{
explain analyze SELECT edge_id,left_face,right_face,geom FROM
"tmp_dyrkbarjord_04_t3_d_005".edge_data WHERE ( left_face = 0 ) OR
(right_face = 0 )
;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on edge_data (cost=0.00..30146.22 rows=94523 width=162) (actual
time=0.017..120.990 rows=78609 loops=1)
Filter: ((left_face = 0) OR (right_face = 0))
Rows Removed by Filter: 243439
Planning Time: 0.775 ms
Execution Time: 123.262 ms
(5 rows)
}}}
Even if rewrite the sql so uses the indexes, we never see anything close
the the performance by using the bbox parameter
{{{
explain analyze SELECT edge_id,left_face,right_face,geom FROM
"tmp_dyrkbarjord_04_t3_d_005".edge_data WHERE left_face = 0 UNION SELECT
edge_id,left_face,right_face,geom FROM
"tmp_dyrkbarjord_04_t3_d_005".edge_data WHERE right_face = 0 ;
;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=46293.27..47311.23 rows=101796 width=44) (actual
time=151.638..164.441 rows=78609 loops=1)
Group Key: edge_data.edge_id, edge_data.left_face,
edge_data.right_face, edge_data.geom
-> Append (cost=1189.75..45275.31 rows=101796 width=44) (actual
time=14.022..91.000 rows=101787 loops=1)
-> Bitmap Heap Scan on edge_data (cost=1189.75..18353.36
rows=35139 width=162) (actual time=14.021..39.493 rows=35127 loops=1)
Recheck Cond: (left_face = 0)
Heap Blocks: exact=8953
-> Bitmap Index Scan on edge_left_face_idx
(cost=0.00..1180.96 rows=35139 width=0) (actual time=10.924..10.925
rows=35127 loops=1)
Index Cond: (left_face = 0)
-> Bitmap Heap Scan on edge_data edge_data_1
(cost=2273.01..25395.02 rows=66657 width=162) (actual time=11.529..47.015
rows=66660 loops=1)
Recheck Cond: (right_face = 0)
Heap Blocks: exact=15257
-> Bitmap Index Scan on edge_right_face_idx
(cost=0.00..2256.35 rows=66657 width=0) (actual time=9.506..9.506
rows=66660 loops=1)
Index Cond: (right_face = 0)
Planning Time: 0.261 ms
Execution Time: 168.322 ms
(15 rows)
}}}
--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5707#comment:1>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.
More information about the postgis-tickets
mailing list