มากกว่า

ประสิทธิภาพ Postgres พร้อม Radius Searches

ประสิทธิภาพ Postgres พร้อม Radius Searches


ฉันกำลังย้ายแอปพลิเคชันจาก mysql ไปยัง postgres และมีหลายตารางที่มีแถวมากกว่า 500k+ ในแต่ละจุดทางภูมิศาสตร์ (lat, lon) ใน mysql ฉันใช้แบบสอบถามแบบมีขอบเขตที่มี hasrsine เพื่อเลือกแถวภายในระยะทางที่กำหนดจากจุดศูนย์กลาง ฉันเข้าใจว่าฉันสามารถใช้ st_dwithin ของ postgis หรือโมดูล earthdistance ได้ (หรือฉันอาจจะพอร์ตการสืบค้น mysql ของฉันไปด้วย) แค่สงสัยว่าใครมีคำแนะนำว่าวิธีใดจะมีประสิทธิภาพดีที่สุดกับชุดข้อมูลขนาดใหญ่เหล่านี้


ใช้ st_dwithin มันจะใช้ดัชนีเชิงพื้นที่และทำงานเร็วมากหากเป็นเพียงจุด 500,000 คะแนนนั้นไม่มาก


ปรับปรุงประสิทธิภาพของ COUNT/GROUP-BY ในตาราง PostgresSQL ขนาดใหญ่หรือไม่

ฉันใช้ PostgresSQL 9.2 และมีความสัมพันธ์ 12 คอลัมน์โดยมีแถวประมาณ 6,700,000 แถว ประกอบด้วยโหนดในพื้นที่ 3 มิติ โดยแต่ละโหนดอ้างอิงถึงผู้ใช้ (ผู้สร้าง) ในการสอบถามว่าผู้ใช้รายใดสร้างจำนวนโหนดที่ฉันทำดังต่อไปนี้ (เพิ่มคำอธิบายการวิเคราะห์สำหรับข้อมูลเพิ่มเติม):

อย่างที่คุณเห็น จะใช้เวลาประมาณ 1.7 วินาที ไม่ได้เลวร้ายเกินไปเมื่อพิจารณาจากปริมาณข้อมูล แต่ฉันสงสัยว่าจะสามารถปรับปรุงได้หรือไม่ ฉันพยายามเพิ่มดัชนี BTree ในคอลัมน์ผู้ใช้ แต่สิ่งนี้ไม่ได้ช่วยอะไรเลย

คุณมีข้อเสนอแนะอื่นหรือไม่?

เพื่อความสมบูรณ์ นี่คือคำจำกัดความของตารางที่สมบูรณ์พร้อมดัชนีทั้งหมด (โดยไม่มีข้อจำกัดของคีย์ต่างประเทศ การอ้างอิงและทริกเกอร์):

แก้ไข: นี่คือผลลัพธ์ เมื่อฉันใช้แบบสอบถาม (และดัชนี) ที่เสนอโดย @ypercube (การสืบค้นใช้เวลาประมาณ 5.3 วินาทีโดยไม่มี EXPLAIN ANALYZE ):

แก้ไข 2: นี่คือผลลัพธ์ เมื่อฉันใช้ดัชนีบน project_id, user_id (แต่ยังไม่มีการเพิ่มประสิทธิภาพสคีมา) ตามที่ @erwin-brandstetter แนะนำ (การสืบค้นทำงานด้วยความเร็ว 1.5 วินาทีที่ความเร็วเดียวกับข้อความค้นหาดั้งเดิมของฉัน):


อย่าเก็บ lat และ long ไว้บนโต๊ะแบบนั้น ให้ใช้เรขาคณิต PostGIS หรือประเภทภูมิศาสตร์แทน

เมื่อคุณต้องการสืบค้นข้อมูล คุณสามารถใช้ KNN ( <-> ) ซึ่งจะทำสิ่งนี้กับดัชนีได้จริง

