On Mon, Nov 02, 2020 at 01:50:57PM +1300, David Rowley wrote: > On Tue, 27 Oct 2020 at 19:40, Amit Langote <amitlangot...@gmail.com> wrote: > > Some comments: > > Thanks for having a look at this. > > I've made some adjustments to those comments and pushed.
commit a929e17e5 doesn't appear in the v14 release notes, but I wanted to mention that this appears to allow fixing a rowcount mis-estimate for us, which I had reported before: https://www.postgresql.org/message-id/20170326193344.GS31628%40telsasoft.com https://www.postgresql.org/message-id/20170415002322.ga24...@telsasoft.com https://www.postgresql.org/message-id/20170524211730.gm31...@telsasoft.com And others have reported before: https://www.postgresql.org/message-id/flat/7df51702-0f6a-4571-80bb-188aaef26...@gmail.com https://www.postgresql.org/message-id/SG2PR01MB29673BE6F7AA24424FDBFF60BC670%40SG2PR01MB2967.apcprd01.prod.exchangelabs.com For years, our reports have included a generated WHERE clause for each table being queried, to allow each table's partitions to be properly pruned/excluded (not just one table, as happened if we used a single WHERE clause). That worked, but then the planner underestimates the rowcount, since it doesn't realize that the conditions are redundant (since "equality classes" do not handle the inequality conditions). In v14, one WHERE clause per table still gives an underestimate; but, now multiple WHERE clauses aren't required, because a single WHERE clause excludes partitions from each table, and the rowcount from the elided partitions is excluded from the Append rowcount at plan time. Thanks for this feature ! -- Justin
