किसी table को कौन से indexes चाहिए, यह आप कैसे तय करते हैं?

table से नहीं, queries से शुरू करें। देखें कि WHERE, JOIN और ORDER BY में क्या आता है, फिर ऐसा composite index बनाएं जिसमें equality वाले columns पहले और range या sort वाला column आखिर में हो। EXPLAIN ANALYZE से पुष्टि करें कि sequential scan की जगह index scan आ गया। हर index write throughput और storage की कीमत लेता है, तो बिना इस्तेमाल वाले हटा दें और कम cardinality वाले column पर अकेले index लगाने से बचें।

मैं डिज़ाइन के वक्त अंदाज़ा लगाने की जगह slow query log से उल्टा चलता हूं। ऐसी query लीजिए जो tenant id और status पर filter करती है और created at से sort करती है। वहां सही index tenant id, status, created at पर इसी क्रम में है, क्योंकि equality वाले predicates पहले जाते हैं और sort वाला column आखिर में ताकि index ordering भी पूरी कर सके। created at पहले रख दीजिए तो उस query के लिए index लगभग बेकार है। फिर मैं सच में EXPLAIN ANALYZE चलाता हूं और देखता हूं कि plan बदला या नहीं और row के अनुमान वाजिब हैं या नहीं, क्योंकि planner index को अनदेखा कर देगा अगर उसे लगे कि वह वैसे भी table का ज़्यादातर हिस्सा छू लेगा। लागत की तरफ, मैंने ऐसी write heavy table देखी है जिसमें ग्यारह indexes थे और inserts ही अड़चन थे, और चार बिना इस्तेमाल वाले हटाने से ingest throughput लगभग दोगुना हो गया। Postgres में मैं कुछ भी हटाने का प्रस्ताव देने से पहले pg_stat_user_indexes में शून्य scans वाले indexes देखता हूं, और लाइव table पर उन्हें concurrently बनाता हूं।