Hi David,
After adding the suggested index, the optimizer picked up the index, and the query ran in 36ms in v15 and 33ms in v17.
Thank you!
QUERY PLAN
-> Bitmap Index Scan on orderbook_added_date_printable_idx (cost=0.00..120.46 rows=8324 width=0) (actual time=0.496..0.496 rows=8589 loops=1)
From: marcos sicat <marcos.sicat@atlasifs.com>
Date: Wednesday, April 30, 2025 at 7:53 PM
To: David Rowley <dgrowleyml@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE
Thanks for the advice, David. I will check the settings and also the index. Some database technologies (I won’t mention here ) have an index advisor. Once turned on, it will suggest or recommend creating an index. I migrated to Postgres from Sybase and MS SQL in 2019. I am using Postgres for an online stock trading platform.
From: David Rowley <dgrowleyml@gmail.com>
Date: Wednesday, April 30, 2025 at 7:16 PM
To: marcos sicat <marcos.sicat@atlasifs.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Postgres 17.4 is much slower than Postgres 15.12 using RECURSIVE
On Thu, 1 May 2025 at 09:43, marcos sicat <marcos.sicat@atlasifs.com> wrote:
> I was able to capture the Qplans for both, and I highlighted the differences in red. Are there server settings differences between v17 and v15 by default?
>
> What would be the suggested configuration settings in v17 that would behave like v15 and match the performance with v15?
(I'd recommend in the future attaching two text files, one for each
plan. You might find people don't want to open spreadsheets received
through email.)
It looks like v17 doesn't want to use the indexgraph1D index on
prod_itchbbo_p_small_message. You should check if that index exists on
the v17 instance. v17 does see the Seq Scan as the cheaper option.
I'm unsure why that would have changed between v15 and v17. Are
seq_page_cost, random_page_cost, and effective_cache_size all set to
the same as on v15?
You could do:
create index on prod_itchbbo_p_small_message (order_book, date(added_date));
and the planner would likely make use of the more selective index. The
"printable" might be worth adding as well, depending on how many rows
you still see in the "Rows Removed by Filter".
David
