[PostGIS] #5807: Joined table ST_Transform runs on each filter evaluation instead of just once per input row

[PostGIS]

#5807: Joined table ST_Transform runs on each filter evaluation instead of just
once per input row
----------------------+---------------------
  Reporter:  lnicola  |      Owner:  pramsey
      Type:  defect   |     Status:  new
  Priority:  medium   |  Milestone:
 Component:  postgis  |    Version:  3.5.x
Resolution:           |   Keywords:
----------------------+---------------------
Comment (by robe):

 I forgot to add that if you use && it does it with only 12 transform
 calls, no need for cte materialize or offset


 {{{
 set track_functions = 'all';
 -- doing this to prevent parallelism from kicking in
 set max_parallel_workers_per_gather=0;
 select pg_stat_reset();
 explain (analyze, buffers, summary)  select count(*)
 from polygons, (    select tile_id, ST_Transform(geom, 2157) as geom
     from shape_tiles_s2
     where tile_id in ('29UMA', '29UMT', '29UMU', '29UMV', '29UNA',
 '29UNT', '29UNU', '29UNV', '29UPA', '29UPT', '29UPU', '29UPV')
 ) AS tiles
 where polygons.wkb_geometry && tiles.geom;
 }}}

 {{{
  explain (analyze, buffers, summary)  select count(*) from polygons, (
 select tile_id, ST_Transform(geom, 2157) as geom    from shape_tiles_s2
 where tile_id in ('29UMA', '29UMT', '29
 UMU', '29UMV', '29UNA', '29UNT', '29UNU', '29UNV', '29UPA', '29UPT',
 '29UPU', '29UPV')) AS tiles where polygons.wkb_geometry && tiles.geom;
 QUERY PLAN
 -------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Finalize Aggregate  (cost=7354.84..7354.85 rows=1 width=8) (actual
 time=9804.104..9804.151 rows=1 loops=1)
    Buffers: shared hit=571244 read=748046
    ->  Gather  (cost=7354.73..7354.84 rows=1 width=8) (actual
 time=9804.092..9804.145 rows=2 loops=1)
          Workers Planned: 1
          Workers Launched: 1
          Buffers: shared hit=571244 read=748046
          ->  Partial Aggregate  (cost=6354.73..6354.74 rows=1 width=8)
 (actual time=4907.346..4907.347 rows=1 loops=2)
                Buffers: shared hit=571244 read=748046
                ->  Nested Loop  (cost=125.32..6330.59 rows=9658 width=0)
 (actual time=87.055..4798.034 rows=793815 loops=2)
                      Buffers: shared hit=571244 read=748046
                      ->  Parallel Seq Scan on shape_tiles_s2
 (cost=0.03..1591.20 rows=7 width=120) (actual time=11.077..11.233 rows=6
 loops=2)
                            Filter: (tile_id = ANY
 ('{29UMA,29UMT,29UMU,29UMV,29UNA,29UNT,29UNU,29UNV,29UPA,29UPT,29UPU,29UPV}'::bpchar[]))
                            Rows Removed by Filter: 28337
                            Buffers: shared hit=1 read=1090
                      ->  Index Scan using ix_polygons_geom on polygons
 (cost=125.29..675.69 rows=137 width=498) (actual time=0.381..761.839
 rows=132302 loops=12)
                            Index Cond: (wkb_geometry &&
 st_transform(shape_tiles_s2.geom, 2157))
                            Buffers: shared hit=571114 read=746924
  Planning:
    Buffers: shared hit=307 read=41
  Planning Time: 4.872 ms
  Execution Time: 9804.564 ms
 (21 rows)

 }}}

 {{{

  select funcname, calls, total_time  from pg_stat_user_functions WHERE
 funcname IN('st_intersects', 'st_transform');

 }}}

 {{{
   funcname    | calls | total_time
 ---------------+-------+------------
  st_transform  |    12 |    150.355
  st_intersects |     0 |          0
 (2 rows)
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5807#comment:6>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.