Slow scan of node_containing_face_idx index with index condition selecting 0 rows out of it
On this system
POSTGIS="3.4.0 0874ea3" [EXTENSION] PGSQL="120" GEOS="3.12.1-CAPI-1.18.1" (compiled against GEOS 3.10.2) SFCGAL="1.3.7" PROJ="8.2.0 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/tmp/proj DATABASE_PATH=/usr/share/proj/proj.db" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY
PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
FROM pg_stat_statements
total_min | avg_ms | calls |
575.4263588030993 | 1108.0807987735484 | 31158 | SELECT ARRAY(SELECT topology.TopoGeo_addLinestring('gronn_test_03_test_topo','0102000020A21000000C000000840E4958ABA12540F0CF8D3711F24D40BEDC4AAEADA1254038F24E3C11F24D40E840F>
Explain
explain analyze SELECT node_id,geom FROM "gronn_test_03_test_topo".node WHERE containing_face IN (0) AND geom && '0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40'::geometry
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Index Scan using node_containing_face_idx on node (cost=0.56..2.58 rows=1 width=36) (actual time=2119.671..2119.671 rows=0 loops=1)
Index Cond: (containing_face = 0)
Filter: (geom && '0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40'::geometry)
Planning Time: 3.400 ms
Execution Time: 2119.701 ms
(5 rows)
After a vacuum full and analyze
explain analyze SELECT node_id,geom FROM "gronn_test_03_test_topo".node WHERE containing_face = 0
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Index Scan using node_containing_face_idx on node (cost=0.56..2.58 rows=1 width=36) (actual time=1529.789..1529.789 rows=0 loops=1)
Index Cond: (containing_face = 0)
Planning Time: 0.572 ms
Execution Time: 1529.808 ms
(4 rows)
Table info size and indexes
\d+ "gronn_test_03_test_topo".node
Unlogged table "gronn_test_03_test_topo.node"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+----------------------+-----------+----------+---------------------------------------------------------------+---------+--------------+-------------
node_id | integer | | not null | nextval('gronn_test_03_test_topo.node_node_id_seq'::regclass) | plain | |
containing_face | integer | | | | plain | |
geom | geometry(Point,4258) | | | | main | |
Indexes:
"node_primary_key" PRIMARY KEY, btree (node_id)
"node_containing_face_idx" btree (containing_face)
"node_gist" gist (geom)
Foreign-key constraints:
"face_exists" FOREIGN KEY (containing_face) REFERENCES gronn_test_03_test_topo.face(face_id)
Referenced by:
TABLE "gronn_test_03_test_topo.edge_data" CONSTRAINT "end_node_exists" FOREIGN KEY (end_node) REFERENCES gronn_test_03_test_topo.node(node_id)
TABLE "gronn_test_03_test_topo.edge_data" CONSTRAINT "start_node_exists" FOREIGN KEY (start_node) REFERENCES gronn_test_03_test_topo.node(node_id)
Access method: heap
SELECT count(*) FROM "gronn_test_03_test_topo".node;
count
----------
15694822
(1 row)
SELECT count(*) FROM "gronn_test_03_test_topo".node WHERE containing_face IS NULL;
count
----------
15694822
(1 row)
SELECT count(*) FROM "gronn_test_03_test_topo".node WHERE geom IS NULL;
count
-------
0
(1 row)
When I try to recreate the problem in another a another table I am not able to do it
CREATE table test_null ( node_id serial primary key, containing_face int, geom geometry);
CREATE INDEX ON test_null(containing_face);
do
$$
begin
for i in 1..15694822 loop
raise notice 'i: %', i;
INSERT INTO test_null(geom) VALUES('0102000020A21000000200000025DDA6B95DC62540F4E713991CE84D4017EE7636A3C625404E468D0D23E84D40');
end loop;
end;
$$;
ANALYZE test_null;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using test_null_containing_face_idx on test_null (cost=0.43..2.45 rows=1 width=4) (actual time=0.312..0.312 rows=0 loops=1)
Index Cond: (containing_face = 0)
Planning Time: 0.678 ms
Execution Time: 0.334 ms
(4 rows)
Edited by Lars Aksel Opsahl