ในแบบสอบถามของคุณ คุณมีระยะห่างอย่างชัดเจน < 5 คุณสามารถทำได้บนดัชนีด้วย

เพื่อให้แน่ใจว่าจะไม่มีการส่งคืนหากจุดทั้งหมดอยู่นอก Distance_in_meters

นอกจากนี้ x และ y ยังเป็นเลขฐานสิบ ST_MakePoint(46.06, 14.05)

คุณสามารถใช้ส่วนขยายคิวบ์และ Earthdistance ของ PostgreSQL

สมมติว่าตำแหน่งปัจจุบันของคุณคือ 35.697933, 139.707318 แบบสอบถามของคุณจะเป็นดังนี้:

โปรดทราบว่าระยะทางเป็นไมล์ (โดยค่าเริ่มต้น)

ดูส่วนสำคัญนี้ คุณจะพบวิธีการประกาศ DOMAIN ในประเภทจุดและวิธีแทนที่ตัวดำเนินการระยะทางเพื่อส่งคืนระยะทางออร์โธโดรมิก

ประกาศประเภท latlong ที่สืบทอดมาจาก point :

ระยะทางออร์โธโดรมิกเป็นกิโลเมตร (ระยะทางบนทรงกลมที่มีรัศมีโลก):

แทนที่ตัวดำเนินการระยะทาง <-> โดยใช้ฟังก์ชันนี้เมื่อใช้กับ latlongs:

ในการค้นหา SQL ของคุณ เพื่อค้นหาเอนทิตีที่ใกล้ที่สุด:

คุณอาจต้องการเก็บตำแหน่ง lat และ long ในฟิลด์เดียวกันโดยใช้ประเภท latlong


2 คำตอบ 2

Async & LISTEN/NOTIFY เป็นวิธีที่ถูกต้อง!

คุณสามารถเพิ่มทริกเกอร์บน UPDATE/INSERT และ ดำเนินการค้นหาของคุณในเนื้อหาของ trigger, บันทึก จำนวนแถว ในตารางอย่างง่ายและหากค่าเปลี่ยนให้โทรแจ้ง หากคุณต้องการชุดค่าผสมพารามิเตอร์หลายชุดในการสืบค้น คุณสามารถสร้าง/ทำลายทริกเกอร์จากภายในโปรแกรมของคุณได้

คุณต้องตั้งค่าทริกเกอร์ที่ถูกต้องซึ่งจะเรียก NOTIFY สำหรับไคลเอ็นต์ทั้งหมดที่ใช้ LISTEN ในแชนเนลเดียวกัน

เป็นการยากที่จะแนะนำว่าคุณใช้ตรรกะ NOTIFY ของคุณอย่างไรภายในทริกเกอร์ เนื่องจากขึ้นอยู่กับสิ่งต่อไปนี้:

  • ข้อความนี้มีไว้สำหรับลูกค้าจำนวนเท่าใด
  • แบบสอบถามที่กำลังประเมินหนัก/ใหญ่เพียงใด
  • ทริกเกอร์สามารถรู้ตรรกะของคิวรีเพื่อประเมินได้หรือไม่

จากคำตอบ คุณอาจพิจารณาแนวทางต่างๆ ซึ่งรวมถึงแต่ไม่จำกัดเฉพาะตัวเลือกต่อไปนี้และการผสมผสาน

  • ดำเนินการสืบค้น/ดูเมื่อไม่สามารถประเมินผลลัพธ์ได้ และแคชผลลัพธ์
  • ให้การแจ้งเตือนอย่างชาญฉลาดหากสามารถประเมินผลลัพธ์ของการสืบค้นได้
  • ใช้เพย์โหลดเพื่อส่งต่อรายละเอียดการอัปเดตไปยังผู้ฟัง
  • กำหนดเวลาการสืบค้น/ดูการเรียกใช้ซ้ำสำหรับการดำเนินการล่าช้า ถ้ามันหนัก
  • ทำการแจ้งเตือนทั้งหมดเป็นงานแยกต่างหาก

บางสถานการณ์อาจเติบโตค่อนข้างซับซ้อน ตัวอย่างเช่น คุณอาจมีไคลเอนต์หลักที่สามารถทำการเปลี่ยนแปลงได้ และมีทาสหลายตัวที่จำเป็นต้องได้รับแจ้ง ในกรณีนี้ ต้นแบบดำเนินการค้นหา ตรวจสอบว่าผลลัพธ์มีการเปลี่ยนแปลงหรือไม่ จากนั้นเรียกใช้ฟังก์ชันในเซิร์ฟเวอร์ PostgreSQL เพื่อทริกเกอร์การแจ้งเตือนในทาสทั้งหมด

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


SequelizeJS และ GIS

การสนับสนุน GIS สำหรับ SequelizeJS นั้นได้รับการสนับสนุนตั้งแต่ปี 2014 ในทางกลับกัน น่าเสียดายที่มีการใช้งานสำหรับ PostgreSQL และ PostGIS เท่านั้น มีการอภิปรายเกี่ยวกับการดำเนินการสนับสนุนในวงกว้างสำหรับ GIS ข้อเสียอีกประการหนึ่งคือขณะนี้รองรับเฉพาะรูปทรงเรขาคณิตเท่านั้น หากคุณต้องการการสนับสนุนด้านภูมิศาสตร์ SequelizeJS วันนี้ไม่สามารถช่วยคุณได้เนื่องจากไม่ได้ใช้งานเป็นประเภทข้อมูลเลย อย่างไรก็ตาม สำหรับตัวอย่างเล็กๆ ของฉัน ไม่เป็นไรที่จะใช้ข้อมูลเรขาคณิต แม้ว่าจะทำการค้นหาตามตำแหน่งเนื่องจากรัศมีจะเล็กพอที่จะได้ผลลัพธ์ที่ดี ที่จริงแล้ว เราสามารถใช้ SequelizeJS สำหรับทั้ง PostgreSQL และ MSSQL ได้! ย่อหน้าถัดไปจะอธิบายสิ่งที่คุณต้องทำเพื่อให้บรรลุเป้าหมายนี้

เตรียม SequelizeJS

สำหรับแบ็กเอนด์ตัวอย่าง ฉันใช้ Node.js v5.4.0 ในตอนแรก เราต้องติดตั้งการพึ่งพาที่จำเป็น npm ง่าย ๆ ที่ฉันทำภาคต่อ pg น่าเบื่อคือสิ่งที่เราต้องการ sequelize จะติดตั้ง SequelizeJS pg เป็นไดรเวอร์ฐานข้อมูลสำหรับ PostgreSQL และไดรเวอร์ MSSQL ที่น่าเบื่อ

หมายเหตุด้านข้าง: มีไดรเวอร์ MSSQL อย่างเป็นทางการจาก Microsoft (ที่นี่และที่นี่) แต่ปัจจุบันมีให้สำหรับ Windows เท่านั้น

สร้างคลาสตัวเชื่อมต่อฐานข้อมูล

เริ่มต้นด้วยการสร้างฐานข้อมูลคลาสที่เรียบง่ายและเรียบง่ายใน ECMAScript 2015 ซึ่งเชื่อมต่อกับฐานข้อมูลและสร้างแบบจำลอง:

