[postgis-users] Why is && operator causing Parallel to kick in ?

[Paul Ramsey]

For fairly complex reasons having to do with the ST_Intersects() function being an inlined SQL function. 

https://carto.com/blog/postgres-parallel/ <https://carto.com/blog/postgres-parallel/>

The issues are fixed in Pg12 / PostGIS 3, but older combinations will be fiddly to parallelize, as you’ve found.

P.

> On Sep 25, 2019, at 7:14 AM, Lars Aksel Opsahl <Lars.Opsahl at nibio.no> wrote:
> 
> Hi
> 
> In the  first sql below we just use "ST_Intersects(r1.geo,r2.geo)", in the second SQL we add  "r1.geo && r2.geo" 
> 
> The second sql executes 3 times faster probably because of parallel seems to kick in. (if we adjust parallell settings we can get the second SQL to run 6. times faster)
> 
> Why is this happening when I just add "r1.geo && r2.geo" ?
> 
> I have ran ANALYZE on the table  before running the tests and there are indexes on geo and gid.
> Indexes:
>     "markslag_myrikilden_temp_gid_idx" UNIQUE, btree (gid)
>     "geoidx__markslag_myrikilden_temp___gist" gist (geo)
> 
> We run on
> PostgreSQL 11.5 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
> POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="110" GEOS="3.8.0dev-CAPI-1.12.0 " SFCGAL="1.3.7" PROJ="Rel. 6.2.0, September 1st, 2019" GDAL="GDAL 3.1.0dev-7a9a0f4-dirty, released 2019/99/99" LIBXML="2.9.1" TOPOLOGY RASTER
> 1. SQL :
> EXPLAIN ANALYZE
> select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext, ST_Collect(geo) as geo 
> from (
> select r1.gid, r1.atil, r1.myr, r1.myrtype,r1.myromdanning,r1.myrtypetext,r1.myromdanningtext,r1.geo, true doUnion
> from sde_markslag.markslag_myrikilden_temp r1,  sde_markslag.markslag_myrikilden_temp r2
> where ST_Intersects(r1.geo,r2.geo) and r1.gid != r2.gid
> ) as r
> group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext;
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  HashAggregate  (cost=10519178.25..10519180.65 rows=192 width=46) (actualtime=185774.300..187207.628 rows=163 loops=1)
>    Group Key: r1.atil, r1.myr, r1.myrtype, r1.myromdanning, r1.myrtypetext, r1.myromdanningtext
>    ->  Nested Loop  (cost=0.29..9169289.37 rows=77136507 width=1612) (actual time=0.680..183087.904 rows=721668 loops=1)
>          ->  Seq Scan on markslag_myrikilden_temp r1  (cost=0.00..59170.91 rows=567241 width=1616) (actual time=0.021..640.233 rows=567241 loops=1)
>          ->  Index Scan using geoidx__markslag_myrikilden_temp___gist onmarkslag_myrikilden_temp r2  (cost=0.29..15.87 rows=19 width=1602) (actual time=0.257..0.317 rows=1 loops=567241)
>                Index Cond: (r1.geo && geo)
>                Filter: ((r1.gid <> gid) AND _st_intersects(r1.geo, geo))
>                Rows Removed by Filter: 2
>  Planning Time: 4.800 ms
>  Execution Time: 187214.730 ms
> (10 rows)
> Time: 187231.267 ms (03:07.231)
> 
> 
> 
> 2. SQL :
> EXPLAIN ANALYZE
> select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext, ST_Collect(geo) as geo 
> from (
> select r1.gid, r1.atil, r1.myr, r1.myrtype,r1.myromdanning,r1.myrtypetext,r1.myromdanningtext,r1.geo, true doUnion
> from sde_markslag.markslag_myrikilden_temp r1,  sde_markslag.markslag_myrikilden_temp r2
> where r1.geo && r2.geo and ST_Intersects(r1.geo,r2.geo) and r1.gid != r2.gid
> ) as r
> group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext;
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  HashAggregate  (cost=243692.31..243694.71 rows=192 width=46) (actualtime=62439.948..63863.421 rows=163 loops=1)
>    Group Key: r1.atil, r1.myr, r1.myrtype, r1.myromdanning, r1.myrtypetext, r1.myromdanningtext
>    ->  Gather  (cost=1000.28..242721.48 rows=55476 width=1612) (actual time=1.519..59526.211rows=721668 loops=1)
>          Workers Planned: 2
>          Workers Launched: 2
>          ->  Nested Loop  (cost=0.29..236173.88 rows=23115 width=1612) (actual time=0.583..59945.673 rows=240556 loops=3)
>                ->  Parallel Seq Scan on markslag_myrikilden_temp r1  (cost=0.00..55862.00 rows=236350 width=1616) (actual time=0.013..260.552 rows=189080 loops=3)
>                ->  Index Scan using geoidx__markslag_myrikilden_temp___gist onmarkslag_myrikilden_temp r2  (cost=0.29..0.75 rows=1 width=1602) (actual time=0.252..0.311 rows=1 loops=567241)
>                      Index Cond: ((r1.geo && geo) AND (r1.geo && geo))
>                      Filter: ((r1.gid <> gid) AND _st_intersects(r1.geo, geo))
>                      Rows Removed by Filter: 2
>  Planning Time: 7.791 ms
>  Execution Time: 63871.265 ms
> (13 rows)
> Time: 63886.211 ms (01:03.886)
> 
> 
> Lars
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/postgis-users <https://lists.osgeo.org/mailman/listinfo/postgis-users>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190925/bf4c7314/attachment.html>