Postgres Pro
Downloads
Home   >   mailing lists

Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE - Mailing list pgsql-bugs

From marcos sicat
Subject Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE
Date
Msg-id MW5PR84MB2227BFE47602F2AAED04A0B3F2812@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM
Whole thread Raw
In response to Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE
List pgsql-bugs
Tree view
Thanks, Pavel.

The function is the same between v15 and v17.  Is there a subtle difference in performance for nested subqueries in v17?


--/
CREATE FUNCTION iq_get_stats_security_v1_4_4 (bigint, timestamp without time zone)  RETURNS TABLE(value double precision, volume double precision, avg_price double precision, high_price double precision, low_price double precision, stock_date timestamp without time zone, bestbidprice double precision, bestbidsize double precision, bestofferprice double precision, bestoffersize double precision, last_trade_price double precision, close_price double precision, open_price double precision, prev_close_price double precision, outstanding_shares bigint)
  VOLATILE
AS $body$
BEGIN
RETURN query

SELECT Cast(Round(Sum(STATS."value"), 4) AS double precision) AS "value", 
Cast(Sum(STATS."volume") AS double precision) AS "volume", 
Cast(Round(Sum(STATS."value"), 4) AS double precision)/Cast(Sum(STATS."volume") AS double precision) AS "avg_price", 
Cast(Round(MAX(STATS."high_price"), 4) AS double precision) AS "high_price", 
Cast(Round(MIN(STATS."low_price"), 4) AS double precision) AS "low_price", 
MAX(STATS."stock_date") AS "stock_date",  
Cast(MAX(BBO."bestbidprice") AS double precision),  
Cast(MAX(BBO."bestbidsize") AS double precision),  
Cast(MAX(BBO."bestofferprice") AS double precision),  
Cast(MAX(BBO."bestoffersize") AS double precision),
Cast(MAX(LTP."trade_price") AS double precision),
Cast(MAX(CLOSEP."close_price")AS double precision),
Cast(MAX(OPENING."trade_price")AS double precision),
Cast(MAX(PREVCLOSEP."prev_close_price")AS double precision),
Cast(MAX(STOCKINFO."outstanding_shares") AS bigint) as "outstanding_shares"

FROM   (
         
    SELECT Sum(TRADES.executed_quantity * Cast(TRADES.execution_price AS DECIMAL) / 10000) AS "value", 
           Sum(TRADES.executed_quantity)AS "volume", 
           AVG(Cast(TRADES.execution_price AS DECIMAL) / 10000) AS "avg_price", 
           MAX(Cast(TRADES.execution_price AS DECIMAL) / 10000) AS "high_price", 
           MIN(Cast(TRADES.execution_price AS DECIMAL) / 10000) AS "low_price", 
           MAX(TRADES.msg_date) AS "stock_date" 
    FROM 
    (
        SELECT distinct on (TRADES_P."trade_id") * from(
            
            SELECT p.msg_date,p.executed_quantity,p.execution_price, 
                       (p.match_number) AS "trade_id" 
                FROM PUBLIC.prod_itchbbo_p_small_message p
                WHERE Date(p.msg_date) = $2 
                  AND p.printable = 'Y'
                  AND p.order_book=$1                  
                ORDER BY p.match_number DESC

        ) AS TRADES_P Order by "trade_id" ASC
    )AS TRADES
      
    
                
            
) AS STATS 
,
( Select Cast(best_bid_price AS double precision) / 10000 AS "bestbidprice", 
         Cast(best_bid_size AS double precision) AS "bestbidsize" ,
         Cast(best_offer_price AS double precision) / 10000  AS "bestofferprice", 
         Cast(best_offer_size AS double precision) AS "bestoffersize" 
  FROM public.prod_itchbbo_o_message
  WHERE order_book = $1 
  AND date(added_date)= $2

    AND added_date::timestamp::time<=
    ( 
        SELECT   s.msg_date::timestamp::time
        FROM     prod_itch_s_message s 
        WHERE    s.event_code='M' 
        ORDER BY s.msg_date DESC limit 1
    ) 
  ORDER BY msg_date desc limit 1
            ) as BBO 
,( select LASTTRADE.trade_price from 

(
        SELECT * from(SELECT (Cast(execution_price AS double precision) / 10000) AS "trade_price", 
                       (match_number) AS "trade_id" 
                FROM PUBLIC.prod_itchbbo_p_small_message 
                WHERE order_book = $1 
                  AND Date(added_date) = $2 
                  AND printable = 'Y' 
                ORDER BY match_number DESC limit 1) AS TRADES_P
                
            
) AS LASTTRADE order by LASTTRADE.trade_id DESC limit 1 ) as LTP 

