Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: marcos sicat <marcos(dot)sicat(at)atlasifs(dot)com>
Cc: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE
Date: 2025-04-28 06:17:19
Message-ID: CAFj8pRBygw-a5+LdeWSW=8N091=i2Tgmx=73ORyTv6ksT3Zw5w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi

po 28. 4. 2025 v 7:37 odesílatel marcos sicat <marcos(dot)sicat(at)atlasifs(dot)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: image.png]
> Server Info: Postgres 15
>
> [image: image.png]
>
> Query Performance:
>
> Postgres 17:
> [image: 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: 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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Álvaro Herrera 2025-04-28 08:42:14 Re: pg_restore error with partitioned table having exclude constraint
Previous Message vignesh C 2025-04-28 04:57:47 Re: BUG #18897: Logical replication conflict after using pg_createsubscriber under heavy load