[Qgis-user] Running intersection with PostGIS overlay layer without local recreation of index?

[Johannes Kröger (WhereGroup)]

Howdy!

I have a local layer with a few polygon features and a remote PostGIS 
layer with millions of polygon features.

When I run native:intersection with the small local layer as input and 
the big remote layer as overlay, QGIS will fetch all features from the 
big remote layer and create a local spatial index. This uses lots of 
bandwidth and takes a long time.

I would have expected QGIS to handle PostGIS layers in a way that their 
existing indexes are utilised.

Searching for the processing log message "Creating spatial index", it 
seems like 
https://github.com/qgis/QGIS/blob/d238f1132bbbf8c479356b9267cff7ee137e5be9/src/analysis/processing/qgsoverlayutils.cpp#L233 
is responsible for that. If I read the code correctly, a QgsSpatialIndex 
will *always* be created for the overlay layer.

Is there a way to do intersections where existing (PostGIS) indexes are 
used?

Cheers, Hannes

PS: A workaround solution could be using some custom Python code around 
qgis:postgisexecuteandloadsql I guess.

-- 
Johannes Kröger / GIS-Entwickler/-Berater

****************************************
WhereGroup Shorts 2025 - am 15. Mai
Im Fokus: PostgreSQL & PostGIS
Online als Zoom-Meeting
https://wheregroup-shorts.de
****************************************

WhereGroup GmbH
c/o KK03 GmbH
Lange Reihe 29
20099 Hamburg
Germany

Tel: +49 (0)228 / 90 90 38 - 36
Fax: +49 (0)228 / 90 90 38 - 11

johannes.kroeger at wheregroup.com
www.wheregroup.com

Geschäftsführer:
Olaf Knopp, Peter Stamm
Amtsgericht Bonn, HRB 9885
-------------------------------