,( select max(Cast(closePrice."close_price" AS double precision) )as "close_price" from(Select Cast(execution_price AS double precision) / 10000 as "close_price" from PUBLIC.prod_itchbbo_p_small_message  
    where date(msg_date)=$2
    and executed_quantity = 0 
    and order_book =$1  order by msg_date DESC limit 1 )as closePrice)as CLOSEP


,( select FIRSTTRADE.trade_price from 

(

        SELECT * from(SELECT (Cast(execution_price AS double precision) / 10000) AS "trade_price", 
                       (match_number) AS "trade_id" 
                FROM PUBLIC.prod_itchbbo_p_small_message 
                WHERE order_book = $1 
                  AND Date(added_date) = $2 
                  AND printable = 'Y' 
                ORDER BY match_number asc limit 1) AS TRADES_P
          
        
            
) AS FIRSTTRADE order by FIRSTTRADE.trade_id ASC limit 1 

) as OPENING

,( Select Cast(price AS double precision) / 10000 as "prev_close_price" from public.prod_itch_a_message 
    where date(msg_date)=$2
    and order_number = 0 
    and orderbook =$1 order by msg_date ASC limit 1 )as PREVCLOSEP

,( Select Cast(shares AS bigint) as "outstanding_shares" from public.prod_stock_information 
        where order_book = $1 and group_name = 'N' ) as STOCKINFO
 
;

END;
$body$ LANGUAGE plpgsql
/


From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Monday, April 28, 2025 at 2:17 AM
To: marcos sicat <marcos.sicat@atlasifs.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE

Hi

po 28. 4. 2025 v 7:37 odesílatel marcos sicat <marcos.sicat@atlasifs.com> napsal:

Postgres 17 version: PostgreSQL 17.4 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit

Postgres 15 version: PostgreSQL 15.12 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit



Description of Bug:

Using RECURSIVE in Postgres 17 is much slower than Postgres 15 

Postgres 17.4 : 6.533 seconds
Postgres 15.12: 1.564 seconds

Same table objects, same tables indexes, same number of records

I ran vacuum, analyze, reindex, and cache (pg_warm) on both Postgres 17 and 15. 

Note: I also used LATERAL, postgres 15 performance is superior than postgres17. Postgres15 timings is 2.3 compared to Postgres17 timings is avg 7 seconds.

SQL:

WITH RECURSIVE stock AS
(
SELECT  prod.order_book,
        prod.symbol,
        value::numeric(20,4),
        volume::numeric,
        prev_close_price::numeric(20,4),
        open_price::numeric(20,4),
        low_price::numeric(20,4),
        high_price::numeric(20,4),
        last_trade_price::numeric(20,4),
        avg_price::numeric(20,4),
        close_price::numeric(20,4),
        bestbidprice::numeric(20,4),
        bestbidsize::numeric(20,0),
        bestofferprice::numeric(20,4),
        bestoffersize::numeric(20,0),
       (((last_trade_price - prev_close_price)/prev_close_price)*100)::numeric(10,2) AS percentage
FROM vw_pse_traded_companies prod,
iq_get_stats_security_v1_4_4(prod.order_book,( 
    (   SELECT 
            DATE(d.added_date) AS DATE
        FROM 
            prod_itchbbo_s_small_message d
        ORDER BY 
            d.added_date DESC
        LIMIT 
            1))::TIMESTAMP without TIME zone)
WHERE 
  prod.group_name = 'N'
)
select * from stock where value is NOT NULL;


Server Info: Postgres 17

image.png
Server Info: Postgres 15

image.png

Query Performance:

Postgres 17:
image.png

