[postgis-users] Bad plan without && operator

[Nicolas Ribot]

Yes, tables are analyzed.

I tried reverting argument, same plan generated:

EXPLAIN SELECT p.id, b.id
FROM tmp.tp1 p join ref.batiment b on st_intersects(p.geom, b.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: (geom && b.geom)
        Filter: _st_intersects(geom, b.geom)

Nico

On 6 December 2017 at 19:14, Paul Ramsey <pramsey at cleverelephant.ca> wrote:

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20171206/9e5f7457/attachment.html>