Results on 16devel: c0 | c3 | c6 | c7 | c8 ----+----+----+----+--------------------------- 0 | | | | ALGERIA 0 | | | | ETHIOPIA 0 | | | | KENYA 0 | | | | MOROCCO 0 | | | | MOZAMBIQUE 1 | | | | ARGENTINA 1 | | | | BRAZIL 1 | | | | CANADA 1 | | | | PERU 1 | | | | UNITED STATES 2 | | | | CHINA 2 | | | | INDIA 2 | | | | INDONESIA 2 | | | | JAPAN 2 | | | | VIETNAM 3 | | | | FRANCE 3 | | | | GERMANY 3 | | | | ROMANIA 3 | | | | RUSSIA 3 | | | | UNITED KINGDOM 4 | | | | EGYPT 4 | | | | IRAN 4 | | | | IRAQ 4 | | | | JORDAN 4 | | | | SAUDI ARABIA (25 rows)
Results on 15.2: c0 | c3 | c6 | c7 | c8 ----+----+----+----+---- (0 rows) tender wang <tndrw...@gmail.com> 于2023年3月6日周一 22:48写道： > Results on 16devel: > c0 | c3 | c6 | c7 | c8 > ----+----+----+----+--------------------------- > 0 | | | | ALGERIA > 0 | | | | ETHIOPIA > 0 | | | | KENYA > 0 | | | | MOROCCO > 0 | | | | MOZAMBIQUE > 1 | | | | ARGENTINA > 1 | | | | BRAZIL > 1 | | | | CANADA > 1 | | | | PERU > 1 | | | | UNITED STATES > 2 | | | | CHINA > 2 | | | | INDIA > 2 | | | | INDONESIA > 2 | | | | JAPAN > 2 | | | | VIETNAM > 3 | | | | FRANCE > 3 | | | | GERMANY > 3 | | | | ROMANIA > 3 | | | | RUSSIA > 3 | | | | UNITED KINGDOM > 4 | | | | EGYPT > 4 | | | | IRAN > 4 | | | | IRAQ > 4 | | | | JORDAN > 4 | | | | SAUDI ARABIA > (25 rows) > > Results on 15.2: > c0 | c3 | c6 | c7 | c8 > ----+----+----+----+---- > (0 rows) > > Ashutosh Bapat <ashutosh.bapat....@gmail.com> 于2023年3月6日周一 22:14写道： > >> >> >> 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 >> >
