<div dir="ltr">Yes, tables are analyzed.<div><br></div><div>I tried reverting argument, same plan generated:</div><div><br></div><div><div><font face="monospace, monospace">EXPLAIN SELECT <a href="http://p.id">p.id</a>, <a href="http://b.id">b.id</a></font></div><div><font face="monospace, monospace">FROM tmp.tp1 p join ref.batiment b on st_intersects(p.geom, b.geom);</font></div></div><div><font face="monospace, monospace"><br></font></div><div><div><font face="monospace, monospace">QUERY PLAN</font></div><div><font face="monospace, monospace">Nested Loop (cost=0.15..21819347.50 rows=50019 width=35)</font></div><div><font face="monospace, monospace"> -> Seq Scan on batiment b (cost=0.00..2238826.04 rows=45794904 width=193)</font></div><div><font face="monospace, monospace"> -> Index Scan using tp1_geom_gist on tp1 p (cost=0.15..0.42 rows=1 width=276)</font></div><div><font face="monospace, monospace"> Index Cond: (geom && b.geom)</font></div><div><font face="monospace, monospace"> Filter: _st_intersects(geom, b.geom)</font></div></div><div><font face="monospace, monospace"><br></font></div><div>Nico</div></div><div class="gmail_extra"><br><div class="gmail_quote">On 6 December 2017 at 19:14, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Just invert the arguments in your call to ST_Intersects().<br>
I don't think we have any way to tell the planner anything about which<br>
side of the condition should drive the nested loop join. And yet it<br>
seems to get the "right" one most of the time. I assume you've already<br>
run 'ANALYZE' and the stats are all up-to-date.<br>
P.<br>
<div><div class="h5"><br>
On Wed, Dec 6, 2017 at 9:57 AM, Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com">nicolas.ribot@gmail.com</a>> wrote:<br>
> Hi,<br>
><br>
> I have 2 tables, indexed and analyzed.<br>
> One big (batiment) with 45M records, one small (tmp.tp1), with 83k records<br>
> (UNLOGGED table)<br>
><br>
> When performing a spatial join, the planner chooses the smallest table for<br>
> seq scan only if I add a && condition with st_intersects:<br>
><br>
> EXPLAIN SELECT <a href="http://p.id" rel="noreferrer" target="_blank">p.id</a>, <a href="http://b.id" rel="noreferrer" target="_blank">b.id</a><br>
> FROM tmp.tp1 p join ref.batiment b on st_intersects(b.geom, p.geom);<br>
><br>
> QUERY PLAN<br>
> Nested Loop (cost=0.15..21819347.50 rows=50019 width=35)<br>
> -> Seq Scan on batiment b (cost=0.00..2238826.04 rows=45794904<br>
> width=193)<br>
> -> Index Scan using tp1_geom_gist on tp1 p (cost=0.15..0.42 rows=1<br>
> width=276)<br>
> Index Cond: (b.geom && geom)<br>
> Filter: _st_intersects(b.geom, geom)<br>
><br>
> vs<br>
><br>
> EXPLAIN SELECT <a href="http://p.id" rel="noreferrer" target="_blank">p.id</a>, <a href="http://b.id" rel="noreferrer" target="_blank">b.id</a><br>
> FROM tmp.tp1 p join ref.batiment b on p.geom && b.geom and<br>
> st_intersects(b.geom, p.geom);<br>
><br>
> QUERY PLAN<br>
> Nested Loop (cost=0.42..118443.25 rows=1 width=35)<br>
> -> Seq Scan on tp1 p (cost=0.00..842.75 rows=13675 width=276)<br>
> -> Index Scan using batiment_geom_gist on batiment b (cost=0.42..8.59<br>
> rows=1 width=193)<br>
> Index Cond: ((p.geom && geom) AND (geom && p.geom))<br>
> Filter: _st_intersects(geom, p.geom)<br>
><br>
> PG:<br>
> PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu<br>
> 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit<br>
><br>
> Postgis:<br>
> POSTGIS="2.3.3 r15473" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.2, 08<br>
> September 2015" GDAL="GDAL 1.11.3, released 2015/09/16" LIBXML="2.9.3"<br>
> LIBJSON="0.11.99" TOPOLOGY (topology procs from "2.3.2 r15302" need upgrade)<br>
> RASTER<br>
><br>
> Thanks for any hint.<br>
><br>
> Nicolas<br>
><br>
</div></div>> ______________________________<wbr>_________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br>
______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a></blockquote></div><br></div>