Hello Richard,
thanks for your quick reply. > We need to fix this. Do you have a better idea than just keeping the old quals - possibly just the ones that get eliminated - in a separate data structure? Is the push down of quals the only case of elimination of quals, only counting the ones which happen before the restrict lists are generated? Regards Arne ________________________________ From: Richard Guo <ri...@pivotal.io> Sent: Thursday, August 1, 2019 1:14:44 PM To: Arne Roland Cc: pgsql-hackers@lists.postgresql.org Subject: Re: Partial join On Thu, Aug 1, 2019 at 5:38 PM Arne Roland <a.rol...@index.de<mailto:a.rol...@index.de>> wrote: Hello, I attached one example of a partitioned table with multi column partition key. I also attached the output. Disabling the hash_join is not really necessary, it just shows the more drastic result in the case of low work_mem. Comparing the first and the second query I was surprised to see that SET enable_partitionwise_join could cause the costs to go up. Shouldn't the paths of the first query be generated as well? The third query seems to have a different issue. That one is close to my original performance problem. It looks to me like the push down of the sl condition stops the optimizer considering a partial join. If so would it be sane to keep a copy of the original quals to make the partial join possible? Do you have better ideas? For the third query, a rough investigation shows that, the qual 'sl = 5' and 'sc.sl<http://sc.sl> = sg.sl<http://sg.sl>' will form an equivalence class and generate two implied equalities: 'sc.sl<http://sc.sl> = 5' and 'sg.sl<http://sg.sl> = 5', which can be pushed down to the base rels. One consequence of the deduction is when constructing restrict lists for the joinrel, we lose the original restrict 'sc.sl<http://sc.sl> = sg.sl<http://sg.sl>', and this would fail the check have_partkey_equi_join(), which checks if there exists an equi-join condition for each pair of partition keys. As a result, this joinrel would not be considered as an input to further partitionwise joins. We need to fix this. Thanks Richard
