Re: Query run in 27s with 15.2 vs 37ms with 14.6

Wrapping the query with a select * from (...) t where length(code) = 4 puts the execution time back to 27 seconds.

This is a bit unexpected since I expect that the result from the inner query to be executed first and then filtered.

select * from (
  with vol_avg as (
    select
      code,
      avg(value) as value
    from
      stock_price
    where
      value > 0 and
      date > (select date from stock_date order by date desc limit 1 offset 5)
    group by
      code
  )
  select
    s.code,
    s.close,
    100.0 * (s.close - s.open) / s.open as chg,
    s.value,
    s.volume,
    va.value as value_avg
  from
    stock_price s
    inner join vol_avg va on
      s.code = va.code
  where
    s.date = '2023-02-20' and
    s.open > 0 and
    s.value > 0
) t where length(code) = 4

Nested Loop  (cost=63003.26..64440.14 rows=1 width=89) (actual time=22.859..26784.170 rows=779 loops=1)
  Join Filter: (s.code = stock_price.code)
  Rows Removed by Join Filter: 349117
  ->  Index Scan using idx_stock_price_date on stock_price s  (cost=0.43..1152.53 rows=1 width=29) (actual time=0.084..4.024 rows=779 loops=1)
        Index Cond: (date = '2023-02-20'::date)
        Filter: ((open > 0) AND (value > 0) AND (length(code) = 4))
        Rows Removed by Filter: 210
  ->  Finalize GroupAggregate  (cost=63002.83..63264.98 rows=1005 width=37) (actual time=33.983..34.347 rows=449 loops=779)
        Group Key: stock_price.code
        InitPlan 1 (returns $0)
          ->  Limit  (cost=0.41..0.43 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)
                ->  Index Only Scan Backward using stock_date_pkey on stock_date  (cost=0.28..182.90 rows=7359 width=4) (actual time=0.012..0.012 rows=6 loops=1)
                      Heap Fetches: 6
        ->  Gather Merge  (cost=63002.40..63236.91 rows=2010 width=37) (actual time=33.972..34.045 rows=450 loops=779)
              Workers Planned: 2
              Params Evaluated: $0
              Workers Launched: 2
              ->  Sort  (cost=62002.37..62004.89 rows=1005 width=37) (actual time=0.885..0.896 rows=150 loops=2337)
                    Sort Key: stock_price.code
                    Sort Method: quicksort  Memory: 95kB
                    Worker 0:  Sort Method: quicksort  Memory: 25kB
                    Worker 1:  Sort Method: quicksort  Memory: 25kB
                    ->  Partial HashAggregate  (cost=61939.70..61952.26 rows=1005 width=37) (actual time=0.696..0.776 rows=302 loops=2337)
                          Group Key: stock_price.code
                          Batches: 1  Memory Usage: 577kB
                          Worker 0:  Batches: 1  Memory Usage: 73kB
                          Worker 1:  Batches: 1  Memory Usage: 73kB
                          ->  Parallel Bitmap Heap Scan on stock_price  (cost=8799.81..61483.32 rows=91275 width=13) (actual time=0.068..0.335 rows=1427 loops=2337)
                                Recheck Cond: (date > $0)
                                Filter: (value > 0)
                                Rows Removed by Filter: 222
                                Heap Blocks: exact=138662
                                ->  Bitmap Index Scan on idx_stock_price_date  (cost=0.00..8745.05 rows=908402 width=0) (actual time=0.177..0.177 rows=8917 loops=779)
                                      Index Cond: (date > $0)
Planning Time: 0.362 ms
Execution Time: 26784.929 ms