รูปแบบดัชนีที่ดีที่สุดสำหรับการเชื่อมโยงแบบโพลีมอร์ฟิกใน Postgres

หากคุณต้องการค้นหาคีย์ต่างประเทศของฐานข้อมูลอย่างรวดเร็ว คุณจะต้องจัดทำดัชนีมัน แต่เมื่อคีย์ต่างประเทศของคุณมีสองส่วน ได้แก่ ID และประเภท คุณควรจัดทำดัชนีส่วนใด

(ไม่มีเวลา ข้ามไปยังจุดสิ้นสุด)

ปรากฎว่ามีตัวเลือกมากมายสำหรับการจัดทำดัชนีการเชื่อมโยงแบบโพลีมอร์ฟิก เราสามารถจัดทำดัชนีฟิลด์ใดฟิลด์หนึ่งเพียงอย่างเดียว หรือทั้งสองฟิลด์ ทั้งสองอย่าง (อย่างอิสระ) หรือทั้งสองอย่างด้วยดัชนีแบบผสม แต่อันไหนมีประสิทธิภาพมากที่สุด? ในฐานะที่ฉันเป็นนักพัฒนาที่ดี ฉันค้นหาคำตอบใน StackOverflow และพบว่า...

… คำตอบครึ่งหนึ่งบอกว่าใช้ดัชนีผสมที่ขึ้นต้นด้วย 'type' และอีกครึ่งหนึ่งแนะนำให้ใช้ดัชนีผสมที่ขึ้นต้นด้วย 'ID' เนื่องจากดัชนีผสมต้องค้นหาตามคอลัมน์แรกก่อนจึงจะเข้าถึงคอลัมน์ที่สองได้ คำตอบเหล่านี้จึงไม่เท่ากัน ไม่มีคำตอบใดที่ฉันพบรวมการวัดประสิทธิภาพ ดังนั้นจึงไม่มีข้อมูลมากนักในการพิจารณาว่าควรทำอย่างไร เนื่องจากฉันไม่พบคำตอบที่เป็นเอกฉันท์ ฉันจึงตัดสินใจทำการทดสอบด้วยตัวเอง

ระเบียบวิธี

ในการตั้งค่า ฉันได้สร้างตารางสองตารางสำหรับชื่อ 'alphas' และ 'omegas' ตามอำเภอใจ จากนั้น ฉันสร้างชุด "ตารางทดสอบดัชนี" อื่นๆ ขึ้นมาชุดหนึ่ง ซึ่งแต่ละตารางมีความสัมพันธ์แบบโพลีมอร์ฟิกที่สามารถอ้างอิงแถวของตารางอัลฟ่าหรือตารางโอเมก้าตัวใดตัวหนึ่งได้ ตารางการทดสอบดัชนีแต่ละตารางจะแตกต่างกันเพียงวิธีการจัดทำดัชนีคอลัมน์ความสัมพันธ์ทั้งสองคอลัมน์เท่านั้น โดยรวมแล้ว ฉันสร้างตารางทดสอบดัชนีเจ็ดตารางโดยใช้การย้ายข้อมูลบนเซิร์ฟเวอร์ Rails 6.1 ของฉัน ซึ่งเชื่อมต่อกับ Postgres ในเครื่อง:

ไม่ได้จัดทำดัชนี
จัดทำดัชนีเฉพาะใน ID
จัดทำดัชนีตามประเภทเท่านั้น
ดัชนีอิสระทั้งประเภทและรหัส
ดัชนีผสมที่มี ID major ประเภท minor
ดัชนีผสมที่มีประเภทหลัก ID รอง
จัดทำดัชนีโดยใช้ประเภทการอ้างอิง t ของ Rails โดยมีการตั้งค่าสถานะ polymorphic และดัชนีเป็น true ในทางปฏิบัติ สิ่งนี้จะสร้างตารางและดัชนีเดียวกันกับ #6 ฉันทดสอบแล้ว แต่จะถือว่าเหมือนกับ #6 ในการสนทนานับจากนี้เป็นต้นไป