QUERY PLAN
CTE Scan on stock  (cost=35697.80..43197.80 rows=373125 width=812) (actual time=28.778..6526.084 rows=241 loops=1)
  Filter: (value IS NOT NULL)
  Rows Removed by Filter: 138
  CTE stock
    ->  Nested Loop  (cost=68.11..35697.80 rows=375000 width=300) (actual time=4.819..6524.963 rows=379 loops=1)
          InitPlan 1
            ->  Limit  (cost=1.60..1.60 rows=1 width=12) (actual time=0.027..0.028 rows=1 loops=1)
                  ->  Sort  (cost=1.60..1.68 rows=34 width=12) (actual time=0.027..0.028 rows=1 loops=1)
                        Sort Key: d.added_date DESC
                        Sort Method: top-N heapsort  Memory: 25kB
                        ->  Seq Scan on prod_itchbbo_s_small_message d  (cost=0.00..1.43 rows=34 width=12) (actual time=0.004..0.008 rows=34 loops=1)
          ->  Sort  (cost=66.26..67.20 rows=375 width=4291) (actual time=0.442..0.666 rows=379 loops=1)
                Sort Key: info.order_book, listed.symbol
                Sort Method: quicksort  Memory: 42kB
                ->  Hash Join  (cost=18.62..50.23 rows=375 width=4291) (actual time=0.120..0.328 rows=379 loops=1)
                      Hash Cond: ((info.symbol)::text = (listed.symbol)::text)
                      ->  Seq Scan on prod_stock_information info  (cost=0.00..30.60 rows=380 width=12) (actual time=0.008..0.123 rows=380 loops=1)
                            Filter: ((group_name)::text = 'N'::text)
                            Rows Removed by Filter: 388
                      ->  Hash  (cost=13.83..13.83 rows=383 width=4) (actual time=0.082..0.082 rows=383 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 22kB
                            ->  Seq Scan on prod_pse_listed_companies listed  (cost=0.00..13.83 rows=383 width=4) (actual time=0.003..0.038 rows=383 loops=1)
          ->  Function Scan on iq_get_stats_security_v1_4_4  (cost=0.25..10.25 rows=1000 width=104) (actual time=17.197..17.197 rows=1 loops=379)
Planning Time: 0.999 ms
Execution Time: 6526.908 ms


Postgres 15:

image.png

QUERY PLAN
CTE Scan on stock  (cost=35697.80..43197.80 rows=373125 width=812) (actual time=7.746..1443.691 rows=241 loops=1)
  Filter: (value IS NOT NULL)
  Rows Removed by Filter: 138
  CTE stock
    ->  Nested Loop  (cost=68.11..35697.80 rows=375000 width=300) (actual time=2.426..1443.143 rows=379 loops=1)
          InitPlan 1 (returns $0)
            ->  Limit  (cost=1.60..1.60 rows=1 width=12) (actual time=0.015..0.016 rows=1 loops=1)
                  ->  Sort  (cost=1.60..1.68 rows=34 width=12) (actual time=0.014..0.014 rows=1 loops=1)
                        Sort Key: d.added_date DESC
                        Sort Method: top-N heapsort  Memory: 25kB
                        ->  Seq Scan on prod_itchbbo_s_small_message d  (cost=0.00..1.43 rows=34 width=12) (actual time=0.005..0.008 rows=34 loops=1)
          ->  Sort  (cost=66.26..67.20 rows=375 width=4291) (actual time=0.466..0.563 rows=379 loops=1)
                Sort Key: info.order_book, listed.symbol
                Sort Method: quicksort  Memory: 51kB
                ->  Hash Join  (cost=18.62..50.23 rows=375 width=4291) (actual time=0.131..0.357 rows=379 loops=1)
                      Hash Cond: ((info.symbol)::text = (listed.symbol)::text)
                      ->  Seq Scan on prod_stock_information info  (cost=0.00..30.60 rows=380 width=12) (actual time=0.009..0.146 rows=380 loops=1)
                            Filter: ((group_name)::text = 'N'::text)
                            Rows Removed by Filter: 388
                      ->  Hash  (cost=13.83..13.83 rows=383 width=4) (actual time=0.106..0.107 rows=383 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 22kB
                            ->  Seq Scan on prod_pse_listed_companies listed  (cost=0.00..13.83 rows=383 width=4) (actual time=0.005..0.061 rows=383 loops=1)
          ->  Function Scan on iq_get_stats_security_v1_4_4  (cost=0.25..10.25 rows=1000 width=104) (actual time=3.797..3.797 rows=1 loops=379)
Planning Time: 0.428 ms
Execution Time: 1443.823 ms

The plan is same, but the execution of
iq_get_stats_security_v1_4_4
is 4x slower - 17ms x 4 ms, so you should to check iq_get_stats_security_v1_4_4

Regards

Pavel
Attachment

pgsql-bugs by date:

Previous
From: Álvaro Herrera
Date:
Subject: Re: pg_restore error with partitioned table having exclude constraint
Next
From: Tom Lane
Date:
Subject: Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE