api/docs/POSTGRESQL_PERFORMANCE_REVIEW.md

گزارش بررسی کارایی 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() شرط به این شکل است:

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 برای هر سطر نتیجه یک زیرپرسش اجرا می‌شود:

(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:

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():

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 و زمان واقعی اندازه‌گیری کنید.

اگر بخواهید در مرحلهٔ بعد روی یکی از موارد (مثلاً فقط وکتور تایل یا فقط جستجو) به‌صورت عملی تغییر کد و اسکریپت پیشنهاد شود، می‌توان همان بخش را دقیق‌تر باز کرد.