On Mon, Mar 6, 2023 at 3:00 PM tender wang <tndrw...@gmail.com> wrote:
> tender wang <tndrw...@gmail.com> > [image: 附件]14:51 (2小时前) > 发送至 pgsql-hackers > Hi hackers. > This query has different result on 16devel and 15.2. > select > sample_3.n_regionkey as c0, > ref_7.l_linenumber as c3, > sample_4.l_quantity as c6, > sample_5.n_nationkey as c7, > sample_3.n_name as c8 > from > public.nation as sample_3 > left join public.lineitem as ref_5 > on ((cast(null as text) ~>=~ cast(null as text)) > or (ref_5.l_discount is NULL)) > left join public.time_statistics as ref_6 > inner join public.lineitem as ref_7 > on (ref_7.l_returnflag = ref_7.l_linestatus) > right join public.lineitem as sample_4 > left join public.nation as sample_5 > on (cast(null as tsquery) = cast(null as tsquery)) > on (cast(null as "time") <= cast(null as "time")) > right join public.customer as ref_8 > on (sample_4.l_comment = ref_8.c_name ) > on (ref_5.l_quantity = ref_7.l_quantity ) > where (ref_7.l_suppkey is not NULL) > or ((case when cast(null as lseg) >= cast(null as lseg) then cast(null > as inet) else cast(null as inet) end > && cast(null as inet)) > or (pg_catalog.getdatabaseencoding() !~~ case when (cast(null as > int2) <= cast(null as int8)) > or (EXISTS ( > select > ref_9.ps_comment as c0, > 5 as c1, > ref_8.c_address as c2, > 58 as c3, > ref_8.c_acctbal as c4, > ref_7.l_orderkey as c5, > ref_7.l_shipmode as c6, > ref_5.l_commitdate as c7, > ref_8.c_custkey as c8, > sample_3.n_nationkey as c9 > from > public.partsupp as ref_9 > where cast(null as tsquery) @> cast(null as tsquery) > order by c0, c1, c2, c3, c4, c5, c6, c7, c8, c9 limit 38)) > then cast(null as text) else cast(null as text) end > )) > order by c0, c3, c6, c7, c8 limit 137; > > plan on 16devel: > > QUERY PLAN > > > ---------------------------------------------------------------------------------------------------------------------------------------------------------------- > Limit > InitPlan 1 (returns $0) > -> Result > One-Time Filter: false > -> Sort > Sort Key: sample_3.n_regionkey, l_linenumber, l_quantity, > n_nationkey, sample_3.n_name > -> Nested Loop Left Join > -> Seq Scan on nation sample_3 > -> Materialize > -> Nested Loop Left Join > Join Filter: (ref_5.l_quantity = l_quantity) > Filter: ((l_suppkey IS NOT NULL) OR > (getdatabaseencoding() !~~ CASE WHEN ($0 OR NULL::boolean) THEN NULL::text > ELSE NULL::text END)) > -> Seq Scan on lineitem ref_5 > Filter: (l_discount IS NULL) > -> Result > One-Time Filter: false > (16 rows) > > plan on 15.2: > QUERY > PLAN > > ---------------------------------------------------------------------------------------------------------------------------------------------------- > Limit > InitPlan 1 (returns $0) > -> Result > One-Time Filter: false > -> Sort > Sort Key: sample_3.n_regionkey, l_linenumber, l_quantity, > n_nationkey, sample_3.n_name > -> Nested Loop Left Join > Filter: ((l_suppkey IS NOT NULL) OR (getdatabaseencoding() > !~~ CASE WHEN ($0 OR NULL::boolean) THEN NULL::text ELSE NULL::text END)) > -> Seq Scan on nation sample_3 > -> Materialize > -> Nested Loop Left Join > Join Filter: (ref_5.l_quantity = l_quantity) > -> Seq Scan on lineitem ref_5 > Filter: (l_discount IS NULL) > -> Result > One-Time Filter: false > (16 rows) > > > It looks wrong that the qual (e.g ((l_suppkey IS NOT NULL) OR > (getdatabaseencoding() !~~ CASE WHEN ($0 OR NULL::boolean) THEN NULL::text > ELSE NULL::text END))) is pushdown. > Is that because $0 comes from a peer plan? An example of the difference in the results would help. -- Best Wishes, Ashutosh Bapat