มาแยกโค้ดนี้กัน – อย่างแรกเลย: นำเข้า Sequelize เพื่อให้เราใช้งานได้ จากนั้นเรากำหนดคลาสฐานข้อมูลด้วยฟิลด์สาธารณะที่เรียกว่า model และฟังก์ชันสาธารณะสองอย่างที่เรียกว่า getDialect และ initialize พื้นที่สาธารณะจะเก็บแบบจำลองตัวอย่างของเราไว้ ดังนั้นเราจึงสามารถใช้ในภายหลังได้ ฟังก์ชัน getDialect จะคืนค่าภาษาถิ่นที่ใช้ทั้ง postgres หรือ mssql ฟังก์ชัน initialize ใช้เพื่อเริ่มต้นและเชื่อมต่อกับฐานข้อมูล ภายใน เราตรวจสอบว่าเราต้องการเชื่อมต่อกับ PostgreSQL หรือ MSSQL หรือไม่ หลังจากเชื่อมต่อ เราจะสร้าง SampleModel ด้วย id คีย์หลักที่เพิ่มค่าอัตโนมัติและจุดประเภท GEOMETRY('POINT') SequelizeJS รองรับรูปทรงต่างๆ แต่ขึ้นอยู่กับเอ็นจิ้นฐานข้อมูล ด้วย GEOMETRY('POINT') เราบอกเอ็นจิ้นฐานข้อมูล เราต้องการเก็บเรขาคณิตของจุดประเภทเท่านั้น ชนิดอื่นๆ ที่ใช้ได้คือ LINESTRING หรือ POLYGON หรือคุณสามารถละเว้นประเภททั้งหมดเพื่อใช้ประเภทต่างๆ ภายในคอลัมน์เดียวกัน ในที่สุด เราเก็บโมเดลของเราไว้ในที่สาธารณะ ดังนั้นจึงสามารถเข้าถึงได้ผ่านทาง this.models.SampleModel ในภายหลัง สุดท้าย แต่ไม่ท้ายสุด เราใช้ syncDatabase() ซึ่งเรียก sequelize.sync() และส่งคืน Promise sequelize.sync() จะสร้างตารางที่จำเป็นสำหรับโมเดลที่คุณกำหนดในกรณีนี้

*หมายเหตุ: *เมธอด SequelizeJS ทั้งหมดที่สื่อสารกับฐานข้อมูลจะส่งกลับ Promise

โมดูลได้รับการส่งออกเป็นอินสแตนซ์/ซิงเกิลตัน

สร้างอะแด็ปเตอร์ SampleService

ถัดไปเป็นคลาสบริการซึ่งจะใช้ฐานข้อมูลและแบบจำลองของเราเพื่อสร้างเอนทิตีและอ่านข้อมูล บริการนี้จะเป็นตัวห่อหุ้มรอบการใช้งานจริงสำหรับกลไกฐานข้อมูลต่างๆ และจัดเตรียมวิธีการเข้าถึงซึ่งสามารถใช้โดยส่วนต่อประสานผู้ใช้หรือ Web API เพื่อเข้าถึงข้อมูล

ในตอนแรก เรานำเข้าสองคลาส: SampleServiceMSSQL และ SampleServicePostgreSQL เนื่องจากเราต้องการแนวทางที่แตกต่างกันในการจัดการข้อมูลเรขาคณิตของเรา จากนั้นเรากำหนด SampleService ที่มีการพึ่งพาฐานข้อมูล สังเกตที่ด้านล่างว่าเราส่งออกคลาสไม่ใช่อินสแตนซ์ โปรดจำไว้ว่า database.initialize() จะคืนค่า Promise เมื่อทุกอย่างถูกตั้งค่า ดังนั้นเราจะสร้างบริการในภายหลังเมื่อสัญญาได้รับการแก้ไข

ภายในคลาส เราจะตรวจสอบว่าเอ็นจิ้นฐานข้อมูลใดที่เรามี ในกรณีของ MSSQL เราสร้าง SampleServiceMSSQL มิฉะนั้น SampleServicePostgreSQL ทั้งคู่ได้รับแบบจำลองเป็นข้อโต้แย้งแรกของพวกเขา เหตุผลเดียวกันที่นี่: ซึ่งช่วยให้มั่นใจว่า database.initialize() Promise ได้รับการแก้ไขแล้ว

