with as慢sql临时解决方案

数据表：

DROP TABLE IF EXISTS "gse_test_project";
CREATE TABLE "gse_test_project" (
? "id" numeric(8),
? "project_name" varchar(20) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "project_no" varchar(14) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "save_year" varchar(32) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "pro_num" numeric(8),
? "jjz" numeric(8),
? "ksz" numeric(8),
? "jsz" numeric(8),
? "cksj" timestamp(6),
? "collect_job_id" varchar(32) COLLATE "pg_catalog"."default",
? "publish_org" varchar(32) COLLATE "pg_catalog"."default",
? "uniontime" timestamp(6),
? "input_org" varchar(32) COLLATE "pg_catalog"."default",
? "input_user" varchar(20) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "input_time" timestamp(6),
? "secrecy_level" varchar(3) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "verify_org" varchar(32) COLLATE "pg_catalog"."default",
? "verifier" varchar(20) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "verify_date" timestamp(6),
? "is_submit_centre_db" varchar(1) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "acqtn_mode" varchar(50) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "branch_id" varchar(32) COLLATE "pg_catalog"."default",
? "quality_tag" varchar(50) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "update_org" varchar(32) COLLATE "pg_catalog"."default",
? "update_user" varchar(20) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "update_date" timestamp(6),
? "dataset_class" varchar(14) COLLATE "pg_catalog"."default" DEFAULT ''::character varying
)
;
COMMENT ON COLUMN "gse_test_project"."id" IS 'id';
COMMENT ON COLUMN "gse_test_project"."project_name" IS 'project_name';
COMMENT ON COLUMN "gse_test_project"."project_no" IS 'project_no';
COMMENT ON COLUMN "gse_test_project"."save_year" IS 'save_year';
COMMENT ON COLUMN "gse_test_project"."pro_num" IS 'pro_num';
COMMENT ON COLUMN "gse_test_project"."jjz" IS 'jjz';
COMMENT ON COLUMN "gse_test_project"."ksz" IS 'ksz';
COMMENT ON COLUMN "gse_test_project"."jsz" IS 'jsz';
COMMENT ON COLUMN "gse_test_project"."cksj" IS 'cksj';
COMMENT ON COLUMN "gse_test_project"."collect_job_id" IS '采集任务标识';
COMMENT ON COLUMN "gse_test_project"."publish_org" IS '发布单位';
COMMENT ON COLUMN "gse_test_project"."uniontime" IS '汇集时间';
COMMENT ON COLUMN "gse_test_project"."input_org" IS '录入单位';
COMMENT ON COLUMN "gse_test_project"."input_user" IS '录入人';
COMMENT ON COLUMN "gse_test_project"."input_time" IS '录入时间';
COMMENT ON COLUMN "gse_test_project"."secrecy_level" IS '密级';
COMMENT ON COLUMN "gse_test_project"."verify_org" IS '审核单位';
COMMENT ON COLUMN "gse_test_project"."verifier" IS '审核人';
COMMENT ON COLUMN "gse_test_project"."verify_date" IS '审核时间';
COMMENT ON COLUMN "gse_test_project"."is_submit_centre_db" IS '是提交中心库';
COMMENT ON COLUMN "gse_test_project"."acqtn_mode" IS '数据采集方式';
COMMENT ON COLUMN "gse_test_project"."branch_id" IS '所属分公司';
COMMENT ON COLUMN "gse_test_project"."quality_tag" IS '质量标志';
COMMENT ON COLUMN "gse_test_project"."update_org" IS '最近更新单位';
COMMENT ON COLUMN "gse_test_project"."update_user" IS '最近更新人';
COMMENT ON COLUMN "gse_test_project"."update_date" IS '最近更新时间';
COMMENT ON COLUMN "gse_test_project"."dataset_class" IS '数据集编码';
COMMENT ON TABLE "gse_test_project" IS '数据质量test_project';

