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

[Johannes Kröger (WhereGroup)]

Workaround, that works well in my specific data and use case:

My input layer only covers small and local parts of the full coverage of 
the big PostGIS layer. Because of this I could switch the overlay input 
for the intersection from the PostGIS layer to a query layer that 
filters the PostGIS layer against the convex hull of the input layer 
with ST_Intersects. Now only a small fraction of the features will be 
fetched and the complexity depends mostly on the spatial distribution of 
the input data, not the overlay layer.

Works very well so far and vastly faster.

I still feel like I am overlooking something very basic though :o)

Cheers, Hannes

On 4/9/25 13:26, Johannes Kröger (WhereGroup) via QGIS-User wrote:
> 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
-------------------------------