performance एक विस्तृत विश्लेषण कैसे समझें




postgresql database-design (2)

हालांकि इस तरह की एक साधारण योजना के लिए उपयोगी नहीं है, http://explain.depesz.com वास्तव में उपयोगी है। http://explain.depesz.com/s/t4fi देखें। "आंकड़े" टैब और "विकल्प" pulldown नोट करें।

इस योजना के बारे में ध्यान देने योग्य बातें:

  • अनुमानित पंक्ति गणना (183) वास्तविक पंक्ति गणना (25) के लिए उचित रूप से तुलनीय है। यह सैकड़ों गुना अधिक नहीं है, न ही यह है 1. जब आप पंक्ति गणना अनुमानों, या "1 बनाम 1" मुद्दों की बात आती है तो आप परिमाण के आदेश में अधिक रुचि रखते हैं। (आपको "सरकारी काम के लिए पर्याप्त निकटता" की आवश्यकता भी नहीं है - "सैन्य अनुबंध लेखांकन के लिए पर्याप्त बंद करें" करेंगे)। चुनिंदाता अनुमान और आंकड़े उचित लगते हैं।

  • यह प्रदान किए गए दो कॉलम आंशिक अनुक्रमणिका index scan using index_cars_onsale_on_brand_and_model_name ( index scan using index_cars_onsale_on_brand_and_model_name ), इसलिए यह आंशिक अनुक्रमणिका स्थिति से मेल खाता है। आप Filter: has_auto_gear में देख सकते हैं Filter: has_auto_gear । इंडेक्स सर्च हालत भी दिखाया गया है।

  • क्वेरी प्रदर्शन उचित दिखता है कि तालिका की पंक्ति गणना का मतलब यह होगा कि सूचकांक काफी बड़ा है, खासकर जब यह दो कॉलम से अधिक है। मिलान पंक्तियां बिखरी जाएंगी, इसलिए संभव है कि प्रत्येक पंक्ति को एक अलग पृष्ठ को भी पढ़ने की आवश्यकता होगी।

मुझे यहां कुछ भी गलत नहीं लगता है। यह क्वेरी पोस्टग्रेएसक्यूएल 9.2 के इंडेक्स-केवल स्कैन से काफी लाभान्वित होगी, हालांकि।

यह संभव है कि यहां कुछ टेबल ब्लोट है, लेकिन 2-कॉलम इंडेक्स और पंक्तियों की निचली संख्या को देखते हुए प्रतिक्रिया समय पूरी तरह से अनुचित नहीं है, खासतौर पर 170 (!!) कॉलम वाली तालिका के लिए जो प्रत्येक में अपेक्षाकृत कुछ tuples फिट होने की संभावना है पृष्ठ। यदि आप कुछ डाउनटाइम पर खर्च कर सकते हैं तो टेबल को पुनर्गठित करने और इंडेक्स को पुनर्निर्माण करने के लिए VACUUM FULL का प्रयास करें। जब यह इसे पुनर्निर्माण करता है तो यह विशेष रूप से तालिका को कुछ समय तक लॉक कर देगा। यदि आप डाउनटाइम पर बर्दाश्त नहीं कर सकते हैं, तो pg_reorg देखें और / या CREATE INDEX CONCURRENTLY pg_reorg और pg_reorg CREATE INDEX CONCURRENTLY pg_reorg ALTER INDEX ... RENAME TO

आपको EXPLAIN (ANALYZE, BUFFERS, VERBOSE) कभी-कभी अधिक जानकारीपूर्ण मिल सकता है, क्योंकि यह बफर एक्सेस आदि दिखा सकता है।

एक विकल्प जो इस क्वेरी को तेज़ी से बना सकता है (हालांकि यह कुछ हद तक अन्य प्रश्नों को धीमा करने का जोखिम चलाता है) brand पर तालिका को विभाजित करना और constraint_exclusion सक्षम करना है। partitioning देखें।

मैं एक्सप्लिन विश्लेषण परिणामों को देखने से बहुत परिचित नहीं हूं, मुझे अपने प्रश्नों के साथ बहुत धीमी समस्या है। मैंने एक व्याख्या प्रश्नों के परिणामों की व्याख्या करने के तरीके को पढ़ने की कोशिश की है, लेकिन मुझे अभी भी पता नहीं है कि मुझे क्या देखना चाहिए, और क्या गलत हो सकता है। मुझे एहसास है कि कहीं भी कुछ बड़ी लाल रोशनी चमकती है, मुझे बस यह नहीं दिख रहा है।