class CreatePolymorphicTestTables < ActiveRecord::Migration[6.1]
  def change
    create_table :alphas

    create_table :omegas

    create_table :cmpd_id_type_refs do |t|
      t.bigint :relation_id, null: false
      t.text :relation_type, null: false
    end

    add_index :cmpd_id_type_refs, [:relation_id, :relation_type]

    create_table :cmpd_type_id_refs do |t|
      t.bigint :relation_id, null: false
      t.text :relation_type, null: false
    end

    add_index :cmpd_type_id_refs, [:relation_type, :relation_id]

    create_table :id_only_refs do |t|
      t.bigint :relation_id, null: false, index: true
      t.text :relation_type, null: false
    end

    create_table :type_only_refs do |t|
      t.bigint :relation_id, null: false
      t.text :relation_type, null: false, index: true
    end

    create_table :independent_type_id_refs do |t|
      t.bigint :relation_id, null: false, index: true
      t.text :relation_type, null: false, index: true
    end

    create_table :reference_refs do |t|
      t.references :relation, null: false, polymorphic: true
    end

    create_table :unindexed_refs do |t|
      t.references :relation, null: false, polymorphic: true, index: false
    end
  end
end

ต่อไป ฉันเติมตารางของฉัน ขั้นแรกสร้างอัลฟ่า 100 ตัวและโอเมก้า 100 ตัว จากนั้น ฉันเลือกอัลฟ่าแบบสุ่มทีละรายการ และสร้างแถวใหม่ในตารางทดสอบดัชนีทุกตารางที่อ้างอิงถึงตารางนั้น จากนั้นฉันก็ทำแบบเดียวกันกับโอเมก้า ฉันทำซ้ำขั้นตอนนี้ 50,000 ครั้งเพื่อให้แต่ละตารางทดสอบดัชนีมี 100,000 รายการ กระบวนการนี้ทำให้มั่นใจได้ว่าตารางทดสอบดัชนีทุกตารางจากทั้งหมดเจ็ดตารางมีจำนวนแถวเท่ากันในลำดับเดียวกันที่อ้างอิงถึงแต่ละอัลฟาหรือโอเมก้า กล่าวอีกนัยหนึ่ง เนื้อหาของตารางเหมือนกัน

desc "Populates polymorphic test tables"

# Example call: `rake populate_polymorphic_test` to actually execute
task :populate_polymorphic_test => :environment do |task, args|
  start_time = Time.now
  puts "-- Started at #{start_time}"

  ActiveRecord::Base.transaction do
    100.times do |i|
      Alpha.create!(id: i+1)
      Omega.create!(id: i+1)
    end

    50000.times do
      idx = rand(100) + 1

      a = Alpha.find(idx)

      UnindexedRef.create!(relation: a)
      IdOnlyRef.create!(relation: a)
      TypeOnlyRef.create!(relation: a)
      IndependentTypeIdRef.create!(relation: a)
      CmpdIdTypeRef.create!(relation: a)
      CmpdTypeIdRef.create!(relation: a)
      ReferenceRef.create!(relation: a)

      idx = rand(100) + 1

      b = Omega.find(idx)

      UnindexedRef.create!(relation: b)
      IdOnlyRef.create!(relation: b)
      TypeOnlyRef.create!(relation: b)
      IndependentTypeIdRef.create!(relation: b)
      CmpdIdTypeRef.create!(relation: b)
      CmpdTypeIdRef.create!(relation: b)
      ReferenceRef.create!(relation: b)
    end
  end

  finish_time = Time.now
  puts "-- Finished at #{finish_time}. Elapsed time #{finish_time - start_time} seconds."
end

สุดท้ายนี้ ฉันดำเนินการค้นหาของฉัน สำหรับอัลฟ่าและโอเมก้าแต่ละตัว ฉันค้นหาการเชื่อมโยงทั้งหมดในการทดสอบสองครั้ง โดยล้างแคชข้อความค้นหาก่อนการทดสอบแต่ละครั้ง การทดสอบครั้งแรกประกอบด้วยการดึงคอลัมน์ ID และการทดสอบครั้งที่สองประกอบด้วยการนับจำนวนบันทึกที่เกี่ยวข้อง การดำเนินการทั้งสองนี้เกิดขึ้นในฐานข้อมูลเป็นหลัก โดยไม่ทำให้ Rails สร้างอินสแตนซ์ ApplicationRecord ซึ่งหมายความว่าส่วนแบ่งส่วนใหญ่ของงานควรเป็นการค้นหาฐานข้อมูลโดยมีงานในหน่วยความจำน้อยกว่าใน Rails เพื่อทำให้น้ำขุ่น

