Hi Tom
Thanks for your quick response. I check the table ddl. There is no row-level security turn on.
There are 2 tables.
dcg.brick_base_ebr This table is partition table. And have trigger define on the table.
dcg.brick_shipment This is very simple and normal table.
I attach the table ddl for your reference.
I also attach the output about different user get different access plan for your reference.
徐志宇（Jack）
Database Engineer
DB Team,ITS. Lenovo China
Phone: 86-18910860709
Email:xuzy13@xxxxxxxxxx
No.6 Shangdi West Road, Haidian District Beijing, China, 100085
-----邮件原件-----
发件人: Tom Lane <tgl@xxxxxxxxxxxxx>
发送时间: 2021年5月19日 23:00
收件人: Zhiyu ZY13 Xu <xuzy13@xxxxxxxxxx>
抄送: pgsql-general@xxxxxxxxxxxxxxxxxxxx
主题: [External] Re: PG 10 experience different user execute same sql get different access plan
Zhiyu ZY13 Xu <xuzy13@xxxxxxxxxx> writes:
> I experience a PG optimizer problem. Different user query with same SQL at same time. But get different access plan.
If you've got row-level security turned on for that table, it could explain results like this. RLS limits the planner's ability to see statistics, which can easily result in a worse plan.
regards, tom lane
ccfdb=# \dS+ dcg.brick_base_ebr
Table "dcg.brick_base_ebr"
Column | Type | Collation | Nullable | Default | Storage | Stats target |
Description
------------------+--------------------------------+-----------+----------+---------+----------+--------------+-
------------
bill_num | character(10) | | not null | | extended | |
bill_item | numeric(6,0) | | not null | | main | |
comp_code | character(4) | | not null | | extended | |
ze2e_flg | character(1) | | not null | | extended | |
fiscvarnt | character(2) | | not null | | extended | |
zsourceid | character(10) | | not null | | extended | |
fiscyear | numeric(4,0) | | not null | | main | |
sales_off | character(4) | | | | extended | |
profit_ctr | character(10) | | | | extended | |
doc_type | character(4) | | | | extended | |
doc_number | character(10) | | not null | | extended | |
so number
s_ord_item | numeric(6,0) | | | | main | |
billtoprty | character(10) | | | | extended | |
ship_to | character(10) | | | | extended | |
sold_to | character(10) | | | | extended | |
zsalescat | character(10) | | | | extended | |
plant | character(4) | | | | extended | |
doc_num | character(10) | | | | extended | |
doc_item | numeric(6,0) | | | | main | |
material | character(18) | | | | extended | |
prod_hier | character(18) | | | | extended | |
zprdhier1 | character(21) | | | | extended | |
zprdhier2 | character(23) | | | | extended | |
zprdhier3 | character(23) | | | | extended | |
zprdhier4 | character(24) | | | | extended | |
zprdhier5 | character(24) | | | | extended | |
currency | character(5) | | | | extended | |
zprdhier6 | character(23) | | | | extended | |
bill_date | character(10) | | | | extended | |
zfiscper | numeric(6,0) | | | | main | |
fiscper | numeric(7,0) | | | | main | |
inv_qty | numeric(17,3) | | | | main | |
zpcacost | numeric(17,2) | | | | main | |
zcountry1 | character(2) | | | | extended | |
zregion2 | character(15) | | | | extended | |
crm_endcst | character(10) | | | | extended | |
bill_qty | numeric(17,3) | | | | main | |
ze2e_prt | character(10) | | | | extended | |
zbmccost | numeric(17,2) | | | | main | |
znet_inv1 | numeric(17,2) | | | | main | |
zfingeo | character(20) | | | | extended | |
deliv_numb | character(10) | | | | extended | |
deliv_item | numeric(6,0) | | | | main | |
zshtoctry | character(3) | | | | extended | |
zsotoctry | character(3) | | | | extended | |
zbicountr | character(3) | | | | extended | |
sys_created_by | character(20) | | | | extended | |
sys_created_date | timestamp(6) without time zone | | | | plain | |
zbpcconty | character(32) | | | | extended | |
zbuel_fin | character(30) | | | | extended | |
mcmtbilldate | numeric(8,0) | | | | main | |
request | character(30) | | | | extended | |
item_categ | character(4) | | | | extended | |
salesorg | character(4) | | | | extended | |
matl_type | character(4) | | | | extended | |
base_uom | character(3) | | | | extended | |
fiscper3 | numeric(3,0) | | | | main | |
recordmode | character(1) | | | | extended | |
zcountry2 | character(2) | | | | extended | |
zextsales | character(1) | | | | extended | |
zendcst_s | character(10) | | | | extended | |
zbu_sg_l1 | character(20) | | | | extended | |
zbu_sg_l2 | character(40) | | | | extended | |
zfin_desc | text | | | | extended | |
zgtm_csl2 | character(20) | | | | extended | |
zgtm_csl1 | character(10) | | | | extended | |
zdcg_csl1 | character(20) | | | | extended | |
zdcg_csl2 | character(40) | | | | extended | |
zdcg_csl3 | character(100) | | | | extended | |
zsolutnl1 | character(20) | | | | extended | |
zsolutnl2 | character(40) | | | | extended | |
zsolutnl3 | character(40) | | | | extended | |
zsolutnl4 | character(40) | | | | extended | |
zzbpcbu | character varying(20) | | | | extended | |
zprodfly | character varying(64) | | | | extended | |
zprodt_l1 | character varying(64) | | | | extended | |
zbpcser | character varying(64) | | | | extended | |
zprdsg_l1 | character varying(64) | | | | extended | |
Indexes:
"zoh_ees15_pkey" PRIMARY KEY, btree (bill_num, bill_item, comp_code, fiscvarnt, fiscyear, ze2e_flg, zsourcei
d, doc_number)
Triggers:
insert_brick_base_ebr_partition_trigger BEFORE INSERT ON dcg.brick_base_ebr FOR EACH ROW EXECUTE PROCEDURE d
cg.brick_base_ebr_partition_trigger()
Child tables: dcg.brick_base_ebr_2018005,
dcg.brick_base_ebr_2018010,
dcg.brick_base_ebr_2019001,
dcg.brick_base_ebr_2019002,
dcg.brick_base_ebr_2019003,
dcg.brick_base_ebr_2019004,
dcg.brick_base_ebr_2019005,
dcg.brick_base_ebr_2019006,
dcg.brick_base_ebr_2019007,
dcg.brick_base_ebr_2020001,
dcg.brick_base_ebr_2020002,
dcg.brick_base_ebr_2020004,
dcg.brick_base_ebr_2020005,
dcg.brick_base_ebr_2020006,
dcg.brick_base_ebr_2020007,
dcg.brick_base_ebr_2020008,
dcg.brick_base_ebr_2020009,
dcg.brick_base_ebr_2020012
\dS+ dcg.brick_shipment
ccfdb=# \dS+ dcg.brick_shipment
Table "dcg.brick_shipment"
Column | Type | Collation | Nullable | Default
| Storage | Stats target | Description
------------------+-----------------------------+-----------+----------+----------------------------------------
--------+----------+--------------+--------------------
bukrs | character varying(4) | | |
| extended | | Company code
gjahr | numeric(4,0) | | |
| main | | Fiscal year
monat | numeric(2,0) | | |
| main | | Period
belnr | character varying(10) | | |
| extended | | Accounting Number
budat | numeric(8,0) | | |
| main | | Posting date
cpudt | numeric(8,0) | | |
| main | | Entry date
cputm | character varying(16) | | |
| extended | | Entry Time
shkzg | character varying(1) | | |
| extended | | Debit/Credit
matnr | character varying(18) | | |
| extended | | Material Code
werks | character varying(4) | | |
| extended | | Plant
prctr | character varying(10) | | |
| extended | | Profit center
menge | character varying(13) | | |
| extended | | GR quantity
wrbtr | numeric(13,2) | | |
| main | | Brick Amount
netpr | numeric(11,2) | | |
| main | | Brick Unit Price
ebeln | character varying(10) | | |
| extended | | Po(H)
ebelp | numeric(5,0) | | |
| main | | Po item(H)
lifnr | character varying(10) | | |
| extended | | Vendor(H)
ltsnr | character varying(10) | | |
| extended | | Manufacture Plant
vbel2 | character varying(10) | | |
| extended | | Sale order(H)
posn2 | numeric(6,0) | | |
| main | | So Item(H)
zzgeo | character varying(50) | | |
| extended | | Geo
zzsgeo | character varying(50) | | |
| extended | | Sub geo
zsreg | character varying(15) | | |
| extended | | Sub Region
land1 | character varying(2) | | |
| extended | | Country
auart | character varying(4) | | |
| extended | | SO Order type(H)
prdha | character varying(18) | | |
| extended | | Product Hierarchy
prdha1 | character varying(1) | | |
| extended | | Product Hierarchy1
prdha4 | character varying(4) | | |
| extended | | Product Hierarchy2
prdha7 | character varying(7) | | |
| extended | | Product Hierarchy3
prdha11 | character varying(11) | | |
| extended | | Product Hierarchy4
prdha15 | character varying(15) | | |
| extended | | Product Hierarchy5
v_name1 | character varying(35) | | |
| extended | | Vendor Name
maktx | character varying(40) | | |
| extended | | Material Name
vtext | character varying(38) | | |
| extended | | PH Description
vtext_1 | character varying(38) | | |
| extended | | PH1 Description
vtext_2 | character varying(38) | | |
| extended | | PH2 Description
vtext_3 | character varying(38) | | |
| extended | | PH3 Description
vtext_4 | character varying(38) | | |
| extended | | PH4 Description
vtext_5 | character varying(38) | | |
| extended | | PH5 Description
so_vtext | character varying(38) | | |
| extended | | Sales org name
sd_vbeln | character varying(10) | | |
| extended | | End Cust So
sd_posnr | numeric(6,0) | | |
| main | | End Cust So item
sd_kunnr | character varying(10) | | |
| extended | | S&D customer
sd_vkorg | character varying(4) | | |
| extended | | S&D sale org
sd_vkbur | character varying(4) | | |
| extended | | S&D sale office
sd_auart | character varying(4) | | |
| extended | | S&D Order type
sd_matnr | character varying(18) | | |
| extended | | S&D material
c_name1 | character varying(35) | | |
| extended | | Customer Nanme
zsbh | character varying(1) | | |
| extended | | Direct/Replenish
bezei | character varying(38) | | |
| extended | | Sales office name
ktext | character varying(38) | | |
| extended | | Profit center name
sys_created_date | timestamp(6) with time zone | | |
| plain | | Sys Created Date
sys_create_by | character varying(20) | | |
| extended | | Sys Created By
id | bigint | | not null | nextval('dcg.brick_shipment_id_seq'::re
gclass) | plain | | ID
buzei | numeric(3,0) | | |
| main | | Doc Item
segment | character varying(40) | | |
| extended | | Segment
zprctr | character varying(10) | | |
| extended | | P&L Profit Center
zdcg_csl1 | character varying(64) | | |
| extended | |
zdcg_csl2 | character varying(64) | | |
| extended | |
zdcg_csl3 | character varying(64) | | |
| extended | |
zprdsg_l1 | character varying(64) | | |
| extended | |
zprodt_l1 | character varying(64) | | |
| extended | |
zzbpcbu | character varying(64) | | |
| extended | |
zbpcser | character varying(64) | | |
| extended | |
Indexes:
"brick_shipment_pkey" PRIMARY KEY, btree (id)
[postgres@slpnmhudtkt-sns backup]$ pg_dump -d ccfdb -s -t dcg.brick_base_ebr
--
-- PostgreSQL database dump
--
-- Dumped from database version 10.3
-- Dumped by pg_dump version 10.3
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: brick_base_ebr; Type: TABLE; Schema: dcg; Owner: i_ds
--
CREATE TABLE dcg.brick_base_ebr (
bill_num character(10) NOT NULL,
bill_item numeric(6,0) NOT NULL,
comp_code character(4) NOT NULL,
ze2e_flg character(1) NOT NULL,
fiscvarnt character(2) NOT NULL,
zsourceid character(10) NOT NULL,
fiscyear numeric(4,0) NOT NULL,
sales_off character(4),
profit_ctr character(10),
doc_type character(4),
doc_number character(10) NOT NULL,
s_ord_item numeric(6,0),
billtoprty character(10),
ship_to character(10),
sold_to character(10),
zsalescat character(10),
plant character(4),
doc_num character(10),
doc_item numeric(6,0),
material character(18),
prod_hier character(18),
zprdhier1 character(21),
zprdhier2 character(23),
zprdhier3 character(23),
zprdhier4 character(24),
zprdhier5 character(24),
currency character(5),
zprdhier6 character(23),
bill_date character(10),
zfiscper numeric(6,0),
fiscper numeric(7,0),
inv_qty numeric(17,3),
zpcacost numeric(17,2),
zcountry1 character(2),
zregion2 character(15),
crm_endcst character(10),
bill_qty numeric(17,3),
ze2e_prt character(10),
zbmccost numeric(17,2),
znet_inv1 numeric(17,2),
zfingeo character(20),
deliv_numb character(10),
deliv_item numeric(6,0),
zshtoctry character(3),
zsotoctry character(3),
zbicountr character(3),
sys_created_by character(20),
sys_created_date timestamp(6) without time zone,
zbpcconty character(32),
zbuel_fin character(30),
mcmtbilldate numeric(8,0),
request character(30),
item_categ character(4),
salesorg character(4),
matl_type character(4),
base_uom character(3),
fiscper3 numeric(3,0),
recordmode character(1),
zcountry2 character(2),
zextsales character(1),
zendcst_s character(10),
zbu_sg_l1 character(20),
zbu_sg_l2 character(40),
zfin_desc text,
zgtm_csl2 character(20),
zgtm_csl1 character(10),
zdcg_csl1 character(20),
zdcg_csl2 character(40),
zdcg_csl3 character(100),
zsolutnl1 character(20),
zsolutnl2 character(40),
zsolutnl3 character(40),
zsolutnl4 character(40),
zzbpcbu character varying(20),
zprodfly character varying(64),
zprodt_l1 character varying(64),
zbpcser character varying(64),
zprdsg_l1 character varying(64)
);
ALTER TABLE dcg.brick_base_ebr OWNER TO i_ds;
--
-- Name: COLUMN brick_base_ebr.doc_number; Type: COMMENT; Schema: dcg; Owner: i_ds
--
COMMENT ON COLUMN dcg.brick_base_ebr.doc_number IS 'so number';
--
-- Name: brick_base_ebr zoh_ees15_pkey; Type: CONSTRAINT; Schema: dcg; Owner: i_ds
--
ALTER TABLE ONLY dcg.brick_base_ebr
ADD CONSTRAINT zoh_ees15_pkey PRIMARY KEY (bill_num, bill_item, comp_code, fiscvarnt, fiscyear, ze2e_flg, zsourceid, doc_number);
--
-- Name: brick_base_ebr insert_brick_base_ebr_partition_trigger; Type: TRIGGER; Schema: dcg; Owner: i_ds
--
CREATE TRIGGER insert_brick_base_ebr_partition_trigger BEFORE INSERT ON dcg.brick_base_ebr FOR EACH ROW EXECUTE PROCEDURE dcg.brick_base_ebr_partition_trigger();
--
-- Name: TABLE brick_base_ebr; Type: ACL; Schema: dcg; Owner: i_ds
--
GRANT SELECT ON TABLE dcg.brick_base_ebr TO p_jiaohx;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_base_ebr TO a_app;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_base_ebr TO p_sunpan1;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_base_ebr TO p_cuiyue3;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_base_ebr TO a_mbgapp_dev;
GRANT SELECT ON TABLE dcg.brick_base_ebr TO p_wangrh8;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_base_ebr TO p_yangxy23;
GRANT ALL ON TABLE dcg.brick_base_ebr TO ccfuser;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_base_ebr TO i_talend;
--
-- PostgreSQL database dump complete
pg_dump -d ccfdb -s -t dcg.brick_shipment
[postgres@slpnmhudtkt-sns backup]$ pg_dump -d ccfdb -s -t dcg.brick_shipment
--
-- PostgreSQL database dump
--
-- Dumped from database version 10.3
-- Dumped by pg_dump version 10.3
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: brick_shipment; Type: TABLE; Schema: dcg; Owner: ccfuser
--
CREATE TABLE dcg.brick_shipment (
bukrs character varying(4),
gjahr numeric(4,0),
monat numeric(2,0),
belnr character varying(10),
budat numeric(8,0),
cpudt numeric(8,0),
cputm character varying(16),
shkzg character varying(1),
matnr character varying(18),
werks character varying(4),
prctr character varying(10),
menge character varying(13),
wrbtr numeric(13,2),
netpr numeric(11,2),
ebeln character varying(10),
ebelp numeric(5,0),
lifnr character varying(10),
ltsnr character varying(10),
vbel2 character varying(10),
posn2 numeric(6,0),
zzgeo character varying(50),
zzsgeo character varying(50),
zsreg character varying(15),
land1 character varying(2),
auart character varying(4),
prdha character varying(18),
prdha1 character varying(1),
prdha4 character varying(4),
prdha7 character varying(7),
prdha11 character varying(11),
prdha15 character varying(15),
v_name1 character varying(35),
maktx character varying(40),
vtext character varying(38),
vtext_1 character varying(38),
vtext_2 character varying(38),
vtext_3 character varying(38),
vtext_4 character varying(38),
vtext_5 character varying(38),
so_vtext character varying(38),
sd_vbeln character varying(10),
sd_posnr numeric(6,0),
sd_kunnr character varying(10),
sd_vkorg character varying(4),
sd_vkbur character varying(4),
sd_auart character varying(4),
sd_matnr character varying(18),
c_name1 character varying(35),
zsbh character varying(1),
bezei character varying(38),
ktext character varying(38),
sys_created_date timestamp(6) with time zone,
sys_create_by character varying(20),
id bigint DEFAULT nextval('dcg.brick_shipment_id_seq'::regclass) NOT NULL,
buzei numeric(3,0),
segment character varying(40),
zprctr character varying(10),
zdcg_csl1 character varying(64),
zdcg_csl2 character varying(64),
zdcg_csl3 character varying(64),
zprdsg_l1 character varying(64),
zprodt_l1 character varying(64),
zzbpcbu character varying(64),
zbpcser character varying(64)
);
ALTER TABLE dcg.brick_shipment OWNER TO ccfuser;
--
-- Name: COLUMN brick_shipment.bukrs; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.bukrs IS 'Company code';
--
-- Name: COLUMN brick_shipment.gjahr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.gjahr IS 'Fiscal year';
--
-- Name: COLUMN brick_shipment.monat; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.monat IS 'Period';
--
-- Name: COLUMN brick_shipment.belnr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.belnr IS 'Accounting Number';
--
-- Name: COLUMN brick_shipment.budat; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.budat IS 'Posting date';
--
-- Name: COLUMN brick_shipment.cpudt; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.cpudt IS 'Entry date';
--
-- Name: COLUMN brick_shipment.cputm; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.cputm IS 'Entry Time';
--
-- Name: COLUMN brick_shipment.shkzg; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.shkzg IS 'Debit/Credit ';
--
-- Name: COLUMN brick_shipment.matnr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.matnr IS 'Material Code';
--
-- Name: COLUMN brick_shipment.werks; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.werks IS 'Plant';
--
-- Name: COLUMN brick_shipment.prctr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.prctr IS 'Profit center';
--
-- Name: COLUMN brick_shipment.menge; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.menge IS 'GR quantity';
--
-- Name: COLUMN brick_shipment.wrbtr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.wrbtr IS 'Brick Amount';
--
-- Name: COLUMN brick_shipment.netpr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.netpr IS 'Brick Unit Price';
--
-- Name: COLUMN brick_shipment.ebeln; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.ebeln IS 'Po(H)';
--
-- Name: COLUMN brick_shipment.ebelp; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.ebelp IS 'Po item(H)';
--
-- Name: COLUMN brick_shipment.lifnr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.lifnr IS 'Vendor(H) ';
--
-- Name: COLUMN brick_shipment.ltsnr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.ltsnr IS 'Manufacture Plant';
--
-- Name: COLUMN brick_shipment.vbel2; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.vbel2 IS 'Sale order(H)';
--
-- Name: COLUMN brick_shipment.posn2; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.posn2 IS 'So Item(H)';
--
-- Name: COLUMN brick_shipment.zzgeo; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.zzgeo IS 'Geo';
--
-- Name: COLUMN brick_shipment.zzsgeo; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.zzsgeo IS 'Sub geo';
--
-- Name: COLUMN brick_shipment.zsreg; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.zsreg IS 'Sub Region';
--
-- Name: COLUMN brick_shipment.land1; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.land1 IS 'Country';
--
-- Name: COLUMN brick_shipment.auart; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.auart IS 'SO Order type(H)';
--
-- Name: COLUMN brick_shipment.prdha; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.prdha IS 'Product Hierarchy ';
--
-- Name: COLUMN brick_shipment.prdha1; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.prdha1 IS 'Product Hierarchy1';
--
-- Name: COLUMN brick_shipment.prdha4; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.prdha4 IS 'Product Hierarchy2';
--
-- Name: COLUMN brick_shipment.prdha7; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.prdha7 IS 'Product Hierarchy3';
--
-- Name: COLUMN brick_shipment.prdha11; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.prdha11 IS 'Product Hierarchy4';
--
-- Name: COLUMN brick_shipment.prdha15; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.prdha15 IS 'Product Hierarchy5';
--
-- Name: COLUMN brick_shipment.v_name1; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.v_name1 IS 'Vendor Name';
--
-- Name: COLUMN brick_shipment.maktx; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.maktx IS 'Material Name';
--
-- Name: COLUMN brick_shipment.vtext; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.vtext IS 'PH Description ';
--
-- Name: COLUMN brick_shipment.vtext_1; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.vtext_1 IS 'PH1 Description ';
--
-- Name: COLUMN brick_shipment.vtext_2; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.vtext_2 IS 'PH2 Description ';
--
-- Name: COLUMN brick_shipment.vtext_3; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.vtext_3 IS 'PH3 Description ';
--
-- Name: COLUMN brick_shipment.vtext_4; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.vtext_4 IS 'PH4 Description ';
--
-- Name: COLUMN brick_shipment.vtext_5; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.vtext_5 IS 'PH5 Description ';
--
-- Name: COLUMN brick_shipment.so_vtext; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.so_vtext IS 'Sales org name';
--
-- Name: COLUMN brick_shipment.sd_vbeln; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.sd_vbeln IS 'End Cust So';
--
-- Name: COLUMN brick_shipment.sd_posnr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.sd_posnr IS 'End Cust So item';
--
-- Name: COLUMN brick_shipment.sd_kunnr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.sd_kunnr IS 'S&D customer';
--
-- Name: COLUMN brick_shipment.sd_vkorg; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.sd_vkorg IS 'S&D sale org';
--
-- Name: COLUMN brick_shipment.sd_vkbur; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.sd_vkbur IS 'S&D sale office';
--
-- Name: COLUMN brick_shipment.sd_auart; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.sd_auart IS 'S&D Order type';
--
-- Name: COLUMN brick_shipment.sd_matnr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.sd_matnr IS 'S&D material';
--
-- Name: COLUMN brick_shipment.c_name1; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.c_name1 IS 'Customer Nanme';
--
-- Name: COLUMN brick_shipment.zsbh; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.zsbh IS 'Direct/Replenish';
--
-- Name: COLUMN brick_shipment.bezei; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.bezei IS 'Sales office name';
--
-- Name: COLUMN brick_shipment.ktext; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.ktext IS 'Profit center name';
--
-- Name: COLUMN brick_shipment.sys_created_date; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.sys_created_date IS 'Sys Created Date';
--
-- Name: COLUMN brick_shipment.sys_create_by; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.sys_create_by IS 'Sys Created By';
--
-- Name: COLUMN brick_shipment.id; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.id IS 'ID';
--
-- Name: COLUMN brick_shipment.buzei; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.buzei IS 'Doc Item';
--
-- Name: COLUMN brick_shipment.segment; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.segment IS 'Segment';
--
-- Name: COLUMN brick_shipment.zprctr; Type: COMMENT; Schema: dcg; Owner: ccfuser
--
COMMENT ON COLUMN dcg.brick_shipment.zprctr IS 'P&L Profit Center';
--
-- Name: brick_shipment brick_shipment_pkey; Type: CONSTRAINT; Schema: dcg; Owner: ccfuser
--
ALTER TABLE ONLY dcg.brick_shipment
ADD CONSTRAINT brick_shipment_pkey PRIMARY KEY (id);
--
-- Name: TABLE brick_shipment; Type: ACL; Schema: dcg; Owner: ccfuser
--
GRANT SELECT ON TABLE dcg.brick_shipment TO p_jiaohx;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_shipment TO i_ds;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_shipment TO a_app;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_shipment TO p_sunpan1;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_shipment TO p_cuiyue3;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_shipment TO a_mbgapp_dev;
GRANT SELECT ON TABLE dcg.brick_shipment TO p_wangrh8;
GRANT SELECT,INSERT,DELETE,TRUNCATE,UPDATE ON TABLE dcg.brick_shipment TO p_yangxy23;
--
-- PostgreSQL database dump complete
--
ccfdb=> \c
You are now connected to database "ccfdb" as user "ccfuser".
ccfdb=> explain SELECT A
.* FROM
(
select * from dcg.brick_base_ebr t1 where 1=1 and t1.ze2e_flg !='J'
and t1.fiscper >='2020007' and '2020007' >=t1.fiscper
and t1.fiscyear = '2020'
)
A LEFT JOIN
(
SELECT
sd_vbeln,
sd_posnr,
wrbtr,
netpr,
matnr
FROM
(
SELECT ROW_NUMBER
() OVER ( PARTITION BY sd_vbeln, sd_posnr ORDER BY A.belnr DESC, A.buzei DESC ) num,*
FROM
dcg.brick_shipment AS A
) AS A
WHERE
num = 1
)
bs ON A.doc_number = bs.sd_vbeln
AND A.s_ord_item = bs.sd_posnr
LEFT JOIN dcg.brick_billing t2
ON
A.bill_num=t2.vbeln
; AND A.bill_item=t2.posnr
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
--------------------------------
Gather (cost=99636.42..163455.57 rows=79187 width=1107)
Workers Planned: 3
-> Hash Left Join (cost=98636.42..154536.87 rows=25544 width=1107)
Hash Cond: ((t1.bill_num = (t2.vbeln)::bpchar) AND (t1.bill_item = t2.posnr))
-> Hash Left Join (cost=60660.29..94891.77 rows=25544 width=1107)
Hash Cond: ((t1.doc_number = (a.sd_vbeln)::bpchar) AND (t1.s_ord_item = a.sd_posnr))
-> Append (cost=0.00..23310.24 rows=25545 width=1107)
-> Parallel Seq Scan on brick_base_ebr t1 (cost=0.00..0.00 rows=1 width=4742)
Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper) AN
D (fiscyear = '2020'::numeric))
-> Parallel Seq Scan on brick_base_ebr_2020007 t1_1 (cost=0.00..23310.24 rows=25544 width=1107)
Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper) AN
D (fiscyear = '2020'::numeric))
-> Hash (cost=60644.50..60644.50 rows=1053 width=15)
-> Subquery Scan on a (cost=52744.22..60644.50 rows=1053 width=15)
Filter: (a.num = 1)
-> WindowAgg (cost=52744.22..58011.07 rows=210674 width=3565)
-> Sort (cost=52744.22..53270.91 rows=210674 width=31)
Sort Key: a_1.sd_vbeln, a_1.sd_posnr, a_1.belnr DESC, a_1.buzei DESC
-> Seq Scan on brick_shipment a_1 (cost=0.00..34115.74 rows=210674 width=31)
-> Hash (cost=29742.85..29742.85 rows=414085 width=16)
-> Seq Scan on brick_billing t2 (cost=0.00..29742.85 rows=414085 width=16)
(20 rows)
ccfdb=> \c ccfdb a_app
You are now connected to database "ccfdb" as user "a_app".
ccfdb=> \c
You are now connected to database "ccfdb" as user "a_app".
ccfdb=> explain SELECT A
.* FROM
(
select * from dcg.brick_base_ebr t1 where 1=1 and t1.ze2e_flg !='J'
and t1.fiscper >='2020007' and '2020007' >=t1.fiscper
and t1.fiscyear = '2020'
)
A LEFT JOIN
(
SELECT
sd_vbeln,
sd_posnr,
wrbtr,
netpr,
matnr
FROM
(
SELECT ROW_NUMBER
() OVER ( PARTITION BY sd_vbeln, sd_posnr ORDER BY A.belnr DESC, A.buzei DESC ) num,*
FROM
dcg.brick_shipment AS A
) AS A
WHERE
num = 1
)
bs ON A.doc_number = bs.sd_vbeln
AND A.s_ord_item = bs.sd_posnr
LEFT JOIN dcg.brick_billing t2
ON
A.bill_num=t2.vbeln
; AND A.bill_item=t2.posnr
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
--------------------------------
Nested Loop Left Join (cost=52752.70..93540.96 rows=2 width=2926)
Join Filter: ((t1.doc_number = (a.sd_vbeln)::bpchar) AND (t1.s_ord_item = a.sd_posnr))
-> Hash Right Join (cost=8.47..32856.97 rows=2 width=2926)
Hash Cond: (((t2.vbeln)::bpchar = t1.bill_num) AND (t2.posnr = t1.bill_item))
-> Seq Scan on brick_billing t2 (cost=0.00..29742.85 rows=414085 width=16)
-> Hash (cost=8.44..8.44 rows=2 width=2924)
-> Append (cost=0.00..8.44 rows=2 width=2924)
-> Seq Scan on brick_base_ebr t1 (cost=0.00..0.00 rows=1 width=4742)
Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper) AN
D (fiscyear = '2020'::numeric))
-> Index Scan using brick_base_ebr_fiscper_2020007 on brick_base_ebr_2020007 t1_1 (cost=0.42..8.44 rows=1 width=1
107)
Index Cond: ((fiscper >= '2020007'::numeric) AND ('2020007'::numeric >= fiscper))
Filter: ((ze2e_flg <> 'J'::bpchar) AND (fiscyear = '2020'::numeric))
-> Materialize (cost=52744.22..60649.76 rows=1053 width=15)
-> Subquery Scan on a (cost=52744.22..60644.50 rows=1053 width=15)
Filter: (a.num = 1)
-> WindowAgg (cost=52744.22..58011.07 rows=210674 width=3565)
-> Sort (cost=52744.22..53270.91 rows=210674 width=31)
Sort Key: a_1.sd_vbeln, a_1.sd_posnr, a_1.belnr DESC, a_1.buzei DESC
-> Seq Scan on brick_shipment a_1 (cost=0.00..34115.74 rows=210674 width=31)
(19 rows)