คลาสเองกำหนดสองวิธี create() แรกจะสร้างรายการใหม่ในฐานข้อมูลตามละติจูดและลองจิจูดที่ให้มา ในการทำเช่นนั้น ออบเจ็กต์จุดจะถูกสร้างขึ้นด้วยประเภทคุณสมบัติของค่า "พอยต์" และพิกัดของคุณสมบัติที่มีอาร์เรย์ที่มีละติจูดและลองจิจูด รูปแบบนี้เรียกว่า GeoJSON และสามารถใช้ได้ตลอดทั้ง SequelizeJS จากนั้นเราเรียกวิธีการสร้างของอแด็ปเตอร์

ทำเช่นเดียวกันกับวิธีที่สอง getAround() จุดประสงค์ของวิธีนี้คือเพื่อให้ได้จุดทั้งหมดในรัศมีรอบละติจูดและลองจิจูดที่กำหนด

โปรดทราบว่าตัวอย่างนี้ไม่มีการตรวจสอบความถูกต้องของข้อมูลป้อนเข้าตามเจตนาเนื่องจากขอบเขตการโพสต์บล็อกนี้

ตอนนี้เรามีฐานข้อมูลและคลาสบริการซึ่งทำหน้าที่เป็นตัวปรับต่อการใช้งานที่เป็นรูปธรรม มาสร้างการใช้งานสำหรับ PostgreSQL และ MSSQL กันเถอะ!

ใช้คลาสอะแด็ปเตอร์ SampleServicePostgreSQL

เราเริ่มต้นด้วยการสร้างคลาส SampleServicePostgreSQL:

นี่คืออะแดปเตอร์สำหรับ PostgreSQL ของเรา การนำวิธีการสร้างไปใช้นั้นตรงไปตรงมามาก ทุกรุ่น SequelizeJS มีวิธีการสร้างซึ่งจะแทรกข้อมูลแบบจำลองลงในฐานข้อมูลพื้นฐาน เนื่องจากการสนับสนุนของ PostGIS เราจึงสามารถเรียก model.create(point) และให้ SequelizeJS ดูแลการแทรกข้อมูลของเราได้อย่างถูกต้อง

มาดูวิธี getAround กัน ดังที่ได้กล่าวไว้ข้างต้น SequelizeJS รองรับ PostGIS น่าเสียดายที่มันเป็นการสนับสนุนขั้นพื้นฐาน รองรับการแทรก อัปเดต และการอ่าน แต่ไม่มีวิธีการอื่นเช่น ST_Distance_Sphere หรือ ST_MakePoint ผ่าน API ที่เป็นนามธรรมที่กำหนดไว้อย่างดี แต่จากปัญหา Github นี้ กำลังมีการหารือกันอยู่ อย่างไรก็ตาม วิธีการดังกล่าวเป็นมาตรฐานเปิดจาก Open Geospatial Consortium (OGC) เราจะเห็นวิธีการเหล่านั้นในภายหลังเมื่อใช้งานอะแดปเตอร์ MS SQL Server

กลับไปที่เมธอด getAround อันดับแรก เราประกาศการสืบค้นแบบกำหนดพารามิเตอร์ เราเลือก id , createdAt และคำนวณระยะทาง โอเครอสักครู่. เกิดอะไรขึ้นที่นี่? เราไม่ได้มีคุณสมบัติ createAt ในโมเดลของเราใช่ไหม เรามี แต่ไม่ชัดเจน ตามค่าเริ่มต้น SequelizeJS จะสร้างคุณสมบัติ createdAt และ updatedAt เพิ่มเติมให้เราโดยอัตโนมัติและติดตามคุณสมบัติเหล่านั้น SequelizeJS จะไม่เป็น SequelizeJS หากคุณไม่สามารถเปลี่ยนพฤติกรรมนี้ได้