-- ----------------------------
-- Records of gse_test_project
-- ----------------------------
INSERT INTO "gse_test_project" VALUES (7, '测试3', '003', '2021-01', 3000, 3000, 3200, 10, NULL, NULL, NULL, NULL, NULL, '', '2023-07-05 14:51:43', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');
INSERT INTO "gse_test_project" VALUES (9, '测试3', '003', '2022-03', 3200, 3000, 3200, 10, NULL, NULL, NULL, NULL, NULL, '', '2023-01-02 14:50:57', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');
INSERT INTO "gse_test_project" VALUES (5, '测试2', '002', '2021-02', 100, 130, 100, 10, '2023-07-05 10:02:58', NULL, NULL, NULL, NULL, '', '2023-09-12 14:51:34', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');
INSERT INTO "gse_test_project" VALUES (6, '测试2', '002', '2021-03', 110, 130, 100, 10, '2023-08-06 10:02:58', NULL, NULL, NULL, NULL, '', '2023-09-07 14:51:39', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');
INSERT INTO "gse_test_project" VALUES (8, '测试2', '002', '2021-05', 130, 130, 100, 10, NULL, NULL, NULL, NULL, NULL, '', '2023-08-09 14:51:49', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');
INSERT INTO "gse_test_project" VALUES (10, '测试2', '002', '2021-04', 120, 130, 100, 10, NULL, NULL, NULL, NULL, NULL, '', '2023-07-01 14:51:21', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');
INSERT INTO "gse_test_project" VALUES (3, '测试1', '001', '2020-11', 10, 110, 10, 10, '2023-07-03 10:02:58', NULL, NULL, NULL, NULL, '', '2023-08-09 14:51:55', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');
INSERT INTO "gse_test_project" VALUES (4, '测试1', '001', '2020-12', 20, 110, 10, 10, '2023-07-04 10:02:58', NULL, NULL, NULL, NULL, '', '2023-07-05 14:51:59', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');
INSERT INTO "gse_test_project" VALUES (11, '测试1', '001', '2021-01', 30, 110, 10, 10, NULL, NULL, NULL, NULL, NULL, '', '2023-07-04 14:51:27', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');
INSERT INTO "gse_test_project" VALUES (2, '测试1', '001', '2021-02', 40, 110, 10, 10, '2023-07-02 10:02:58', NULL, NULL, NULL, NULL, '', '2023-09-06 14:51:06', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');
INSERT INTO "gse_test_project" VALUES (1, '测试1', '001', '2021-03', 100, 110, 10, 10, '2023-07-01 10:02:58', NULL, NULL, NULL, NULL, '', '2023-08-01 14:51:16', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');
INSERT INTO "gse_test_project" VALUES (21, NULL, '001', '2021-03', 100, 110, 10, 10, '2023-07-01 10:02:58', NULL, NULL, NULL, NULL, '', '2023-08-01 14:51:16', '', NULL, '', NULL, '', '', '01987EEAC5EC2A7AB8BBFBF207E3EE3C', '', NULL, '', NULL, 'test_project');

DROP TABLE IF EXISTS "gse_test_zb";
CREATE TABLE "gse_test_zb" (
? "id" numeric(8),
? "lon" varchar(20) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "lat" varchar(20) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "save_date" varchar(20) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "project_id" numeric(8),
? "collect_job_id" varchar(32) COLLATE "pg_catalog"."default",
? "publish_org" varchar(32) COLLATE "pg_catalog"."default",
? "uniontime" timestamp(6),
? "input_org" varchar(32) COLLATE "pg_catalog"."default",
? "input_user" varchar(20) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "input_time" timestamp(6),
? "secrecy_level" varchar(3) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "verify_org" varchar(32) COLLATE "pg_catalog"."default",
? "verifier" varchar(20) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "verify_date" timestamp(6),
? "is_submit_centre_db" varchar(1) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "acqtn_mode" varchar(50) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "branch_id" varchar(32) COLLATE "pg_catalog"."default",
? "quality_tag" varchar(50) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "update_org" varchar(32) COLLATE "pg_catalog"."default",
? "update_user" varchar(20) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "update_date" timestamp(6),
? "dataset_class" varchar(14) COLLATE "pg_catalog"."default" DEFAULT ''::character varying,
? "wwww" varchar(32) COLLATE "pg_catalog"."default",
? "cs_0001" varchar(32) COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "gse_test_zb"."id" IS 'id';
COMMENT ON COLUMN "gse_test_zb"."lon" IS 'lon';
COMMENT ON COLUMN "gse_test_zb"."lat" IS 'lat';
COMMENT ON COLUMN "gse_test_zb"."save_date" IS 'save_date';
COMMENT ON COLUMN "gse_test_zb"."project_id" IS 'project_id';
COMMENT ON COLUMN "gse_test_zb"."collect_job_id" IS '采集任务标识';
COMMENT ON COLUMN "gse_test_zb"."publish_org" IS '发布单位';
COMMENT ON COLUMN "gse_test_zb"."uniontime" IS '汇集时间';
COMMENT ON COLUMN "gse_test_zb"."input_org" IS '录入单位';
COMMENT ON COLUMN "gse_test_zb"."input_user" IS '录入人';
COMMENT ON COLUMN "gse_test_zb"."input_time" IS '录入时间';
COMMENT ON COLUMN "gse_test_zb"."secrecy_level" IS '密级';
COMMENT ON COLUMN "gse_test_zb"."verify_org" IS '审核单位';
COMMENT ON COLUMN "gse_test_zb"."verifier" IS '审核人';
COMMENT ON COLUMN "gse_test_zb"."verify_date" IS '审核时间';
COMMENT ON COLUMN "gse_test_zb"."is_submit_centre_db" IS '是提交中心库';
COMMENT ON COLUMN "gse_test_zb"."acqtn_mode" IS '数据采集方式';
COMMENT ON COLUMN "gse_test_zb"."branch_id" IS '所属分公司';
COMMENT ON COLUMN "gse_test_zb"."quality_tag" IS '质量标志';
COMMENT ON COLUMN "gse_test_zb"."update_org" IS '最近更新单位';
COMMENT ON COLUMN "gse_test_zb"."update_user" IS '最近更新人';
COMMENT ON COLUMN "gse_test_zb"."update_date" IS '最近更新时间';
COMMENT ON COLUMN "gse_test_zb"."dataset_class" IS '数据集编码';
COMMENT ON COLUMN "gse_test_zb"."wwww" IS 'SJJ00A001001';
COMMENT ON COLUMN "gse_test_zb"."cs_0001" IS 'cs_0001';
COMMENT ON TABLE "gse_test_zb" IS '数据质量坐标test_zb';

-- ----------------------------
-- Records of gse_test_zb
-- ----------------------------
INSERT INTO "gse_test_zb" VALUES (1, '123.66072', '34.79977', '2021-03-11 09:00:00', 1, NULL, NULL, NULL, NULL, '', '2023-08-01 10:00:00', '', NULL, '', NULL, '', '', NULL, '', NULL, '', NULL, 'test_zb', NULL, NULL);
INSERT INTO "gse_test_zb" VALUES (6, '113.65', '34.76', '2021-03-11 09:00:00', 6, NULL, NULL, NULL, NULL, '', '2023-08-01 10:00:00', '', NULL, '', NULL, '', '', NULL, '', NULL, '', NULL, 'test_zb', NULL, NULL);
INSERT INTO "gse_test_zb" VALUES (9, '113.65', '34.81', '2021-03-11 09:00:00', 9, NULL, NULL, NULL, NULL, '', '2023-08-01 10:00:00', '', NULL, '', NULL, '', '', NULL, '', NULL, '', NULL, 'test_zb', NULL, NULL);
INSERT INTO "gse_test_zb" VALUES (5, '113.65', '34.76', '2021-03-11 09:00:00', 5, NULL, NULL, NULL, NULL, '', '2023-08-01 10:00:00', '', NULL, '', NULL, '', '', NULL, '', NULL, '', NULL, 'test_zb', NULL, NULL);
INSERT INTO "gse_test_zb" VALUES (2, '113.66072', '350', '2021-03-11 09:00:00', 1, NULL, NULL, NULL, NULL, '', '2023-08-01 10:00:00', '', NULL, '', NULL, '', '', NULL, '', NULL, '', NULL, 'test_zb', NULL, NULL);
INSERT INTO "gse_test_zb" VALUES (3, '113.42175853602474', '34.811229145387216', '2021-03-11 09:00:00', 3, NULL, NULL, NULL, NULL, '', '2023-08-01 10:00:00', '', NULL, '', NULL, '', '', NULL, '', NULL, '', NULL, 'test_zb', NULL, NULL);
INSERT INTO "gse_test_zb" VALUES (4, '113.73812176197008', '34.783202623364886', '2021-03-11 09:00:00', 4, NULL, NULL, NULL, NULL, '', '2023-08-01 10:00:00', '', NULL, '', NULL, '', '', NULL, '', NULL, '', NULL, 'test_zb', NULL, NULL);
INSERT INTO "gse_test_zb" VALUES (7, '113.65', '34.76', '2021-03-11 09:00:00', 7, NULL, NULL, NULL, NULL, '', '2023-08-01 10:00:00', '', NULL, '', NULL, '', '', NULL, '', NULL, '', NULL, 'test_zb', NULL, NULL);
INSERT INTO "gse_test_zb" VALUES (8, '113.40', '34.804753', '2021-03-11 09:00:00', 8, NULL, NULL, NULL, NULL, '', '2023-08-01 10:00:00', '', NULL, '', NULL, '', '', NULL, '', NULL, '', NULL, 'test_zb', NULL, NULL);

慢sql:

?? ? WITH dquality_temp AS ( SELECT * FROM gse_test_zb WHERE dataset_class = 'test_zb' )?
?? ? SELECT *?
FROM
?? ?dquality_temp
?? ?A INNER JOIN gse_test_project B ON B.dataset_class = 'test_project'?
?? ?AND A.project_id = B.
?? ?ID ?INNER JOIN (
?? ?SELECT B.colbm_project_no
?? ?FROM
?? ??? ?( SELECT A.*,
?? ??? ??? ?B.project_no AS colbm_project_no FROM dquality_temp A INNER JOIN gse_test_project B ON A.project_id = B.ID AND B.dataset_class = 'test_project' ) A?
?? ??? ?INNER JOIN ( SELECT A.*,
?? ??? ??? ?B.project_no AS colbm_project_no FROM dquality_temp A INNER JOIN gse_test_project B ON A.project_id = B.ID AND B.dataset_class = 'test_project' ) B?
?? ??? ??? ?ON A.colbm_project_no = B.colbm_project_no ?? ??? ?
?? ??? ??? ? AND A.save_date = B.save_date
?? ??? ?AND A.ID != B.ID?
?? ?GROUP BY
?? ??? ?B.colbm_project_no
?? ?) C ON B.project_no = C.colbm_project_no

如果去掉标红行，结果几秒返回。不去掉标红行，结果执行几十分钟。找不到原因

解决办法，去掉with as弄成子查询

?SELECT *?
FROM
gse_test_zb
?? ?A INNER JOIN gse_test_project B ON B.dataset_class = 'test_project'?
?? ?AND A.project_id = B.
?? ?ID ?INNER JOIN (
?? ?SELECT B.colbm_project_no
?? ?FROM
?? ??? ?( SELECT A.*,
?? ??? ??? ?B.project_no AS colbm_project_no FROM gse_test_zb A INNER JOIN gse_test_project B ON A.project_id = B.ID AND B.dataset_class = 'test_project' ) A?
?? ??? ?INNER JOIN ( SELECT A.*,
?? ??? ??? ?B.project_no AS colbm_project_no FROM gse_test_zb A INNER JOIN gse_test_project B ON A.project_id = B.ID AND B.dataset_class = 'test_project' ) B?
?? ??? ??? ?ON A.colbm_project_no = B.colbm_project_no ?? ??? ?
?? ??? ??? ? AND A.save_date = B.save_date
?? ??? ?AND A.ID != B.ID?
?? ?GROUP BY
?? ??? ?B.colbm_project_no
?? ?) C ON B.project_no = C.colbm_project_no
?? ?

?? ?