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

[Lars Aksel Opsahl]

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) (actual time=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 on markslag_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) (actual time=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.211 rows=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 on markslag_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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190925/29585393/attachment.html>