แล้วระยะทาง AS ของ ST_Distance_Sphere(ST_MakePoint(:latitude, :longitude), "point") เราใช้ ST_MakePoint เพื่อสร้างจุดจากพารามิเตอร์ละติจูดและลองจิจูดของเรา จากนั้นเราใช้ผลลัพธ์เป็นพารามิเตอร์แรกสำหรับ ST_Distance_Sphere พารามิเตอร์ที่สอง "จุด" อ้างอิงคอลัมน์ตารางของเรา ดังนั้นสำหรับทุกแถวในตาราง SampleModels ของเรา (SequelizeJS จะรวมชื่อตารางเป็นพหูพจน์โดยอัตโนมัติโดยค่าเริ่มต้น) เราคำนวณระยะทางทรงกลม (แม้ว่าจะเป็นวัตถุเรขาคณิตระนาบ) ระหว่างจุดที่กำหนดกับจุดหนึ่งในคอลัมน์ของเรา ระวังที่นี่และอย่าสับสน! ST_Distance_Sphere คำนวณระยะทางด้วยรัศมีเฉลี่ยโลกที่กำหนดที่ 6370986 เมตร หากคุณต้องการใช้ Spheroid จริงตาม SRID ที่กล่าวถึงข้างต้น คุณต้องใช้ ST_DistanceSpheroid

ส่วน WHERE ของการสืบค้นจะใช้เพื่อเลือกเฉพาะข้อมูลที่อยู่ภายในรัศมีที่กำหนดซึ่งแสดงโดยพารามิเตอร์ชื่อ maxDistance สุดท้ายแต่ไม่ท้ายสุด เราเรียกใช้แบบสอบถามนี้กับ PostgreSQL ของเราโดยเรียก model.sequelize.query พารามิเตอร์แรกคือข้อความค้นหาของเรา พารามิเตอร์ที่สองคือบางตัวเลือก ตามที่คุณอาจสังเกตเห็น เราใช้ตัวยึดตำแหน่งที่มีชื่อในข้อความค้นหาของเรา ดังนั้นเราจึงใช้อ็อบเจ็กต์การแทนที่เพื่อบอก SequelizeJS เกี่ยวกับค่าสำหรับตัวยึดตำแหน่ง ละติจูดและลองจิจูดเป็นตัวอธิบาย maxDistance ตั้งไว้ที่ 10 กิโลเมตร ดังนั้นเราจึงได้รับคะแนนในรัศมีที่กำหนดเท่านั้น ด้วยคุณสมบัติ type เราตั้งค่าประเภทของการสืบค้นเป็นคำสั่ง SELECT

จนถึงตอนนี้ ดีมาก อะแดปเตอร์ PostgreSQL ของเราเสร็จเรียบร้อยแล้ว ไปที่อะแดปเตอร์ MSSQL กันเถอะ!

ใช้คลาสอะแด็ปเตอร์ SampleServiceMSSQL

รหัสสำหรับคลาส SampleServiceMSSQL มีดังต่อไปนี้:

มาดูสิ่งนี้ทีละขั้นตอน เนื่องจากขาดการสนับสนุนเรขาคณิตอย่างสมบูรณ์ใน MSSQL เราจึงต้องทำทุกอย่างด้วยตนเองในตอนนี้ ดูวิธีการสร้าง เราเริ่มต้นด้วยการกำหนดข้อความค้นหา INSERT และแทรกค่า: point , createdAt และ updatedAt หากเราดำเนินการสืบค้นข้อมูลดิบ เราจำเป็นต้องดูแลเกี่ยวกับการตั้งค่า createdAt และ updatedAt สำหรับค่าของจุด เราใช้เรขาคณิต::Point($, $, 0) . หากคุณไม่คุ้นเคยกับสตริงเทมเพลตของ JavaScript สิ่งนี้อาจทำให้คุณเสียสายตาเล็กน้อย ไวยากรณ์ $ เพียงแทรกค่าลงในสตริง geometric::Point() เป็น MSSQL ที่เทียบเท่ากับ ST_MakePoint ที่กล่าวถึงข้างต้นโดยมีข้อแตกต่างอย่างหนึ่ง ต้องการให้มีพารามิเตอร์ที่สามคือ SRID เนื่องจากเราไม่ได้ใช้ที่นี่ เราจึงสามารถใช้ 0 ได้

