[postgis-users] Bad plan without && operator

[Paul Ramsey]

Just invert the arguments in your call to ST_Intersects().
I don't think we have any way to tell the planner anything about which
side of the condition should drive the nested loop join. And yet it
seems to get the "right" one most of the time. I assume you've already
run 'ANALYZE' and the stats are all up-to-date.
P.

On Wed, Dec 6, 2017 at 9:57 AM, Nicolas Ribot <nicolas.ribot at gmail.com> wrote:
> Hi,
>
> I have 2 tables, indexed and analyzed.
> One big (batiment) with 45M records, one small (tmp.tp1), with 83k records
> (UNLOGGED table)
>
> When performing a spatial join, the planner chooses the smallest table for
> seq scan only if I add a && condition with st_intersects:
>
> EXPLAIN SELECT p.id, b.id
> FROM tmp.tp1 p join ref.batiment b on st_intersects(b.geom, p.geom);
>
> QUERY PLAN
> Nested Loop  (cost=0.15..21819347.50 rows=50019 width=35)
>   ->  Seq Scan on batiment b  (cost=0.00..2238826.04 rows=45794904
> width=193)
>   ->  Index Scan using tp1_geom_gist on tp1 p  (cost=0.15..0.42 rows=1
> width=276)
>         Index Cond: (b.geom && geom)
>         Filter: _st_intersects(b.geom, geom)
>
> vs
>
> EXPLAIN SELECT p.id, b.id
> FROM tmp.tp1 p join ref.batiment b on p.geom && b.geom and
> st_intersects(b.geom, p.geom);
>
> QUERY PLAN
> Nested Loop  (cost=0.42..118443.25 rows=1 width=35)
>   ->  Seq Scan on tp1 p  (cost=0.00..842.75 rows=13675 width=276)
>   ->  Index Scan using batiment_geom_gist on batiment b  (cost=0.42..8.59
> rows=1 width=193)
>         Index Cond: ((p.geom && geom) AND (geom && p.geom))
>         Filter: _st_intersects(geom, p.geom)
>
> PG:
> PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
>
> Postgis:
> POSTGIS="2.3.3 r15473" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.2, 08
> September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3"
> LIBJSON="0.11.99" TOPOLOGY (topology procs from "2.3.2 r15302" need upgrade)
> RASTER
>
> Thanks for any hint.
>
> Nicolas
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users