一个优秀的IDEA插件-Paw Index Advisor

/****************************************************************\
 *                                                               *
 *          Indexes by Paw Index Advisor                  *
 *                                                               *
 *            Powered by Paw(2021 - )                       *
 *                                                               *
\****************************************************************/


-- Existing indexes
/* 
CREATE INDEX CUSTOMER_C_NATIONKEY_FKEY ON CUSTOMER(C_NATIONKEY);
CREATE INDEX CUSTOMER_PKEY ON CUSTOMER(C_CUSTKEY);
CREATE INDEX MYINDEX ON CUSTOMER(C_NAME,C_PHONE);
CREATE INDEX LINEITEM_L_ORDERKEY_FKEY ON LINEITEM(L_ORDERKEY);
CREATE INDEX LINEITEM_L_PARTKEY_L_SUPPKEY_FKEY ON LINEITEM(L_SUPPKEY,L_PARTKEY);
CREATE INDEX LINEITEM_PKEY ON LINEITEM(L_ORDERKEY,L_LINENUMBER);
CREATE INDEX NATION_N_REGIONKEY_FKEY ON NATION(N_REGIONKEY);
CREATE INDEX NATION_PKEY ON NATION(N_NATIONKEY);
CREATE INDEX ORDERS_O_CUSTKEY_FKEY ON ORDERS(O_CUSTKEY);
CREATE INDEX ORDERS_PKEY ON ORDERS(O_ORDERKEY);
CREATE INDEX PART_PKEY ON PART(P_PARTKEY);
CREATE INDEX PARTSUPP_PKEY ON PARTSUPP(PS_SUPPKEY,PS_PARTKEY);
CREATE INDEX PARTSUPP_PS_PARTKEY_FKEY ON PARTSUPP(PS_PARTKEY);
CREATE INDEX PARTSUPP_PS_SUPPKEY_FKEY ON PARTSUPP(PS_SUPPKEY);
CREATE INDEX REGION_PKEY ON REGION(R_REGIONKEY);
CREATE INDEX SUPPLIER_PKEY ON SUPPLIER(S_SUPPKEY);
CREATE INDEX SUPPLIER_S_NATIONKEY_FKEY ON SUPPLIER(S_NATIONKEY);
*/
-- Recommended indexes (After deduplication with existing indexes above)
/*
CREATE INDEX SQLLAB_IDX1496549982 ON NATION(N_NATIONKEY,N_NAME);
CREATE INDEX SQLLAB_IDX1049412868 ON NATION(N_NAME,N_NATIONKEY,N_REGIONKEY);
CREATE INDEX SQLLAB_IDX1640274034 ON NATION(N_NATIONKEY,N_REGIONKEY,N_NAME);
CREATE INDEX SQLLAB_IDX1968327707 ON PARTSUPP(PS_SUPPKEY,PS_PARTKEY,PS_SUPPLYCOST);
CREATE INDEX SQLLAB_IDX1794855777 ON PARTSUPP(PS_SUPPLYCOST,PS_SUPPKEY,PS_PARTKEY);
CREATE INDEX SQLLAB_IDX1797601124 ON PARTSUPP(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST,PS_AVAILQTY);
CREATE INDEX SQLLAB_IDX1400713053 ON PARTSUPP(PS_SUPPKEY,PS_SUPPLYCOST,PS_AVAILQTY,PS_PARTKEY);
CREATE INDEX SQLLAB_IDX0949237515 ON PART(P_PARTKEY,P_TYPE);
CREATE INDEX SQLLAB_IDX0306025125 ON PART(P_NAME,P_PARTKEY);
CREATE INDEX SQLLAB_IDX1038398061 ON PART(P_PARTKEY,P_NAME);
CREATE INDEX SQLLAB_IDX0711368375 ON PART(P_TYPE,P_PARTKEY);
CREATE INDEX SQLLAB_IDX0224144590 ON PART(P_SIZE,P_BRAND,P_TYPE);
CREATE INDEX SQLLAB_IDX1352615704 ON PART(P_BRAND,P_CONTAINER,P_PARTKEY);
CREATE INDEX SQLLAB_IDX1922219213 ON PART(P_SIZE,P_PARTKEY,P_TYPE,P_BRAND);
CREATE INDEX SQLLAB_IDX0558549761 ON ORDERS(O_ORDERPRIORITY,O_ORDERDATE);
CREATE INDEX SQLLAB_IDX0427958114 ON ORDERS(O_ORDERKEY,O_ORDERPRIORITY);
CREATE INDEX SQLLAB_IDX1621082330 ON ORDERS(O_ORDERSTATUS,O_ORDERKEY);
CREATE INDEX SQLLAB_IDX0685567197 ON ORDERS(O_CUSTKEY,O_COMMENT,O_ORDERKEY);
CREATE INDEX SQLLAB_IDX1965289424 ON ORDERS(O_TOTALPRICE,O_ORDERDATE,O_ORDERKEY,O_CUSTKEY);
CREATE INDEX SQLLAB_IDX1852359742 ON ORDERS(O_ORDERDATE,O_ORDERKEY,O_CUSTKEY,O_SHIPPRIORITY);
CREATE INDEX SQLLAB_IDX1352808126 ON ORDERS(O_ORDERKEY,O_CUSTKEY,O_TOTALPRICE,O_ORDERDATE);
CREATE INDEX SQLLAB_IDX1017563074 ON ORDERS(O_ORDERKEY,O_CUSTKEY,O_ORDERDATE,O_SHIPPRIORITY);
CREATE INDEX SQLLAB_IDX0485564461 ON CUSTOMER(C_CUSTKEY,C_NAME);
CREATE INDEX SQLLAB_IDX1620687743 ON CUSTOMER(C_NATIONKEY,C_CUSTKEY);
CREATE INDEX SQLLAB_IDX1365836084 ON CUSTOMER(C_MKTSEGMENT,C_CUSTKEY);
CREATE INDEX SQLLAB_IDX1000938250 ON SUPPLIER(S_ACCTBAL);
CREATE INDEX SQLLAB_IDX0895928642 ON SUPPLIER(S_NAME,S_SUPPKEY);
CREATE INDEX SQLLAB_IDX1908606279 ON SUPPLIER(S_SUPPKEY,S_NAME);
CREATE INDEX SQLLAB_IDX0339323878 ON SUPPLIER(S_COMMENT,S_SUPPKEY);
CREATE INDEX SQLLAB_IDX0864193542 ON SUPPLIER(S_NAME,S_NATIONKEY,S_SUPPKEY,S_ADDRESS);
CREATE INDEX SQLLAB_IDX1003493942 ON SUPPLIER(S_NATIONKEY,S_SUPPKEY,S_NAME,S_ADDRESS);
CREATE INDEX SQLLAB_IDX1409884827 ON SUPPLIER(S_SUPPKEY,S_PHONE,S_NAME,S_ADDRESS);
CREATE INDEX SQLLAB_IDX1264424863 ON LINEITEM(L_SHIPDATE);
CREATE INDEX SQLLAB_IDX1887590273 ON LINEITEM(L_RETURNFLAG,L_LINESTATUS);
CREATE INDEX SQLLAB_IDX0538538531 ON LINEITEM(L_ORDERKEY,L_SUPPKEY);
CREATE INDEX SQLLAB_IDX1716298676 ON LINEITEM(L_ORDERKEY,L_QUANTITY);
CREATE INDEX SQLLAB_IDX0005546689 ON LINEITEM(L_SHIPMODE,L_ORDERKEY);
CREATE INDEX SQLLAB_IDX0607681583 ON LINEITEM(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX0755170250 ON LINEITEM(L_SUPPKEY,L_ORDERKEY,L_SHIPDATE);
CREATE INDEX SQLLAB_IDX2135225760 ON LINEITEM(L_ORDERKEY,L_RECEIPTDATE,L_COMMITDATE);
CREATE INDEX SQLLAB_IDX0499202881 ON LINEITEM(L_PARTKEY,L_SUPPKEY,L_ORDERKEY);
CREATE INDEX SQLLAB_IDX0863873348 ON LINEITEM(L_SUPPKEY,L_SHIPDATE,L_DISCOUNT,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1416165687 ON LINEITEM(L_SUPPKEY,L_ORDERKEY,L_RECEIPTDATE,L_COMMITDATE);
CREATE INDEX SQLLAB_IDX1050685658 ON LINEITEM(L_PARTKEY,L_SHIPDATE,L_DISCOUNT,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1285498835 ON LINEITEM(L_PARTKEY,L_SUPPKEY,L_SHIPDATE,L_QUANTITY);
CREATE INDEX SQLLAB_IDX1728355301 ON LINEITEM(L_SUPPKEY,L_ORDERKEY,L_DISCOUNT,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1895683859 ON LINEITEM(L_DISCOUNT,L_SHIPDATE,L_QUANTITY,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX0394351801 ON LINEITEM(L_ORDERKEY,L_SHIPDATE,L_DISCOUNT,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1120442220 ON LINEITEM(L_RETURNFLAG,L_ORDERKEY,L_DISCOUNT,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX0989057909 ON REGION(R_NAME,R_REGIONKEY);
*/
-- After what-if analaysis validation
CREATE INDEX SQLLAB_IDX1640274034 ON NATION(N_NATIONKEY,N_REGIONKEY,N_NAME);
CREATE INDEX SQLLAB_IDX1120442220 ON LINEITEM(L_RETURNFLAG,L_ORDERKEY,L_DISCOUNT,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1003493942 ON SUPPLIER(S_NATIONKEY,S_SUPPKEY,S_NAME,S_ADDRESS);
CREATE INDEX SQLLAB_IDX1852359742 ON ORDERS(O_ORDERDATE,O_ORDERKEY,O_CUSTKEY,O_SHIPPRIORITY);
CREATE INDEX SQLLAB_IDX1285498835 ON LINEITEM(L_PARTKEY,L_SUPPKEY,L_SHIPDATE,L_QUANTITY);
CREATE INDEX SQLLAB_IDX1728355301 ON LINEITEM(L_SUPPKEY,L_ORDERKEY,L_DISCOUNT,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1716298676 ON LINEITEM(L_ORDERKEY,L_QUANTITY);
CREATE INDEX SQLLAB_IDX0711368375 ON PART(P_TYPE,P_PARTKEY);
CREATE INDEX SQLLAB_IDX0538538531 ON LINEITEM(L_ORDERKEY,L_SUPPKEY);
CREATE INDEX SQLLAB_IDX0607681583 ON LINEITEM(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1797601124 ON PARTSUPP(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST,PS_AVAILQTY);
CREATE INDEX SQLLAB_IDX0949237515 ON PART(P_PARTKEY,P_TYPE);
CREATE INDEX SQLLAB_IDX0394351801 ON LINEITEM(L_ORDERKEY,L_SHIPDATE,L_DISCOUNT,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX0485564461 ON CUSTOMER(C_CUSTKEY,C_NAME);
CREATE INDEX SQLLAB_IDX1409884827 ON SUPPLIER(S_SUPPKEY,S_PHONE,S_NAME,S_ADDRESS);
CREATE INDEX SQLLAB_IDX1416165687 ON LINEITEM(L_SUPPKEY,L_ORDERKEY,L_RECEIPTDATE,L_COMMITDATE);
CREATE INDEX SQLLAB_IDX1365836084 ON CUSTOMER(C_MKTSEGMENT,C_CUSTKEY);
CREATE INDEX SQLLAB_IDX1352615704 ON PART(P_BRAND,P_CONTAINER,P_PARTKEY);
CREATE INDEX SQLLAB_IDX1621082330 ON ORDERS(O_ORDERSTATUS,O_ORDERKEY);
CREATE INDEX SQLLAB_IDX1017563074 ON ORDERS(O_ORDERKEY,O_CUSTKEY,O_ORDERDATE,O_SHIPPRIORITY);
CREATE INDEX SQLLAB_IDX1968327707 ON PARTSUPP(PS_SUPPKEY,PS_PARTKEY,PS_SUPPLYCOST);
CREATE INDEX SQLLAB_IDX1895683859 ON LINEITEM(L_DISCOUNT,L_SHIPDATE,L_QUANTITY,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1264424863 ON LINEITEM(L_SHIPDATE);
CREATE INDEX SQLLAB_IDX1400713053 ON PARTSUPP(PS_SUPPKEY,PS_SUPPLYCOST,PS_AVAILQTY,PS_PARTKEY);
CREATE INDEX SQLLAB_IDX0427958114 ON ORDERS(O_ORDERKEY,O_ORDERPRIORITY);
CREATE INDEX SQLLAB_IDX1922219213 ON PART(P_SIZE,P_PARTKEY,P_TYPE,P_BRAND);
CREATE INDEX SQLLAB_IDX0989057909 ON REGION(R_NAME,R_REGIONKEY);
CREATE INDEX SQLLAB_IDX2135225760 ON LINEITEM(L_ORDERKEY,L_RECEIPTDATE,L_COMMITDATE);
复制

/****************************************************************\
 *                                                               *
 *          Recommended Index Validation Details                 *
 *                                                               *
 *            Powered by PawSQL(2021 - )                         *
 *                                                               *
\****************************************************************/
-- Recommended indexes
CREATE INDEX SQLLAB_IDX1640274034 ON NATION(N_NATIONKEY,N_REGIONKEY,N_NAME);
CREATE INDEX SQLLAB_IDX1120442220 ON LINEITEM(L_RETURNFLAG,L_ORDERKEY,L_DISCOUNT,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1003493942 ON SUPPLIER(S_NATIONKEY,S_SUPPKEY,S_NAME,S_ADDRESS);
CREATE INDEX SQLLAB_IDX1852359742 ON ORDERS(O_ORDERDATE,O_ORDERKEY,O_CUSTKEY,O_SHIPPRIORITY);
CREATE INDEX SQLLAB_IDX1285498835 ON LINEITEM(L_PARTKEY,L_SUPPKEY,L_SHIPDATE,L_QUANTITY);
CREATE INDEX SQLLAB_IDX1728355301 ON LINEITEM(L_SUPPKEY,L_ORDERKEY,L_DISCOUNT,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1716298676 ON LINEITEM(L_ORDERKEY,L_QUANTITY);
CREATE INDEX SQLLAB_IDX0711368375 ON PART(P_TYPE,P_PARTKEY);
CREATE INDEX SQLLAB_IDX0538538531 ON LINEITEM(L_ORDERKEY,L_SUPPKEY);
CREATE INDEX SQLLAB_IDX0607681583 ON LINEITEM(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1797601124 ON PARTSUPP(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST,PS_AVAILQTY);
CREATE INDEX SQLLAB_IDX0949237515 ON PART(P_PARTKEY,P_TYPE);
CREATE INDEX SQLLAB_IDX0394351801 ON LINEITEM(L_ORDERKEY,L_SHIPDATE,L_DISCOUNT,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX0485564461 ON CUSTOMER(C_CUSTKEY,C_NAME);
CREATE INDEX SQLLAB_IDX1409884827 ON SUPPLIER(S_SUPPKEY,S_PHONE,S_NAME,S_ADDRESS);
CREATE INDEX SQLLAB_IDX1416165687 ON LINEITEM(L_SUPPKEY,L_ORDERKEY,L_RECEIPTDATE,L_COMMITDATE);
CREATE INDEX SQLLAB_IDX1365836084 ON CUSTOMER(C_MKTSEGMENT,C_CUSTKEY);
CREATE INDEX SQLLAB_IDX1352615704 ON PART(P_BRAND,P_CONTAINER,P_PARTKEY);
CREATE INDEX SQLLAB_IDX1621082330 ON ORDERS(O_ORDERSTATUS,O_ORDERKEY);
CREATE INDEX SQLLAB_IDX1017563074 ON ORDERS(O_ORDERKEY,O_CUSTKEY,O_ORDERDATE,O_SHIPPRIORITY);
CREATE INDEX SQLLAB_IDX1968327707 ON PARTSUPP(PS_SUPPKEY,PS_PARTKEY,PS_SUPPLYCOST);
CREATE INDEX SQLLAB_IDX1895683859 ON LINEITEM(L_DISCOUNT,L_SHIPDATE,L_QUANTITY,L_EXTENDEDPRICE);
CREATE INDEX SQLLAB_IDX1264424863 ON LINEITEM(L_SHIPDATE);
CREATE INDEX SQLLAB_IDX1400713053 ON PARTSUPP(PS_SUPPKEY,PS_SUPPLYCOST,PS_AVAILQTY,PS_PARTKEY);
CREATE INDEX SQLLAB_IDX0427958114 ON ORDERS(O_ORDERKEY,O_ORDERPRIORITY);
CREATE INDEX SQLLAB_IDX1922219213 ON PART(P_SIZE,P_PARTKEY,P_TYPE,P_BRAND);
CREATE INDEX SQLLAB_IDX0989057909 ON REGION(R_NAME,R_REGIONKEY);
CREATE INDEX SQLLAB_IDX2135225760 ON LINEITEM(L_ORDERKEY,L_RECEIPTDATE,L_COMMITDATE);
-- Validation details
-- 17.sql1, performance improves by 4318.409090909091%[cost_before=583.23,after_cost=13.2]
Contributing indice:[SQLLAB_IDX1352615704, SQLLAB_IDX0607681583, SQLLAB_IDX1285498835]
SELECT SUM(L_EXTENDEDPRICE)  7.0 AS AVG_YEARLY FROM LINEITEM, PART WHERE P_PARTKEY = L_PARTKEY AND P_BRAND = 'BRAND#44' AND P_CONTAINER = 'WRAP PKG' AND L_QUANTITY < ( SELECT 0.2 * AVG(L_QUANTITY) FROM LINEITEM WHERE L_PARTKEY = P_PARTKEY );
-- 21.sql1, performance improves by 1173.2289478709183%[cost_before=1333.58,after_cost=104.74]
Contributing indice:[SQLLAB_IDX1621082330, SQLLAB_IDX1003493942, SQLLAB_IDX1416165687, IDX_LINEITEM_ORDERKEY, SQLLAB_IDX0538538531]
SELECT S_NAME, COUNT(*) AS NUMWAIT FROM SUPPLIER, LINEITEM L1, ORDERS, NATION WHERE S_SUPPKEY = L1.L_SUPPKEY AND O_ORDERKEY = L1.L_ORDERKEY AND O_ORDERSTATUS = 'F' AND L1.L_RECEIPTDATE > L1.L_COMMITDATE AND EXISTS ( SELECT * FROM LINEITEM L2 WHERE L2.L_ORDERKEY = L1.L_ORDERKEY AND L2.L_SUPPKEY <> L1.L_SUPPKEY ) AND NOT EXISTS ( SELECT * FROM LINEITEM L3 WHERE L3.L_ORDERKEY = L1.L_ORDERKEY AND L3.L_SUPPKEY <> L1.L_SUPPKEY AND L3.L_RECEIPTDATE > L3.L_COMMITDATE ) AND S_NATIONKEY = N_NATIONKEY AND N_NAME = 'EGYPT' GROUP BY S_NAME ORDER BY NUMWAIT DESC, S_NAME LIMIT 100;
-- 7.sql1, performance improves by 3877.8084714548804%[cost_before=863.98,after_cost=21.72]
Contributing indice:[SQLLAB_IDX1264424863, SQLLAB_IDX1409884827, SQLLAB_IDX1017563074, CUSTOMER_PKEY, SQLLAB_IDX1640274034, SQLLAB_IDX1640274034]
SELECT SUPP_NATION, CUST_NATION, L_YEAR, SUM(VOLUME) AS REVENUE FROM ( SELECT N1.N_NAME AS SUPP_NATION, N2.N_NAME AS CUST_NATION, EXTRACT(YEAR FROM L_SHIPDATE) AS L_YEAR, L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS VOLUME FROM SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2 WHERE S_SUPPKEY = L_SUPPKEY AND O_ORDERKEY = L_ORDERKEY AND C_CUSTKEY = O_CUSTKEY AND S_NATIONKEY = N1.N_NATIONKEY AND C_NATIONKEY = N2.N_NATIONKEY AND ( (N1.N_NAME = 'JAPAN' AND N2.N_NAME = 'INDIA') OR (N1.N_NAME = 'INDIA' AND N2.N_NAME = 'JAPAN') ) AND L_SHIPDATE BETWEEN DATE '1995-01-01' AND DATE '1996-12-31' ) AS SHIPPING GROUP BY SUPP_NATION, CUST_NATION, L_YEAR ORDER BY SUPP_NATION, CUST_NATION, L_YEAR;
-- 14.sql1, performance improves by 8417.167721518987%[cost_before=1076.57,after_cost=12.64]
Contributing indice:[SQLLAB_IDX1264424863, SQLLAB_IDX0949237515]
SELECT 100.00 * SUM(CASE WHEN P_TYPE LIKE 'PROMO%' THEN L_EXTENDEDPRICE * (1 - L_DISCOUNT) ELSE 0 END)  SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS PROMO_REVENUE FROM LINEITEM, PART WHERE L_PARTKEY = P_PARTKEY AND L_SHIPDATE >= DATE '1996-12-01' AND L_SHIPDATE < DATE '1996-12-01' + INTERVAL '1' MONTH;
-- 4.sql1, performance improves by 5272.832369942196%[cost_before=650.65,after_cost=12.11]
Contributing indice:[SQLLAB_IDX1852359742, SQLLAB_IDX2135225760]
SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT FROM ORDERS WHERE O_ORDERDATE >= DATE '1995-01-01' AND O_ORDERDATE < DATE '1995-01-01' + INTERVAL '3' MONTH AND EXISTS ( SELECT * FROM LINEITEM WHERE L_ORDERKEY = O_ORDERKEY AND L_COMMITDATE < L_RECEIPTDATE ) GROUP BY O_ORDERPRIORITY ORDER BY O_ORDERPRIORITY;
-- 10.sql1, performance improves by 3783.790087463558%[cost_before=666.07,after_cost=17.15]
Contributing indice:[SQLLAB_IDX1852359742, SQLLAB_IDX0485564461, SQLLAB_IDX1120442220, SQLLAB_IDX1640274034]
SELECT C_CUSTKEY, C_NAME, SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE, C_ACCTBAL, N_NAME, C_ADDRESS, C_PHONE, C_COMMENT FROM CUSTOMER, ORDERS, LINEITEM, NATION WHERE C_CUSTKEY = O_CUSTKEY AND L_ORDERKEY = O_ORDERKEY AND O_ORDERDATE >= DATE '1993-08-01' AND O_ORDERDATE < DATE '1993-08-01' + INTERVAL '3' MONTH AND L_RETURNFLAG = 'R' AND C_NATIONKEY = N_NATIONKEY GROUP BY C_CUSTKEY, C_NAME, C_ACCTBAL, C_PHONE, N_NAME, C_ADDRESS, C_COMMENT ORDER BY REVENUE DESC LIMIT 20;
-- 15.sql1, performance improves by 8364.229559748428%[cost_before=1076.65,after_cost=12.72]
Contributing indice:[SQLLAB_IDX1264424863, SQLLAB_IDX1409884827]
WITH REVENUE0 (SUPPLIER_NO, TOTAL_REVENUE) AS ( SELECT L_SUPPKEY, SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) FROM LINEITEM WHERE L_SHIPDATE >= DATE '1997-07-01' AND L_SHIPDATE < DATE '1997-07-01' + INTERVAL '3' MONTH GROUP BY L_SUPPKEY ) SELECT S_SUPPKEY, S_NAME, S_ADDRESS, S_PHONE, TOTAL_REVENUE FROM SUPPLIER, REVENUE0 WHERE S_SUPPKEY = SUPPLIER_NO AND TOTAL_REVENUE = ( SELECT MAX(TOTAL_REVENUE) FROM REVENUE0 ) ORDER BY S_SUPPKEY;
-- 20.sql1, performance improves by 21.872536535079735%[cost_before=200.98,after_cost=164.91]
Contributing indice:[SQLLAB_IDX1797601124, SQLLAB_IDX1285498835, SUPPLIER_PKEY, SQLLAB_IDX1640274034]
SELECT S_NAME, S_ADDRESS FROM SUPPLIER, NATION WHERE S_SUPPKEY IN ( SELECT PS_SUPPKEY FROM PARTSUPP WHERE PS_PARTKEY IN ( SELECT P_PARTKEY FROM PART WHERE P_NAME LIKE 'GREEN%' ) AND PS_AVAILQTY > ( SELECT 0.5 * SUM(L_QUANTITY) FROM LINEITEM WHERE L_PARTKEY = PS_PARTKEY AND L_SUPPKEY = PS_SUPPKEY AND L_SHIPDATE >= DATE '1993-01-01' AND L_SHIPDATE < DATE '1993-01-01' + INTERVAL '1' YEAR ) ) AND S_NATIONKEY = N_NATIONKEY AND N_NAME = 'ALGERIA' ORDER BY S_NAME;
-- 2.sql1, performance improves by 304.29413932915594%[cost_before=219.37,after_cost=54.26]
Contributing indice:[SQLLAB_IDX1797601124, SQLLAB_IDX0989057909, SQLLAB_IDX1003493942, SQLLAB_IDX1968327707, SUPPLIER_PKEY, SQLLAB_IDX1640274034, SQLLAB_IDX0989057909]
SELECT S_ACCTBAL, S_NAME, N_NAME, P_PARTKEY, P_MFGR, S_ADDRESS, S_PHONE, S_COMMENT FROM PART, SUPPLIER, PARTSUPP, NATION, REGION WHERE P_PARTKEY = PS_PARTKEY AND S_SUPPKEY = PS_SUPPKEY AND P_SIZE = 30 AND P_TYPE LIKE '%STEEL' AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY AND R_NAME = 'ASIA' AND PS_SUPPLYCOST = ( SELECT MIN(PS_SUPPLYCOST) FROM PARTSUPP, SUPPLIER, NATION, REGION WHERE P_PARTKEY = PS_PARTKEY AND S_SUPPKEY = PS_SUPPKEY AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY AND R_NAME = 'ASIA' ) ORDER BY S_ACCTBAL DESC, N_NAME, S_NAME, P_PARTKEY LIMIT 100;
-- 12.sql1, performance improves by 2526.340279182683%[cost_before=1298.2,after_cost=49.43]
Contributing indice:[SQLLAB_IDX0427958114]
SELECT L_SHIPMODE, SUM(CASE WHEN O_ORDERPRIORITY = '1-URGENT' OR O_ORDERPRIORITY = '2-HIGH' THEN 1 ELSE 0 END) AS HIGH_LINE_COUNT, SUM(CASE WHEN O_ORDERPRIORITY <> '1-URGENT' AND O_ORDERPRIORITY <> '2-HIGH' THEN 1 ELSE 0 END) AS LOW_LINE_COUNT FROM ORDERS, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY AND L_SHIPMODE IN ('RAIL', 'FOB') AND L_COMMITDATE < L_RECEIPTDATE AND L_SHIPDATE < L_COMMITDATE AND L_RECEIPTDATE >= DATE '1997-01-01' AND L_RECEIPTDATE < DATE '1997-01-01' + INTERVAL '1' YEAR GROUP BY L_SHIPMODE ORDER BY L_SHIPMODE;
-- 5.sql1, performance improves by 295.4041204437401%[cost_before=74.85,after_cost=18.93]
Contributing indice:[SQLLAB_IDX1852359742, SQLLAB_IDX0485564461, SQLLAB_IDX1640274034, SQLLAB_IDX0989057909, SQLLAB_IDX1003493942, SQLLAB_IDX1728355301]
SELECT N_NAME, SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE FROM CUSTOMER, ORDERS, LINEITEM, SUPPLIER, NATION, REGION WHERE C_CUSTKEY = O_CUSTKEY AND L_ORDERKEY = O_ORDERKEY AND L_SUPPKEY = S_SUPPKEY AND C_NATIONKEY = S_NATIONKEY AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY AND R_NAME = 'MIDDLE EAST' AND O_ORDERDATE >= DATE '1994-01-01' AND O_ORDERDATE < DATE '1994-01-01' + INTERVAL '1' YEAR GROUP BY N_NAME ORDER BY REVENUE DESC;
-- 18.sql1, performance improves by 6.347088314693402%[cost_before=2757.58,after_cost=2593.0]
Contributing indice:[SQLLAB_IDX0485564461, SQLLAB_IDX1716298676, SQLLAB_IDX1716298676]
SELECT C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE, SUM(L_QUANTITY) FROM CUSTOMER, ORDERS, LINEITEM WHERE O_ORDERKEY IN ( SELECT L_ORDERKEY FROM LINEITEM GROUP BY L_ORDERKEY HAVING SUM(L_QUANTITY) > 314 ) AND C_CUSTKEY = O_CUSTKEY AND O_ORDERKEY = L_ORDERKEY GROUP BY C_NAME, C_CUSTKEY, O_ORDERKEY, O_ORDERDATE, O_TOTALPRICE ORDER BY O_TOTALPRICE DESC, O_ORDERDATE LIMIT 100;
-- 8.sql1, performance improves by 213.35274542429286%[cost_before=75.33,after_cost=24.04]
Contributing indice:[SQLLAB_IDX1852359742, SQLLAB_IDX0485564461, SQLLAB_IDX1640274034, SQLLAB_IDX0989057909, IDX_LINEITEM_ORDERKEY, SQLLAB_IDX0711368375, SUPPLIER_PKEY, SQLLAB_IDX1640274034]
SELECT O_YEAR, SUM(CASE WHEN NATION = 'INDIA' THEN VOLUME ELSE 0 END)  SUM(VOLUME) AS MKT_SHARE FROM ( SELECT EXTRACT(YEAR FROM O_ORDERDATE) AS O_YEAR, L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS VOLUME, N2.N_NAME AS NATION FROM PART, SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2, REGION WHERE P_PARTKEY = L_PARTKEY AND S_SUPPKEY = L_SUPPKEY AND L_ORDERKEY = O_ORDERKEY AND O_CUSTKEY = C_CUSTKEY AND C_NATIONKEY = N1.N_NATIONKEY AND N1.N_REGIONKEY = R_REGIONKEY AND R_NAME = 'ASIA' AND S_NATIONKEY = N2.N_NATIONKEY AND O_ORDERDATE BETWEEN DATE '1995-01-01' AND DATE '1996-12-31' AND P_TYPE = 'SMALL PLATED COPPER' ) AS ALL_NATIONS GROUP BY O_YEAR ORDER BY O_YEAR;
-- 6.sql1, performance improves by 25543.53876739562%[cost_before=1289.87,after_cost=5.03]
Contributing indice:[SQLLAB_IDX1895683859]
SELECT SUM(L_EXTENDEDPRICE * L_DISCOUNT) AS REVENUE FROM LINEITEM WHERE L_SHIPDATE >= DATE '1994-01-01' AND L_SHIPDATE < DATE '1994-01-01' + INTERVAL '1' YEAR AND L_DISCOUNT BETWEEN 0.06 - 0.01 AND 0.06 + 0.01 AND L_QUANTITY < 24;
-- 13.sql1, performance improves by 0.0%[cost_before=1680.85,after_cost=1680.85]
Contributing indice:[CUSTOMER_PKEY]
SELECT C_COUNT, COUNT(*) AS CUSTDIST FROM ( SELECT C_CUSTKEY, COUNT(O_ORDERKEY) AS C_COUNT FROM CUSTOMER LEFT OUTER JOIN ORDERS ON C_CUSTKEY = O_CUSTKEY AND O_COMMENT NOT LIKE '%PENDING%DEPOSITS%' GROUP BY C_CUSTKEY ) C_ORDERS GROUP BY C_COUNT ORDER BY CUSTDIST DESC, C_COUNT DESC;
-- 16.sql1, performance improves by 10.046450919421083%[cost_before=1433.3,after_cost=1302.45]
Contributing indice:[PARTSUPP_PKEY, SQLLAB_IDX1922219213]
SELECT P_BRAND, P_TYPE, P_SIZE, COUNT(DISTINCT PS_SUPPKEY) AS SUPPLIER_CNT FROM PARTSUPP, PART WHERE P_PARTKEY = PS_PARTKEY AND P_BRAND <> 'BRAND#34' AND P_TYPE NOT LIKE 'LARGE BRUSHED%' AND P_SIZE IN (48, 19, 12, 4, 41, 7, 21, 39) AND PS_SUPPKEY NOT IN ( SELECT S_SUPPKEY FROM SUPPLIER WHERE S_COMMENT LIKE '%CUSTOMER%COMPLAINTS%' ) GROUP BY P_BRAND, P_TYPE, P_SIZE ORDER BY SUPPLIER_CNT DESC, P_BRAND, P_TYPE, P_SIZE;
-- 22.sql1, performance improves by 0.0%[cost_before=1611.98,after_cost=1611.98]
Contributing indice:[IDX_ORDERS_CUSTKEY]
SELECT CNTRYCODE, COUNT(*) AS NUMCUST, SUM(C_ACCTBAL) AS TOTACCTBAL FROM ( SELECT SUBSTRING(C_PHONE FROM 1 FOR 2) AS CNTRYCODE, C_ACCTBAL FROM CUSTOMER WHERE SUBSTRING(C_PHONE FROM 1 FOR 2) IN ('20', '40', '22', '30', '39', '42', '21') AND C_ACCTBAL > ( SELECT AVG(C_ACCTBAL) FROM CUSTOMER WHERE C_ACCTBAL > 0.00 AND SUBSTRING(C_PHONE FROM 1 FOR 2) IN ('20', '40', '22', '30', '39', '42', '21') ) AND NOT EXISTS ( SELECT * FROM ORDERS WHERE O_CUSTKEY = C_CUSTKEY ) ) AS CUSTSALE GROUP BY CNTRYCODE ORDER BY CNTRYCODE;
-- 19.sql1, performance improves by 626.5760869565217%[cost_before=267.38,after_cost=36.8]
Contributing indice:[]
SELECT SUM(L_EXTENDEDPRICE* (1 - L_DISCOUNT)) AS REVENUE FROM LINEITEM, PART WHERE ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'BRAND#52' AND P_CONTAINER IN ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') AND L_QUANTITY >= 4 AND L_QUANTITY <= 4 + 10 AND P_SIZE BETWEEN 1 AND 5 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' ) OR ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'BRAND#11' AND P_CONTAINER IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') AND L_QUANTITY >= 18 AND L_QUANTITY <= 18 + 10 AND P_SIZE BETWEEN 1 AND 10 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' ) OR ( P_PARTKEY = L_PARTKEY AND P_BRAND = 'BRAND#51' AND P_CONTAINER IN ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') AND L_QUANTITY >= 29 AND L_QUANTITY <= 29 + 10 AND P_SIZE BETWEEN 1 AND 15 AND L_SHIPMODE IN ('AIR', 'AIR REG') AND L_SHIPINSTRUCT = 'DELIVER IN PERSON' );


-- 9.sql1, performance improves by 3.2328053807767403%[cost_before=142.74,after_cost=138.27]
Contributing indice:[SQLLAB_IDX1797601124, SUPPLIER_PKEY, SQLLAB_IDX1640274034, IDX_LINEITEM_PART_SUPP, SQLLAB_IDX1017563074]
SELECT NATION, O_YEAR, SUM(AMOUNT) AS SUM_PROFIT FROM ( SELECT N_NAME AS NATION, EXTRACT(YEAR FROM O_ORDERDATE) AS O_YEAR, L_EXTENDEDPRICE * (1 - L_DISCOUNT) - PS_SUPPLYCOST * L_QUANTITY AS AMOUNT FROM PART, SUPPLIER, LINEITEM, PARTSUPP, ORDERS, NATION WHERE S_SUPPKEY = L_SUPPKEY AND PS_SUPPKEY = L_SUPPKEY AND PS_PARTKEY = L_PARTKEY AND P_PARTKEY = L_PARTKEY AND O_ORDERKEY = L_ORDERKEY AND S_NATIONKEY = N_NATIONKEY AND P_NAME LIKE '%DIM%' ) AS PROFIT GROUP BY NATION, O_YEAR ORDER BY NATION, O_YEAR DESC;
-- 1.sql1, performance improves by 11768.496420047732%[cost_before=994.58,after_cost=8.38]
Contributing indice:[SQLLAB_IDX1264424863]
SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY, SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS SUM_DISC_PRICE, SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT) * (1 + L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY, AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER FROM LINEITEM WHERE L_SHIPDATE <= DATE '1998-12-01' - INTERVAL '108' DAY GROUP BY L_RETURNFLAG, L_LINESTATUS ORDER BY L_RETURNFLAG, L_LINESTATUS;
-- 3.sql1, performance improves by 4579.939668174962%[cost_before=620.56,after_cost=13.26]
Contributing indice:[SQLLAB_IDX1365836084, SQLLAB_IDX1852359742, SQLLAB_IDX0394351801]
SELECT L_ORDERKEY, SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT)) AS REVENUE, O_ORDERDATE, O_SHIPPRIORITY FROM CUSTOMER, ORDERS, LINEITEM WHERE C_MKTSEGMENT = 'AUTOMOBILE' AND C_CUSTKEY = O_CUSTKEY AND L_ORDERKEY = O_ORDERKEY AND O_ORDERDATE < DATE '1995-03-13' AND L_SHIPDATE > DATE '1995-03-13' GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY ORDER BY REVENUE DESC, O_ORDERDATE LIMIT 10;
-- 11.sql1, performance improves by 300.4089979550102%[cost_before=685.3,after_cost=171.15]
Contributing indice:[SQLLAB_IDX1003493942, SQLLAB_IDX1400713053, SQLLAB_IDX1003493942, SQLLAB_IDX1400713053]
SELECT PS_PARTKEY, SUM(PS_SUPPLYCOST * PS_AVAILQTY) AS VALUE FROM PARTSUPP, SUPPLIER, NATION WHERE PS_SUPPKEY = S_SUPPKEY AND S_NATIONKEY = N_NATIONKEY AND N_NAME = 'MOZAMBIQUE' GROUP BY PS_PARTKEY HAVING SUM(PS_SUPPLYCOST * PS_AVAILQTY) > ( SELECT SUM(PS_SUPPLYCOST * PS_AVAILQTY) * 0.0001000000 FROM PARTSUPP, SUPPLIER, NATION WHERE PS_SUPPKEY = S_SUPPKEY AND S_NATIONKEY = N_NATIONKEY AND N_NAME = 'MOZAMBIQUE' ) ORDER BY VALUE DESC;
复制