[postgis-users] Bad plan without && operator

[Nicolas Ribot]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20171206/1ce58582/attachment.html>