pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: marcos sicat <marcos.sicat@atlasifs.com>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Cc: Marcos Arnel G. Sicat Sicat <marcos.sicat@atlasifs.com>
Subject: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE
Date: Sun, 27 Apr 2025 20:51:10 +0000
Message-ID: <MW5PR84MB2227AA8FFF0ACD8E02F14F1AF2862@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM> (raw )
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
Attachments:
[application/octet-stream] img-83918caa-2822-4948-8876-92437da84717 (63.5K, ../MW5PR84MB2227AA8FFF0ACD8E02F14F1AF2862@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM/3-img-83918caa-2822-4948-8876-92437da84717)
download
[application/octet-stream] img-3b4394de-3e6b-4c5d-9e95-556736ad624c (64.1K, ../MW5PR84MB2227AA8FFF0ACD8E02F14F1AF2862@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM/4-img-3b4394de-3e6b-4c5d-9e95-556736ad624c)
download
[application/octet-stream] img-bb7ee740-5854-4628-98d1-04cfddc4b3f7 (158.3K, ../MW5PR84MB2227AA8FFF0ACD8E02F14F1AF2862@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM/5-img-bb7ee740-5854-4628-98d1-04cfddc4b3f7)
download
[application/octet-stream] img-faeb37af-9724-48cc-badd-b4326d182be9 (152.8K, ../MW5PR84MB2227AA8FFF0ACD8E02F14F1AF2862@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM/6-img-faeb37af-9724-48cc-badd-b4326d182be9)
download
view thread (10+ messages) latest in thread
Message-ID: <MW5PR84MB2227AA8FFF0ACD8E02F14F1AF2862@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM>
Permalink: ../MW5PR84MB2227AA8FFF0ACD8E02F14F1AF2862@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM/
Also on: postgresql.org/message-id/MW5PR84MB2227AA8FFF0ACD8E02F14F1AF2862@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: marcos.sicat@atlasifs.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE
In-Reply-To: <MW5PR84MB2227AA8FFF0ACD8E02F14F1AF2862@MW5PR84MB2227.NAMPRD84.PROD.OUTLOOK.COM>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox