Skip to main content

Return to Answer

added 11 characters in body
Source Link
Laurenz Albe
Laurenz Albe
  • 257k
  • 22
  • 312
  • 388

The problemIn the bad plan after the upgrade, all the row count estimates for the scans of the individual partitions is that a type cast from timestamp (without time zone) and timestamp with time zone depends on1, which is far off the current settingactual row count. That is very likely the cause of the parameterbad performance after the upgrade.

It seems that your provider forgot to gather optimizer statistics after running timezonepg_upgrade. So if you say that the query "breaks" on one system (whatever exactly

Kudos to Frank Pachot for pointing that means), the parameter must be set differentlyout in the respective sessionsa comment.

The problem is that a type cast from timestamp (without time zone) and timestamp with time zone depends on the current setting of the parameter timezone. So if you say that the query "breaks" on one system (whatever exactly that means), the parameter must be set differently in the respective sessions.

In the bad plan after the upgrade, all the row count estimates for the scans of the individual partitions is 1, which is far off the actual row count. That is very likely the cause of the bad performance after the upgrade.

It seems that your provider forgot to gather optimizer statistics after running pg_upgrade.

Kudos to Frank Pachot for pointing that out in a comment.

Source Link
Laurenz Albe
Laurenz Albe
  • 257k
  • 22
  • 312
  • 388

The problem is that a type cast from timestamp (without time zone) and timestamp with time zone depends on the current setting of the parameter timezone. So if you say that the query "breaks" on one system (whatever exactly that means), the parameter must be set differently in the respective sessions.