desc "Benchmark queries on polymorphic indexes"

# Example call: `rake run_polymorphic_query_test` to actually execute
task :run_polymorphic_query_test => :environment do |task, args|

  test_set = [*Alpha.all, *Omega.all]

  ActiveRecord::Base.connection.query_cache.clear

  p "---------------------------- Pluck ID Test ----------------------------"
  Benchmark.bm do |x|
    x.report("Unindexed") { test_set.each{|rl| rl.unindexed_refs.pluck(:id)} }
    x.report("ID Only") { test_set.each{|rl| rl.id_only_refs.pluck(:id)} }
    x.report("Type Only") { test_set.each{|rl| rl.type_only_refs.pluck(:id)} }
    x.report("Independent Indicies") { test_set.each{|rl| rl.independent_type_id_refs.pluck(:id)} }
    x.report("Reference (Compound Type-ID)") { test_set.each{|rl| rl.reference_refs.pluck(:id)} }
    x.report("Compound Type-ID") { test_set.each{|rl| rl.cmpd_type_id_refs.pluck(:id)} }
    x.report("Compound ID-Type") { test_set.each{|rl| rl.cmpd_id_type_refs.pluck(:id)} }
  end

  ActiveRecord::Base.connection.query_cache.clear

  p "---------------------------- Count Test ----------------------------"
  Benchmark.bm do |x|
    x.report("Unindexed") { test_set.each{|rl| rl.unindexed_refs.count} }
    x.report("ID Only") { test_set.each{|rl| rl.id_only_refs.count} }
    x.report("Type Only") { test_set.each{|rl| rl.type_only_refs.count} }
    x.report("Independent Indicies") { test_set.each{|rl| rl.independent_type_id_refs.count} }
    x.report("Reference (Compound Type-ID)") { test_set.each{|rl| rl.reference_refs.count} }
    x.report("Compound Type-ID") { test_set.each{|rl| rl.cmpd_type_id_refs.count} }
    x.report("Compound ID-Type") { test_set.each{|rl| rl.cmpd_id_type_refs.count} }
  end
end

ผลการวิจัย

สัญชาตญาณของฉันคือว่าดัชนีผสมที่มี ID major จะทำงานได้ดีที่สุดเนื่องจากฟิลด์ ID มีคาร์ดินัลลิตี้สูงสุด (IE ที่มีความแปรผันมากที่สุด) และดังนั้นจึงนำไปสู่ดัชนีที่มีประโยชน์มากกว่า แล้วฉันพูดถูกไหม?

ชนิดของ. รูปแบบการจัดทำดัชนีที่ดีที่สุดคือ จากดีที่สุดไปหาดีน้อยเล็กน้อย

ไม่ว่าจะเป็นดัชนีผสม
ดัชนีอิสระ
การจัดทำดัชนี ID เท่านั้น

# Example Run Results
#
# The `real` timing seems to be the most stable and useful. However,
# don't trust the exact numbers in this example. There was a good
# amount of variation between runs.
#
# ---------------------------- Pluck ID Test ----------------------------
                               user       system     total    real
Unindexed                      0.219539   0.004578   0.224117 (  1.048511)
ID Only                        0.114350   0.006782   0.121132 (  0.244616)
Type Only                      0.144048   0.011984   0.156032 (  1.002924)
Independent Indicies           0.106722   0.007628   0.114350 (  0.223154)
Reference (Compound Type-ID)   0.106546   0.007616   0.114162 (  0.195699)
Compound Type-ID               0.109110   0.008631   0.117741 (  0.198535)
Compound ID-Type               0.110510   0.008175   0.118685 (  0.195146)

# ---------------------------- Count Test ----------------------------
                               user       system     total    real
