<div dir="ltr">Hi Nicolas,<div><br></div><div>I've seen that behavior for tables that have same, rather sparse but worldwide coverage for points and polygons.</div><div>In my case it was due to statistic histogram having not enough resolution to properly estimate selectivity.</div><div>When you add more && to request, you basically replace selectivity by selectivity^2 in planner.</div><div><br></div><div>Another way to change this may be </div><div><br></div><div>ALTER TABLE tmp.tp1 SET STATISTICS 10000;<br>ALTER TABLE ref.batimet SET STATISTICS 10000;<br></div><div>ANALYZE tmp.tp1;</div><div>ANALYZE ref.batimet;</div><div><br></div><div>By default table is estimated by histogram of 100 cells, basically grid 10x10. 10000 changes it to 100x100, allowing </div></div><br><div class="gmail_quote"><div dir="ltr">чт, 7 дек. 2017 г. в 11:24, Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com">nicolas.ribot@gmail.com</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Same with a logged table.<div><br></div><div>It seems the && condition have to be duplicated for the right plan to be chosen:</div><div><br></div><div></div></div><div dir="ltr"><div><div>EXPLAIN SELECT <a href="http://p.id" target="_blank">p.id</a>, <a href="http://b.id" target="_blank">b.id</a></div><div>FROM tmp.tp1 p join ref.batiment b</div></div></div><div dir="ltr"><div><div>    on b.geom && p.geom and p.geom && b.geom and _st_intersects(b.geom, p.geom);</div></div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On 6 December 2017 at 22:36, 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">If you change the small table into a normal table (not unlogged) does<br>
the problem go away? Could be an interesting side effect of<br>
"unlogged"?<br>
<div class="m_-2440168042031293873HOEnZb"><div class="m_-2440168042031293873h5"><br>
On Wed, Dec 6, 2017 at 1:14 PM, Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>> wrote:<br>
> Yes, tables are analyzed.<br>
><br>
> I tried reverting argument, same plan generated:<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(p.geom, b.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: (geom && b.geom)<br>
>         Filter: _st_intersects(geom, b.geom)<br>
><br>
> Nico<br>
><br>
> On 6 December 2017 at 19:14, Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>> wrote:<br>
>><br>
>> 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>
>><br>
>> On Wed, Dec 6, 2017 at 9:57 AM, Nicolas Ribot <<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>><br>
>> 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<br>
>> > records<br>
>> > (UNLOGGED table)<br>
>> ><br>
>> > When performing a spatial join, the planner chooses the smallest table<br>
>> > 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<br>
>> > (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,<br>
>> > 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<br>
>> > upgrade)<br>
>> > RASTER<br>
>> ><br>
>> > Thanks for any hint.<br>
>> ><br>
>> > Nicolas<br>
>> ><br>
>> > _______________________________________________<br>
>> > postgis-users mailing list<br>
>> > <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">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/mailman/listinfo/postgis-users</a><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">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/mailman/listinfo/postgis-users</a><br>
><br>
><br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">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/mailman/listinfo/postgis-users</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">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/mailman/listinfo/postgis-users</a></div></div></blockquote></div><br></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">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/mailman/listinfo/postgis-users</a></blockquote></div>