Re: effect on planner of turning a subquery to a table, sql function returning table

[Thierry Henrio <thierry.henrio@xxxxxxxxx>]

Thanks David!

Here are the plans with (ANALYZE, BUFFERS), and track_io_timing ON:

(A)

 GroupAggregate  (cost=401037.82..503755.82 rows=1467400 width=124) (actual time=416.851..426.534 rows=4670 loops=1)
   Group Key: t.device_id, t.date, t.start_time, t.end_time, (timerange(((t_1.value ->> 0))::time without time zone, ((t_1.value ->> 1))::time without time zone))
   Buffers: shared hit=3067
   ->  Sort  (cost=401037.82..404706.32 rows=1467400 width=96) (actual time=416.812..417.855 rows=22908 loops=1)
         Sort Key: t.device_id, t.date, t.start_time, t.end_time, (timerange(((t_1.value ->> 0))::time without time zone, ((t_1.value ->> 1))::time without time zone)), t.rank
         Sort Method: quicksort  Memory: 3229kB
         Buffers: shared hit=3067
         ->  Nested Loop  (cost=2.99..100268.62 rows=1467400 width=96) (actual time=371.380..395.324 rows=22908 loops=1)
               Buffers: shared hit=3067
               ->  Nested Loop  (cost=2.98..55962.20 rows=14674 width=64) (actual time=371.362..376.059 rows=11454 loops=1)
                     Buffers: shared hit=3067
                     ->  Nested Loop  (cost=2.54..39.31 rows=500 width=40) (actual time=371.332..371.337 rows=1 loops=1)
                           Buffers: shared hit=3
                           ->  Index Scan using campaigns_pkey on campaigns c  (cost=0.28..8.30 rows=1 width=355) (actual time=371.243..371.244 rows=1 loops=1)
                                 Index Cond: (id = 11870)
                                 Buffers: shared hit=3
                           ->  Hash Join  (cost=2.26..26.01 rows=500 width=40) (actual time=0.083..0.086 rows=1 loops=1)
                                 Hash Cond: (EXTRACT(dow FROM d.date) = ((j.dow)::integer)::numeric)
                                 ->  Function Scan on generate_series d  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.025..0.026 rows=1 loops=1)
                                 ->  Hash  (cost=1.00..1.00 rows=100 width=64) (actual time=0.040..0.040 rows=7 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                       ->  Function Scan on jsonb_each j  (cost=0.00..1.00 rows=100 width=64) (actual time=0.015..0.016 rows=7 loops=1)
                     ->  Index Scan using device_timeslots_date_index on device_timeslots t  (cost=0.43..111.56 rows=29 width=32) (actual time=0.023..3.477 rows=11454 loops=1)
                           Index Cond: ((date = d.date) AND (date >= '2024-04-26'::date) AND (date <= '2024-04-26'::date))
                           Buffers: shared hit=3064
               ->  Memoize  (cost=0.01..1.01 rows=100 width=32) (actual time=0.000..0.000 rows=2 loops=11454)
                     Cache Key: j.times
                     Cache Mode: binary
                     Hits: 11453  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                     ->  Function Scan on jsonb_array_elements t_1  (cost=0.00..1.00 rows=100 width=32) (actual time=0.004..0.005 rows=2 loops=1)
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.455 ms
 JIT:
   Functions: 36
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 1.949 ms, Inlining 28.891 ms, Optimization 207.481 ms, Emission 134.907 ms, Total 373.228 ms
 Execution Time: 429.037 ms

And (B)

 GroupAggregate  (cost=70121.37..71282.14 rows=33165 width=124) (actual time=37.895..48.577 rows=4670 loops=1)
   Group Key: t.device_id, t.date, t.start_time, t.end_time, z.times
   Buffers: shared hit=3064, local hit=1
   ->  Sort  (cost=70121.37..70204.28 rows=33165 width=64) (actual time=37.874..38.979 rows=22908 loops=1)
         Sort Key: t.device_id, t.date, t.start_time, t.end_time, z.times, t.rank
         Sort Method: quicksort  Memory: 2737kB
         Buffers: shared hit=3064, local hit=1
         ->  Merge Join  (cost=67127.99..67631.11 rows=33165 width=64) (actual time=9.587..15.797 rows=22908 loops=1)
               Merge Cond: (z.date = t.date)
               Buffers: shared hit=3064, local hit=1
               ->  Sort  (cost=78.60..81.43 rows=1130 width=40) (actual time=0.030..0.031 rows=2 loops=1)
                     Sort Key: z.date
                     Sort Method: quicksort  Memory: 25kB
                     Buffers: local hit=1
                     ->  Seq Scan on z11870 z  (cost=0.00..21.30 rows=1130 width=40) (actual time=0.016..0.018 rows=2 loops=1)
                           Buffers: local hit=1
               ->  Sort  (cost=67049.39..67109.04 rows=23861 width=32) (actual time=9.549..10.720 rows=22907 loops=1)
                     Sort Key: t.date
                     Sort Method: quicksort  Memory: 1100kB
                     Buffers: shared hit=3064
                     ->  Bitmap Heap Scan on device_timeslots t  (cost=329.01..65314.41 rows=23861 width=32) (actual time=2.952..7.534 rows=11454 loops=1)
                           Recheck Cond: ((date >= '2024-04-26'::date) AND (date <= '2024-04-26'::date))
                           Heap Blocks: exact=3052
                           Buffers: shared hit=3064
                           ->  Bitmap Index Scan on device_timeslots_date_index  (cost=0.00..323.05 rows=23861 width=0) (actual time=1.648..1.648 rows=11454 loops=1)
                                 Index Cond: ((date >= '2024-04-26'::date) AND (date <= '2024-04-26'::date))
                                 Buffers: shared hit=12
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.587 ms
 Execution Time: 49.163 ms

Both look correct on buffer hit? The first hits too much (11453) ?