一个优秀的数据库索引推荐IDEA插件-Paw Index Advisor

-- 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);
...
*/
-- 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);
...
*/
-- 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);
...
复制

-- 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;
...
复制