Fihirisar Database Wanda Kafaffen Tambayar Sannu: Me BAYYANA NAN A GASKIYA Ya Nuna Ni

Shafin tarihin odar mu ya ɗauki kusan daƙiƙa tara don lodi ga abokan ciniki waɗanda ke da tarihin siyayya na gaske, kuma na ɗauka tsawon watanni cewa wannan shine kawai ainihin farashi na abokin ciniki tare da dubban umarni, wani abu don warwarewa tare da pagination maimakon ainihin kwaro. A gaskiya karatun anEXPLAIN ANALYZEfitarwa yadda ya kamata maimakon skimming ya tabbatar da cewa zato gaba daya kuskure.

Tambayar da ta yi kyau kuma ba ta kasance ba

Tambayar ta haɗu da umarni, odar abubuwa, da tebur ɗin neman samfur, wanda aka tace ta ID ɗin abokin ciniki kuma aka jera ta kwanan wata, wanda ya karanta a matsayin tambaya mai ma’ana a kallo kuma yana cikin samarwa, ba ta canzawa, sama da shekaru biyu kafin kowa ya bayyana ta a kan abokin ciniki mai cikakken tarihin tsari don fallasa matsalar.

SELECT o.*, oi.*, p.name
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = $1
ORDER BY o.created_at DESC;

Abin da BAYYANA ANALYZE ya nuna a zahiri, da zarar na karanta bayan saman layi

zan guduEXPLAIN ANALYZEkafin a kan sauran tambayoyin kuma galibi kawai bincika jimlar lokacin aiwatarwa a ƙasa ba tare da karanta shirin da kansa ba. A wannan lokacin, karanta ta hanyar ainihin layin layi ta hanyar layi, Na sami bincike na jeri akan tebur ɗin oda tana tacewa ta abokin ciniki_id, wanda ke nufin Postgres yana karanta kowane layi ɗaya a cikin duka tebur ɗin umarni, miliyoyin layuka, don nemo ɗan hannun abokin ciniki ɗaya, maimakon amfani da index don tsalle kai tsaye zuwa gare su.

Seq Scan on orders o  (cost=0.00..48291.00 rows=1200 width=96)
  (actual time=0.045..8842.213 rows=1204 loops=1)
  Filter: (customer_id = $1)
  Rows Removed by Filter: 4198106
-- reading 4.2 million rows to find 1,200 matching ones

Me yasa ba a ƙara fihirisa a cikin shekaru biyu ba

An ƙirƙiri teburin umarni da wuri, kafin tarihin odar abokin ciniki ya zama babban shafi na zirga-zirga na gaske, kuma index akan abokin ciniki_id ba a taɓa ƙara shi ba tun lokacin da tambayar “ta yi aiki” a ma’anar cewa ta dawo da sakamako daidai, babu wanda ya haɗa koke-koken ɗaukar nauyi na shafi zuwa takamaiman bayanin da ya ɓace saboda korafe-korafen sun shigo a matsayin m “shafin yana jin jinkirin wani lokaci” rahotanni maimakon wani abu da ke nuna wannan takamaiman tambaya.

Ƙara fihirisar, da kuma tabbatar da ainihin haɓakawa maimakon ɗauka

Na kara madaidaicin ma’aunin B-itace akan abokin ciniki_id, kuma na sake gudanar da bincike iri ɗaya na BAYYANA maimakon dogaro kawai cewa fihirisa zai taimaka a fili, tunda an ƙone ni a baya ta hanyar fihirisa mai tsara tambaya ya zaɓi kada in yi amfani da shi a zahiri saboda dalilan da ban yi tsammani ba.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- after the index
Index Scan using idx_orders_customer_id on orders o
  (cost=0.43..1245.21 rows=1200 width=96)
  (actual time=0.031..12.847 rows=1204 loops=1)
-- 8.8 seconds down to 13 milliseconds

Fihirisar haɗaɗɗiyar da ta daidaita nau’in, wanda fihirisar ginshiƙi ɗaya kaɗai ba ta yi ba

Fihirisar ginshiƙi ɗaya ta gyara matatar amma har yanzu tambayar ta yi wani mataki daban bayan haka tun da fihirisar bata yi daidai da ORDER BY sashe ba. Sauya shi tare da maƙasudin ƙididdiga akan duka abokin ciniki_id da ƙirƙira_at bari Postgres ya dawo da layuka da yawa a cikin daidaitaccen tsari kai tsaye daga fihirisar, cire nau’in nau’in matakin gaba ɗaya.

CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);

Me yasa yanzu nake gudanar da BAYYANA akan kowace tambaya ta taɓa tebur sama da layuka 100k, babu keɓanta

Wannan takamaiman kwaro ya rayu cikin samarwa har tsawon shekaru biyu saboda babu wanda ke da al’ada ta zahiri ta ba da takamaiman tambayoyi game da ƙarar bayanan gaskiya kafin jigilar su, gwadawa a maimakon ƙaramin bayanan ci gaban gida inda fihirisar da ta ɓace ba ta haifar da wani bambanci ba kwata-kwata. Tun daga lokacin na mai da shi ka’ida ta sirri, kuma na tura shi azaman aikin ƙungiya, don gudanar da BAYYANA ANALYZE a kan tsarin samar da bayanai masu girman ƙima don kowace sabuwar tambaya da ta taɓa teburin da muke tsammanin girma girma, maimakon aminta da cewa tambayar da ke jin sauri a cikin gida za ta dawwama da sauri da zarar ƙarar bayanai ta zo.

Abin da a zahiri ya canza bayan jigilar gyara

Lokacin ɗaukar nauyin shafin tarihin oda ya ragu daga kusan daƙiƙa tara zuwa ƙasa da millisecond 200 don manyan abokan cinikinmu, kuma tikitin tallafin da ke bayyana “shafin yana jinkirin” ya ragu sosai a cikin wata mai zuwa, alaƙar da na ke da kwarin gwiwa game da yadda musamman wannan shafin ya kasance babban cikas tsawon lokaci, kawai ba a taɓa gano shi daidai ba.