คุณอาจสังเกตเห็นว่าเราไม่ได้ใช้พารามิเตอร์ที่มีชื่อที่นี่ SequelizeJS จะจดจำทุกสิ่งที่ขึ้นต้นด้วยโคลอนโดยอัตโนมัติ ดังนั้นมันจะพยายามแทนที่ :Point ด้วยพารามิเตอร์ที่มีชื่อ โชคดีที่วัตถุแทนที่สามารถเป็นอาร์เรย์ได้เช่นกัน และแทนที่เครื่องหมายคำถามทั้งหมดด้วยค่าที่กำหนดไว้ตามลำดับลักษณะที่ปรากฏ นอกจากนี้ เราจัดหาแบบจำลองทรัพย์สินด้วยมูลค่าของแบบจำลองของเรา สิ่งนี้บอกให้ SequelizeJS จับคู่ผลลัพธ์ของคำสั่ง INSERT กับโมเดลของเราโดยอัตโนมัติ สุดท้าย เราตั้งค่าชนิดของแบบสอบถามเป็น INSERT

ถึงวิธีสุดท้ายของเรา getAround โดยพื้นฐานแล้วจะเหมือนกับอแดปเตอร์ PostgreSQL แต่เนื่องจากเราไม่ได้ใช้ SRID ในการคำนวณ MS SQL Server จะคำนวณบนระนาบ นั่นเป็นเหตุผลที่เราคูณผลลัพธ์ด้วยรัศมีเฉลี่ยของโลกเพื่อให้ได้ระยะทางเป็นเมตร หมายเหตุ: มีความแม่นยำน้อยกว่าเวอร์ชันการคำนวณของ PostgreSQL ด้วย ST_Distance_Sphere เล็กน้อย

ว้าว. หายใจเข้าลึกๆ เราเสร็จสิ้นคลาสฐานข้อมูลและการบริการแล้ว สิ่งสุดท้ายที่ต้องทำคือการประสานกันเล็กน้อยเพื่อลองทุกอย่าง!


1.609344 เป็นปัจจัยในการแปลงไมล์เป็นกิโลเมตร ที่มา: Wikipedia สมมติว่ามิเตอร์เป็นหน่วย! เอกสารประกอบ:

สำหรับเรขาคณิต: ระยะทางถูกระบุในหน่วยที่กำหนดโดยระบบอ้างอิงเชิงพื้นที่ของเรขาคณิต เพื่อให้ฟังก์ชันนี้สมเหตุสมผล เรขาคณิตต้นทางต้องอยู่ในการฉายภาพพิกัดเดียวกัน โดยมี SRID เหมือนกัน

สำหรับหน่วยภูมิศาสตร์เป็นเมตร

ฉันคิดว่า SRID 4326 ของคุณใช้องศา ไม่ เมตร คำแนะนำในคำตอบที่เกี่ยวข้องนี้:

คุณต้องมี ดัชนี บน geo_point เพื่อให้รวดเร็ว (ใช้โดย ST_DWithin() โดยอัตโนมัติ):

นอกจากนี้ยังสามารถใช้ดัชนี GiST เพื่อระบุ ใกล้ที่สุด เพื่อนบ้าน (ร่วมกับจำนวนสูงสุด - LIMIT )


15.2. ฟังก์ชันการจัดทำดัชนีเชิงพื้นที่¶

เฉพาะชุดย่อยของฟังก์ชันเท่านั้นที่จะใช้ประโยชน์จากดัชนีเชิงพื้นที่ หากมี

