On Tue, 21 Feb 2023 at 14:38, Charles <peacech@gmail.com> wrote: > Disabling mergejoin on 14.6 and disabling nestedloop on 15.2 causes both to use hashjoin where it runs for 37ms in 14.6 and 208ms in 15.2. > > 14.6: > -> Parallel Index Scan using idx_stock_price_date on stock_price (cost=0.43..59671.39 rows=81248 width=13) (actual time=0.021..0.689 rows=1427 loops=3) > Index Cond: (date > $0) > Filter: (value > 0) > Rows Removed by Filter: 222
The difference in the two above fragments likely accounts for the majority of the remaining performance difference. Possibly 15.2 is using a Seq Scan because it's estimating slightly more rows from stock_price for these two quals. For the date > $0 qual, the stats don't really help as the planner does not know what $0 will be during planning, so it'll just assume that the selectivity is 1/3rd of rows. For the value > 0, there could be some variation there just between ANALYZE runs. That might be enough to account for the difference in estimate between 14 and 15.
You might also want to check that effective_cache_size is set to something realistic on 15. random_page_cost is also a factor for index scan vs seq scan.
Thank you for the hint. I think this is it. random_page_cost was set to 1.1 in 14.6 (the data directory is located in a ssd). When upgrading to 15.2 I thought that ssd random seek time is not that close to sequential seek time, so I revert it back to 4.
The new timings are
random_page_cost = 1.1 with no extended stats = still 27 seconds
random_page_cost = 1.1 with materialized cte = 92ms
random_page_cost = 1.1 with extended stats = 33ms
random_page_cost = 1.1 with extended stats and materialized cte = 33ms