Unindexed                      0.102973   0.006801   0.109774 (  0.933632)
ID Only                        0.078064   0.004718   0.082782 (  0.197286)
Type Only                      0.189893   0.002887   0.192780 (  1.047528)
Independent Indicies           0.080484   0.003792   0.084276 (  0.184939)
Reference (Compound Type-ID)   0.072188   0.008226   0.080414 (  0.158406)
Compound Type-ID               0.070968   0.008155   0.079123 (  0.152290)
Compound ID-Type               0.060601   0.012225   0.072826 (  0.147477)

จากการวิ่งหลายครั้ง ดัชนีผสมทั้งสองมีประสิทธิภาพคล้ายกันมาก พวกเขาอยู่ใกล้พอที่จะดูเหมือนไม่สำคัญว่าคุณเลือกอะไร ความคลาดเคลื่อนระหว่างสิ่งเหล่านั้นอยู่ในขอบเขตของข้อผิดพลาดในการทดสอบของฉัน การวิ่งบางรายการ ID-major จะชนะและบางรายการอาจไปประเภทเมเจอร์

การจัดทำดัชนีอิสระเป็นรองอันดับสองที่ใกล้เคียงกันมาก แต่ปรากฏว่าช้ากว่าดัชนีผสมทั้งสองเล็กน้อยอย่างน่าเชื่อถือ การจัดทำดัชนีเฉพาะ ID นั้นใกล้เคียงกันแม้ว่าจะช้ากว่าเล็กน้อยก็ตาม

อีกสองแผนการนั้นช้ากว่ามาก ประสิทธิภาพของตารางประเภทอย่างเดียวและตารางที่ไม่ได้จัดทำดัชนีนั้นค่อนข้างเทียบเท่า โดยทั่วไปจะช้ากว่าตารางอื่นๆ ประมาณ 5–6 เท่า

เป็นที่น่าสังเกตว่าก่อนที่ฉันจะตัดสินใจเกี่ยวกับวิธีการขั้นสุดท้าย ฉันทดสอบด้วยบันทึกเพียง 20,000 รายการ (แทนที่จะเป็น 100,000 รายการ) ในแต่ละตารางทดสอบดัชนี ที่ขนาดตารางนั้น ความแตกต่างระหว่างวิธีการต่างๆ มีความชัดเจนน้อยกว่ามาก แม้แต่การสแกนตามลำดับบนตารางที่ไม่ได้จัดทำดัชนีก็ทำได้ค่อนข้างคล้ายกับรูปแบบอื่นๆ ทุกประการ

ข้อสรุป (tl; dr)

หากคุณกำลังเขียนดัชนีสำหรับการเชื่อมโยงแบบโพลีมอร์ฟิก การเชื่อมโยงแบบผสมอย่างใดอย่างหนึ่งก็ทำได้ดี

หากคุณกังวลเกี่ยวกับขนาดดัชนีหรือเวลาในการเขียนดัชนี คุณสามารถใช้ดัชนีเฉพาะ ID ได้และจะไม่สังเกตเห็นความแตกต่าง

บนโต๊ะเล็กๆ (‹10,000 แถว) คุณคงไม่สังเกตเห็นความแตกต่างเลย

และในทุกกรณี Postgres ก็ชั่วร้ายอย่างรวดเร็ว

หากคุณ — ผู้พัฒนาเว็บผู้กล้าได้กล้าเสีย — ชอบบทความนี้ คุณอาจเรียนรู้เคล็ดลับสั้นๆ จากบทความอื่นๆ ของฉันที่แสดงด้านล่าง และหากคุณอยู่ในสหรัฐอเมริกาและกำลังมองหางาน ลองอ่าน ทำไมคุณซึ่งเป็นวิศวกรซอฟต์แวร์ จึงควรขายยาสัตว์เลี้ยงทางอินเทอร์เน็ตกับฉัน

รูปแบบดัชนีที่ดีที่สุดสำหรับการเชื่อมโยงแบบโพลีมอร์ฟิกใน Postgres

ระเบียบวิธี

ผลการวิจัย

ข้อสรุป (tl; dr)

คำถามในหัวข้อ