Inconsistent query planners between two Postgres 16.4 instances

Question

I am trying to understand why the query planner is being so unoptimal on my DO managed Postgres 16.4. I have installed Postgres 16.4 on my Windows dev machine.

I restored the same database to both, VACUUM ANALYZEd both. Then, I run the same query on both. On DO managed, I get an execution time of 32.182s, but on my Windows machine, 7.282s. Accordingly, the output of EXPLAIN (ANALYZE, BUFFERS) on both machines is very different.

The query is using views and functions, so I'm not sure how relevant it is, unless I was to paste the 500 lines or so unfolding all the views. So, my specific questions:

1- How/why are the two query planners taking such different routes? Since I vaccumed analyzed the fresh pg_restore, I would think both clusters have the same statistics about the tables? Or does VACUUM ANALYZE not force all statistics to refresh? Is it down to the compilation on Linux vs Windows, or is there some configuration parameter I should look at?

2- DO query plan is at https://explain.dalibo.com/plan/e6gd8cde718dafbc. Windows query plan is at https://explain.dalibo.com/plan/gbgb978e5gc2e067. Any advice on getting DO query planner not be stupid and do the thing Windows query planner is doing and get an execution time of 5 seconds rather than 30?

3- While trying to figure out what's wrong, I also see this node, where it says doing an index scan on a table of about 800 rows takes 33s 465ms. Now clearly, that is not literally what is happening. What is happening? What part, what do I look at in the query plan to figure it out?

Answer 1

I can't read Dalibo's plan obfuscator, but in the raw output we can see the difference.

Slow:

->  Index Scan using stations_pkey on stations s  (cost=0.28..177.61 rows=14 width=347) (actual time=33463.808..33464.833 rows=3 loops=1)
      Filter: (active AND (station_type = 'Météorologique'::station_type_list) AND can_view_station(id, NULL::text, NULL::integer))
      Rows Removed by Filter: 559
      Buffers: shared hit=531

Fast:

->  Index Scan using stations_pkey on stations s  (cost=0.28..247.48 rows=14 width=347) (actual time=1.153..2.657 rows=3 loops=1)
      Filter: (active AND (station_type = 'Météorologique'::station_type_list) AND can_view_station(id, NULL::text, NULL::integer))
      Rows Removed by Filter: 559
      Buffers: shared hit=642

One possible explanation could be (huge) index bloat, but if both databases are freshly restored, that cannot be the case.

Another explanation is that there was a high lock on the stations table that blocks execution. But if the result is repeatable, that also seems unlikely.

The most likely explanation is that the function can_view_station() is for some reason very slow on the first system, but fast on the second. You should investigate in that direction.