214 lines
16 KiB
Markdown
214 lines
16 KiB
Markdown
# گزارش بررسی کارایی PostgreSQL (پروژه memaps.ir)
|
||
|
||
این سند نتیجهٔ بررسی دیتابیس PostgreSQL (دیتابیس `gis` با PostGIS و pgRouting) از نظر **کارایی** و **زمان پاسخ‌گویی** است. فعلاً **هیچ تغییری اعمال نشده** و فقط پیشنهادها جمع‌بندی شده‌اند.
|
||
|
||
---
|
||
|
||
## ۱. نقشهٔ استفاده از دیتابیس
|
||
|
||
| بخش | فایل(ها) | جداول اصلی | نوع کوئری |
|
||
|-----|----------|------------|-----------|
|
||
| وکتور تایل (MVT) | `VectorTileController` | `planet_osm_line`, `planet_osm_polygon`, `planet_osm_point` | فضایی: برش با bbox تایل در 3857 |
|
||
| جستجوی OSM | `OsmSearchRepository` | `planet_osm_point`, `planet_osm_line`, `planet_osm_polygon` | فضایی + فیلتر نام/نوع، مرتب‌سازی فاصله |
|
||
| جستجوی متنی | `SearchController` | `planet_osm_*` + زیرپرسش برای شهر | `ILIKE` روی نام + زیرپرسش شهر |
|
||
| مسیریابی ماتریس | `MatrixRoutingRepository` | `routing.ways`, `routing.ways_vertices_pgr` | Snap نقطه، `pgr_dijkstra`، خواندن هندسه یال‌ها |
|
||
|
||
---
|
||
|
||
## ۲. مشکلات و پیشنهادها (بدون اعمال تغییر)
|
||
|
||
### ۲.۱ وکتور تایل — استفاده نکردن از اندیس فضایی
|
||
|
||
**وضعیت فعلی:**
|
||
در `VectorTileController::fetchMvt()` شرط به این شکل است:
|
||
|
||
```sql
|
||
ST_Intersects(ST_Transform(l.way, 3857), {$bbox})
|
||
```
|
||
|
||
یعنی ستون `way` (که اندیس GIST معمولاً روی همان است) داخل `ST_Transform` قرار دارد. در این حالت **اندیس فضایی روی `way` در کوئری استفاده نمی‌شود** و پلن به سمت Sequential Scan و تبدیل تمام ردیف‌ها می‌رود؛ روی جداول بزرگ بسیار کند است.
|
||
|
||
**پیشنهاد (برای کمترین زمان پاسخ):**
|
||
|
||
- **حالت ایده‌آل:** bbox تایل را به SRID ذخیره‌سازی جدول (معمولاً 4326 یا 3857 در osm2pgsql) تبدیل کنید و شرط را طوری بنویسید که **فقط یک هندسه (bbox) تبدیل شود**، نه ستون جدول:
|
||
- اگر `way` در دیتابیس با SRID 4326 است:
|
||
- بbox را در 3857 بسازید، به 4326 تبدیل کنید و با `way` مقایسه کنید:
|
||
`ST_Intersects(way, ST_Transform(bbox_3857, 4326))`
|
||
- سپس در لایهٔ اپلیکیشن برای MVT دوباره به 3857 تبدیل کنید (یا در SELECT فقط برای خروجی).
|
||
- اگر `way` از قبل در 3857 است، همان bbox را مستقیم با `way` استفاده کنید و اصلاً جدول را تبدیل نکنید.
|
||
- **جایگزین:** اگر ناچارید همیشه با 3857 کار کنید، یک ستون یا **functional index** روی `ST_Transform(way, 3857)` در نظر بگیرید (هزینهٔ دیسک و نگهداری دارد؛ معمولاً تبدیل bbox ارجح است).
|
||
|
||
با این تغییر، انتظار می‌رود زمان پاسخ وکتور تایل به‌طور محسوسی کم شود، به‌ویژه در زومهای پایین و روی جداول بزرگ.
|
||
|
||
---
|
||
|
||
### ۲.۲ جستجو (SearchController) — زیرپرسش تکراری برای شهر
|
||
|
||
**وضعیت فعلی:**
|
||
در `searchPoints`, `searchStreets`, `searchAreas` برای **هر سطر نتیجه** یک زیرپرسش اجرا می‌شود:
|
||
|
||
```sql
|
||
(SELECT name FROM planet_osm_polygon
|
||
WHERE admin_level = '8'
|
||
AND ST_Contains(way, ST_Transform(p.way, 4326))
|
||
LIMIT 1) AS city
|
||
```
|
||
|
||
این یک **correlated subquery** است: به ازای هر ردیف خروجی، یک بار جستجو در `planet_osm_polygon` انجام می‌شود. با ۲۰ نتیجه، دست‌کم ۲۰ بار این زیرپرسش اجرا می‌شود و زمان پاسخ را زیاد می‌کند.
|
||
|
||
**پیشنهاد:**
|
||
|
||
- به‌جای زیرپرسش، از **JOIN LATERAL** یا **JOIN** با یک بار اسکن مناسب استفاده کنید تا شهر فقط یک بار (یا به ازای هر نقطه با یک پلن بهینه) محاسبه شود.
|
||
- روی `planet_osm_polygon` برای این استفاده:
|
||
- اندیس GIST روی `way`
|
||
- فیلتر `boundary = 'administrative' AND admin_level = '8'` در صورت امکان در اندیس (partial index) یا حداقل در شرط کوئری.
|
||
|
||
با این کار زمان پاسخ API جستجو باید کاهش محسوسی داشته باشد.
|
||
|
||
---
|
||
|
||
### ۲.۳ جستجوی اطراف (OsmSearchRepository) — واحد فاصله و اندیس
|
||
|
||
**وضعیت فعلی:**
|
||
در `searchNearbyPlaces`:
|
||
|
||
```sql
|
||
WHERE ST_DWithin(way, ST_SetSRID(ST_Point(?, ?), 4326), ?)
|
||
```
|
||
|
||
اگر ستون `way` از نوع **geometry** (و نه geography) و SRID 4326 باشد، پارامتر سوم `ST_DWithin` بر حسب **درجه** است. در حالی که در کد `$radius = 1000` (احتمالاً به متر) پاس داده می‌شود. در این حالت عدد 1000 به‌عنوان 1000 درجه تفسیر می‌شود و هم نتیجه اشتباه است هم ممکن است پلن بسیار سنگین شود.
|
||
|
||
**پیشنهاد:**
|
||
|
||
- یا از **geography** استفاده کنید (مثلاً `way::geography` و فاصله به متر)، یا اگر با geometry کار می‌کنید، شعاع را به درجه تبدیل کنید (تقریبی: `radius_m / 111320.0` برای عرض‌های میانه).
|
||
- برای کارایی: اگر از geography استفاده می‌کنید، در صورت امکان ستون جداگانهٔ geography یا اندیس روی `way::geography` در نظر بگیرید تا محدودهٔ فاصله با اندیس پوشش داده شود.
|
||
|
||
---
|
||
|
||
### ۲.۴ مکان‌های پرطرفدار — ORDER BY RANDOM()
|
||
|
||
**وضعیت فعلی:**
|
||
در `OsmSearchRepository::getPopularPlaces()`:
|
||
|
||
```sql
|
||
ORDER BY RANDOM()
|
||
LIMIT ?
|
||
```
|
||
|
||
`ORDER BY RANDOM()` باعث می‌شود کل جدول (یا بخش بزرگی از آن) اسکن شود و برای هر ردیف یک عدد تصادفی تولید شود. روی `planet_osm_point` با حجم بالا این کوئری **بسیار سنگین** است و هیچ اندیسی کمکی نمی‌کند.
|
||
|
||
**پیشنهاد:**
|
||
|
||
- اگر واقعاً به تصادفی بودن نیاز دارید:
|
||
- از **TABLESAMPLE** (مثلاً `FROM planet_osm_point TABLESAMPLE BERNOULLI(1)`) استفاده کنید و در صورت نیاز بعد از آن با شرط و LIMIT فیلتر کنید.
|
||
- یا محدودهٔ مکانی/شناسه را محدود کنید و فقط از آن زیرمجموعه تصادفی بگیرید (مثلاً با `WHERE random() < 0.01` و سپس LIMIT؛ یا با offset تصادفی روی یک زیرمجموعه).
|
||
- اگر «پرطرفدار» به معنی ثابت/قابل کش است، به‌جای RANDOM می‌توان لیست ثابت یا بر اساس یک معیار (مثلاً ترافیک یا دستی) برگرداند و در اپلیکیشن کش کرد.
|
||
|
||
این تغییر به‌طور مستقیم زمان پاسخ این endpoint را کم می‌کند.
|
||
|
||
---
|
||
|
||
### ۲.۵ مسیریابی ماتریس — تعداد فراخوانی الگوریتم
|
||
|
||
**وضعیت فعلی:**
|
||
در `MatrixRoutingRepository::computeCostMatrix()` برای **هر مبدأ** یک بار `pgr_dijkstra` (از طریق `runDijkstraToMultiple`) صدا زده می‌شود. با ۲۵ مبدأ و ۲۵ مقصد، تا ۲۵ بار الگوریتم اجرا می‌شود. هر بار هم کوئری لبه‌ها به صورت متن به pgRouting داده می‌شود:
|
||
`SELECT id, source, target, cost, reverse_cost FROM routing.ways`.
|
||
|
||
**پیشنهاد:**
|
||
|
||
- اگر نسخهٔ pgRouting شما **pgr_dijkstraCost** (یا تابع مشابه ماتریس مبدأ–مقصد) را پشتیبانی می‌کند، با یک فراخوانی و آرایهٔ مبدأها و مقصدها ماتریس هزینه را بگیرید تا تعداد اجرای الگوریتم و ر round-trip به دیتابیس کم شود.
|
||
- جدول `routing.ways` از قبل اندیس روی `source` و `target` دارد (در `setup-routing-graph.sql`); اگر پلن اجرا هنوز سنگین است، می‌توان با **ANALYZE routing.ways** آمار را به‌روز نگه داشت و در صورت نیاز اندیس مرکب (source, target) را تست کرد.
|
||
|
||
این موارد بیشتر زمان پاسخ درخواست‌های ماتریس را هدف می‌گیرند.
|
||
|
||
---
|
||
|
||
### ۲.۶ اسنپ نقطه (routing.ways_vertices_pgr)
|
||
|
||
**وضعیت فعلی:**
|
||
کوئری اسنپ با `ST_DWithin(the_geom::geography, ...)` و `ORDER BY ST_Distance(the_geom::geography, ...)` نوشته شده است. اندیس موجود در اسکریپت روی **geometry** است:
|
||
`idx_routing_ways_vertices_the_geom ON routing.ways_vertices_pgr USING GIST(the_geom)`.
|
||
|
||
**پیشنهاد:**
|
||
|
||
- در برخی نسخه‌های PostGIS، cast به geography ممکن است استفاده از اندیس geometry را محدود کند. در صورت مشاهدهٔ کندی این کوئری در پلن اجرا:
|
||
- یا با **geometry** و واحد درجه (با تبدیل شعاع به درجه) کار کنید تا همان اندیس GIST روی `the_geom` استفاده شود،
|
||
- یا اگر ترجیح می‌دهید با متر کار کنید، ستون/اندیس جدا برای **geography** در نظر بگیرید و کوئری را روی آن بنویسید.
|
||
|
||
هدف، استفادهٔ پایدار از یک اندیس فضایی برای اسنپ است.
|
||
|
||
---
|
||
|
||
### ۲.۷ اندیس‌های جداول OSM (planet_osm_*)
|
||
|
||
**وضعیت فعلی:**
|
||
اسکریپت پروژه فقط اندیس‌های **routing** را تعریف می‌کند. جداول `planet_osm_point`, `planet_osm_line`, `planet_osm_polygon` معمولاً با **osm2pgsql** پر می‌شوند؛ بسته به تنظیمات، ممکن است اندیس GIST روی `way` از قبل وجود داشته باشد یا نباشد.
|
||
|
||
**پیشنهاد:**
|
||
|
||
- روی سرور دیتابیس اجرا کنید و وجود و نوع اندیس‌ها را بررسی کنید:
|
||
- `\di *planet_osm*` در `psql` یا
|
||
- `SELECT indexname, indexdef FROM pg_indexes WHERE tablename LIKE 'planet_osm_%';`
|
||
- اگر روی ستون `way` هیچ اندیس GIST نیست، حتماً یکی ایجاد کنید (حداقل برای جداولی که در وکتور تایل و جستجو استفاده می‌شوند).
|
||
- برای کوئری‌های وکتور تایل که با **highway / building / name|amenity|shop|tourism** فیلتر می‌شوند، می‌توان (مشابه openstreetmap-carto) **partial GIST index** روی `way` با همان شرط‌ها در نظر گرفت تا حجم اندیس و زمان جستجو بهتر شود (مثلاً برای خطوط با `highway IS NOT NULL`).
|
||
|
||
این کار باعث می‌شود پس از اصلاح شرط فضایی (تبدیل bbox به SRID جدول)، پلن واقعاً از اندیس استفاده کند.
|
||
|
||
---
|
||
|
||
### ۲.۸ اتصال و هم‌زمانی
|
||
|
||
**وضعیت فعلی:**
|
||
در هر درخواست، از داخل کنترلر/ریپازیتوری یک **اتصال PDO جدید** به PostgreSQL باز می‌شود و پس از اتمام درخواست بسته می‌شود. در بار هم‌زمانی بالا، تعداد اتصال و ایجاد/بستن مکرر می‌تواند به‌خودی‌خود زمان پاسخ و بار سرور را افزایش دهد.
|
||
|
||
**پیشنهاد (برای محیط پرترافیک):**
|
||
|
||
- استفاده از **PgBouncer** (یا مشابه) در حالت connection pooling تا تعداد اتصال واقعی به PostgreSQL محدود بماند و اتصال‌ها reuse شوند.
|
||
- در صورت امکان، در اپلیکیشن از یک connection pool (مثلاً از طریق Doctrine DBAL یا تنظیمات وب‌سرور/PHP) استفاده کنید تا با حداقل تغییر در کد، اتصال‌ها مدیریت شوند.
|
||
|
||
این موارد بیشتر برای کاهش تأخیر و پایداری تحت بار است تا بهینه‌سازی یک کوئری خاص.
|
||
|
||
---
|
||
|
||
### ۲.۹ آمار و پلن اجرا
|
||
|
||
**پیشنهاد کلی:**
|
||
|
||
- به‌طور دوره‌ای **ANALYZE** روی جداول پرکاربرد اجرا شود (مثلاً پس از هر به‌روزرسانی OSM یا بازسازی گراف مسیریابی):
|
||
- `ANALYZE planet_osm_point;`
|
||
- `ANALYZE planet_osm_line;`
|
||
- `ANALYZE planet_osm_polygon;`
|
||
- `ANALYZE routing.ways;`
|
||
- `ANALYZE routing.ways_vertices_pgr;`
|
||
- برای کوئری‌های کند، **EXPLAIN (ANALYZE, BUFFERS)** بگیرید و بررسی کنید:
|
||
- آیا اندیس استفاده می‌شود؟
|
||
- آیا Sequential Scan روی جدول بزرگ وجود دارد؟
|
||
- آیا تبدیل هندسه روی ستون اندیس‌دار انجام می‌شود؟
|
||
|
||
این کار به شما کمک می‌کند اولویت تغییرات بعدی (اندیس، بازنویسی شرط فضایی، بازنویسی زیرپرسش شهر) را بر اساس دادهٔ واقعی تنظیم کنید.
|
||
|
||
---
|
||
|
||
## ۳. جمع‌بندی اولویت‌ها
|
||
|
||
| اولویت | موضوع | اثر تقریبی روی زمان پاسخ |
|
||
|--------|--------|---------------------------|
|
||
| بالا | وکتور تایل: تبدیل bbox به SRID جدول به‌جای تبدیل `way` | کاهش زیاد برای تایل‌ها |
|
||
| بالا | جستجو: حذف زیرپرسش تکراری شهر (JOIN/LATERAL) | کاهش محسوس برای API جستجو |
|
||
| متوسط | جستجوی اطراف: واحد فاصله (درجه/متر) و در صورت نیاز geography/اندیس | درست بودن نتیجه + کارایی |
|
||
| متوسط | مکان‌های پرطرفدار: جایگزینی ORDER BY RANDOM() | کاهش زیاد برای این endpoint |
|
||
| متوسط | مسیریابی ماتریس: pgr_dijkstraCost یا کاهش تعداد فراخوانی | کاهش زمان ماتریس |
|
||
| پایین | اسنپ رأس: هماهنگی geography/geometry با اندیس | بهبود در صورت کندی اسنپ |
|
||
| پایین | اندیس‌های GIST/partial روی planet_osm_* | استفادهٔ بهتر از تغییر وکتور تایل و جستجو |
|
||
| زیرساخت | Connection pooling (PgBouncer)، ANALYZE دوره‌ای | پایداری و کاهش تأخیر تحت بار |
|
||
|
||
---
|
||
|
||
## ۴. گام‌های پیشنهادی بعدی (بدون اعمال خودکار)
|
||
|
||
1. روی یک کپی از دیتابیس یا محیط تست، وجود و نوع اندیس‌های `planet_osm_*` و `routing.*` را با `pg_indexes` و در صورت نیاز `EXPLAIN` بررسی کنید.
|
||
2. برای یک درخواست وکتور تایل نمونه، قبل و بعد از تغییر شرط به «تبدیل bbox به SRID جدول»، **EXPLAIN (ANALYZE, BUFFERS)** بگیرید و پلن را مقایسه کنید.
|
||
3. برای یک جستجوی نمونه با lat/lng، همین کار را برای کوئری جستجو (با و بدون جایگزینی زیرپرسش شهر) انجام دهید.
|
||
4. پس از اطمینان از نتیجه و پلن، تغییرات را مرحله‌به‌مرحله در کد و در صورت نیاز در اسکریپت‌های SQL اعمال کنید و دوباره با EXPLAIN و زمان واقعی اندازه‌گیری کنید.
|
||
|
||
اگر بخواهید در مرحلهٔ بعد روی یکی از موارد (مثلاً فقط وکتور تایل یا فقط جستجو) به‌صورت عملی تغییر کد و اسکریپت پیشنهاد شود، می‌توان همان بخش را دقیق‌تر باز کرد.
|