तो क्वेरी बहुत सरल है, ऐसा लगता है:

EXPLAIN ANALYZE SELECT "cars".* FROM "cars" WHERE "cars"."sales_state" = 'onsale' AND "cars"."brand" = 'BMW' AND "cars"."model_name" = '318i' AND "cars"."has_auto_gear" = TRUE  LIMIT 25 OFFSET 0

और इस तरह का परिणाम:

Limit  (cost=0.00..161.07 rows=25 width=1245) (actual time=35.232..38.694 rows=25 loops=1)
  ->  Index Scan using index_cars_onsale_on_brand_and_model_name on cars  (cost=0.00..1179.06 rows=183 width=1245) (actual time=35.228..38.652 rows=25 loops=1)
        Index Cond: (((brand)::text = 'BMW'::text) AND ((model_name)::text = '318i'::text))
        Filter: has_auto_gear"
Total runtime: 38.845 ms

एक छोटी सी पृष्ठभूमि: मैं पोस्टग्रेस्क्ल 9.1.6 पर हूं, हेरोोकस समर्पित डेटाबेस पर चल रहा हूं। मेरे डीबी में लगभग 7,5 जीबी रैम है, टेबल कारों में 3,1 एम पंक्तियां हैं और पंक्तियों के एक aprox 2,0M में sales_state = 'onsale' है। तालिका में 170 कॉलम हैं। इंडेक्स जो इसका उपयोग करता है वह इस तरह दिखता है:

CREATE INDEX index_cars_onsale_on_brand_and_model_name
  ON cars
  USING btree
  (brand COLLATE pg_catalog."default" , model_name COLLATE pg_catalog."default" )
  WHERE sales_state::text = 'onsale'::text;

कोई भी कुछ बड़ा स्पष्ट मुद्दा देख रहा है?

संपादित करें:

SELECT pg_relation_size('cars'), pg_total_relation_size('cars');

pg_relation_size: 2058444800 pg_total_relation_size: 4900126720

SELECT pg_relation_size('index_cars_onsale_on_brand_and_model_name');

pg_relation_size: 46301184

SELECT avg(pg_column_size(cars)) FROM cars limit 5000;

औसत: 636.97325672107929 9 5

सीमा के बिना:

EXPLAIN ANALYZE SELECT "cars".* FROM "cars" WHERE "cars"."sales_state" = 'onsale' AND "cars"."brand" = 'BMW' AND "cars"."model_name" = '318i' AND "cars"."has_auto_gear" = TRUE

Bitmap Heap Scan on cars  (cost=12.54..1156.95 rows=183 width=4) (actual time=17.067..55.198 rows=2096 loops=1)
  Recheck Cond: (((brand)::text = 'BMW'::text) AND ((model_name)::text = '318i'::text) AND ((sales_state)::text = 'onsale'::text))
  Filter: has_auto_gear
  ->  Bitmap Index Scan on index_cars_onsale_on_brand_and_model_name  (cost=0.00..12.54 rows=585 width=0) (actual time=15.211..15.211 rows=7411 loops=1)"
        Index Cond: (((brand)::text = 'BMW'::text) AND ((model_name)::text = '318i'::text))
Total runtime: 56.851 ms

Answer #1

खैर ... पहली बात यह है कि मैं आपको बता सकता हूं कि आपका डेटाबेस उम्मीद कर रहा है (आंकड़ों से) 183 पंक्तियां प्राप्त करने के लिए। हकीकत में यह 25 पंक्तियां प्राप्त कर रहा है। यद्यपि यह शायद इस मामले में बहुत प्रासंगिक नहीं है (यानी इन छोटी मात्राओं और कोई भारी परिचालन के साथ, इसे गलत तरीके से अनुमान लगाने की चिंता करने की आवश्यकता नहीं है)।

एक बड़ी समस्या (imho) यह है कि 25 पंक्तियों के लिए एक साधारण सूचकांक लुकअप 35ms ले रहा है। यह थोड़ा सा लगता है। क्या डाटाबेस कम से कम सभी इंडेक्स मेमोरी में पर्याप्त है? हालांकि यह अत्यधिक नहीं है, बस मेरे लिए थोड़ा धीमा लगता है।

आपकी व्याख्याओं को देखने के लिए, मैं interpret.depesz.com का उपयोग करने की अनुशंसा करता हूं: http://explain.depesz.com/s/sA6







postgresql-performance