สี่ตัวแรกเป็นคำที่ใช้บ่อยที่สุดในแบบสอบถาม และ ST_DWithin มีความสำคัญมากสำหรับการทำแบบสอบถามสไตล์ "ภายในระยะทาง" หรือ "ภายในรัศมี" ในขณะที่ยังคงได้รับการเพิ่มประสิทธิภาพจากดัชนี

ในการเพิ่มการเร่งดัชนีให้กับฟังก์ชันอื่นๆ ที่ไม่ได้อยู่ในรายการนี้ (โดยปกติคือ ST_Relate) ให้เพิ่มส่วนคำสั่งเฉพาะดัชนีตามที่อธิบายไว้ด้านล่าง


Postgis ปรับขนาดเช่นเดียวกับมาตราส่วน postgresql ดัชนี postgis จะทำงานเหมือนกับความสัมพันธ์อื่นๆ คุณสามารถตรวจสอบได้ที่นี่

หากคุณดูที่ลิงก์ จะอธิบายว่าลิงก์นั้นทำดัชนีโดยใช้อัลกอริธึมเชิงเรขาคณิตซึ่งดำเนินการในการแทรกแต่ละครั้ง ดังนั้นจึงอาจตอบสนองไม่เพียงพอในแอปแบบเรียลไทม์

ในขณะที่ elasticsearch มีการจัดทำดัชนีตามเวลาจริง โดยอิงตามดัชนี Lucene โดยทั่วไปแล้วการค้นหาแบบยืดหยุ่นจะเหมาะสมกว่าสำหรับแอปพลิเคชันที่ใช้งานหนักแบบเรียลไทม์มากกว่า Postgresql

Postgresql มีข้อได้เปรียบอย่างมากคือความเรียบง่าย การทดสอบและบำรุงรักษาคุณลักษณะดังกล่าวทำได้ง่ายกว่ามากโดยใช้ Postgresql ตัวอย่างเช่น ฉันชอบสร้างต้นแบบตาม Postgresql อย่างรวดเร็ว และถ้ามันเริ่มทำงานได้ไม่ดีเนื่องจากมีการเขียนจำนวนมาก ฯลฯ ฉันเปลี่ยนไปใช้ elasticsearch


เลือก DISTINCT

ข้อกังวลต่อไปคือการใช้ SELECT DISTINCT ซึ่งควรหลีกเลี่ยง เนื่องจากฐานข้อมูลจะต้องนำงานไปขจัดความซ้ำซ้อนของผลลัพธ์ แบบสอบถามที่สร้างขึ้นมาอย่างดีกับฐานข้อมูลที่ถูกทำให้เป็นมาตรฐานควรให้ผลลัพธ์ที่ถูกต้องโดยไม่ต้องมีการขจัดความซ้ำซ้อนดังกล่าว กล่าวอีกนัยหนึ่งถ้าคุณต้องการ SELECT DISTINCT ก็เช่นกัน

  • สคีมาฐานข้อมูลของคุณถูกทำให้เป็นมาตรฐานอย่างไม่ถูกต้อง
  • ฐานข้อมูลของคุณมีข้อมูลขยะ ซึ่งไม่สามารถปฏิเสธได้โดยข้อจำกัดของตาราง
  • แบบสอบถามของคุณถูกสร้างขึ้นมาไม่ดีเพื่อให้ JOIN สร้างแถวที่ซ้ำกัน

ฉันเชื่อว่าคำอธิบายสุดท้ายใช้ที่นี่ คุณดำเนินการ LEFT OUTER JOIN เป็นจำนวนมากสำหรับการค้นหาข้อความ แต่คุณไม่สนใจคอลัมน์ของตารางที่เข้าร่วม การละทิ้งคอลัมน์ของตารางที่เชื่อมกันจะสร้างแถวปลอมขึ้นมา

แทนที่จะใช้ LEFT OUTER JOIN คุณควรใช้ส่วนคำสั่ง WHERE EXISTS กับเคียวรีย่อยที่สัมพันธ์กัน