PostgreSQL ক্যোয়ারী প্রোফাইলার: কিভাবে প্ল্যান এবং ক্যোয়ারী মেলে

যারা আগে থেকেই ব্যবহার করছেন ব্যাখ্যা.tensor.ru - আমাদের PostgreSQL প্ল্যান ভিজ্যুয়ালাইজেশন পরিষেবা তার একটি সুপার পাওয়ার সম্পর্কে সচেতন নাও হতে পারে - সার্ভার লগের একটি কঠিন-পঠন অংশ চালু করতে ...

… প্রাসঙ্গিক প্ল্যান নোডগুলির জন্য প্রাসঙ্গিক ইঙ্গিত সহ একটি সুন্দরভাবে ডিজাইন করা প্রশ্নের মধ্যে:

তার দ্বিতীয় খণ্ডের এই প্রতিলিপিতে ড PGConf.Russia 2020 এ রিপোর্ট আমরা কীভাবে এটি করতে পেরেছি তা আমি আপনাকে বলব।

প্রথম অংশের ট্রান্সক্রিপ্ট, সাধারণ ক্যোয়ারী পারফরম্যান্স সমস্যা এবং তাদের সমাধানগুলির জন্য উত্সর্গীকৃত, নিবন্ধটিতে পাওয়া যাবে "অসুস্থ এসকিউএল প্রশ্নের জন্য রেসিপি".

প্রথমত, আসুন রঙ করা যাক - এবং আমরা আর পরিকল্পনাটি রঙ করব না, আমরা ইতিমধ্যে এটি আঁকা করেছি, এটি ইতিমধ্যেই সুন্দর এবং বোধগম্য, তবে অনুরোধ।

এটি আমাদের কাছে মনে হয়েছিল যে একটি বিন্যাসহীন "শীট" সহ লগ থেকে টেনে আনা অনুরোধটি খুব কুশ্রী এবং তাই অসুবিধাজনক দেখাচ্ছে।


বিশেষত যখন ডেভেলপাররা কোডে অনুরোধের বডিটিকে "আঠা" করে (এটি অবশ্যই একটি অ্যান্টি-প্যাটার্ন, তবে এটি ঘটে) এক লাইনে। বিভীষিকা !

আসুন এটিকে আরও সুন্দরভাবে আঁকুন।


এবং যদি আমরা এটিকে সুন্দরভাবে আঁকতে পারি, অর্থাৎ, রিকোয়েস্ট বডিটিকে আলাদা করে এবং একত্রিত করতে পারি, তাহলে আমরা এই অনুরোধের প্রতিটি বস্তুর সাথে একটি ইঙ্গিত "সংযুক্ত" করতে পারি - পরিকল্পনার সংশ্লিষ্ট পয়েন্টে কী ঘটেছে।

প্রশ্ন সিনট্যাক্স ট্রি

এটি করার জন্য, ক্যোয়ারীটি প্রথমে পার্স করা আবশ্যক।


কারণ, আমাদের আছে সিস্টেমের মূল NodeJS এ চলে, তারপর আমরা এটির জন্য একটি মডিউল তৈরি করেছি, আপনি করতে পারেন গিথুবে এটি খুঁজুন. প্রকৃতপক্ষে, এগুলি PostgreSQL পার্সারের অভ্যন্তরীণ অংশে বর্ধিত "বাইন্ডিং"। অর্থাৎ, ব্যাকরণটি কেবল বাইনারি সংকলিত এবং NodeJS দ্বারা এটিতে বাইন্ডিং তৈরি করা হয়। আমরা অন্য লোকের মডিউলগুলিকে ভিত্তি হিসাবে নিয়েছি - এখানে কোনও বড় গোপনীয়তা নেই।

আমরা আমাদের ফাংশনে ইনপুট অনুরোধের বডি ফিড করি - আউটপুটে আমরা JSON অবজেক্টের আকারে একটি পার্স করা সিনট্যাক্স ট্রি পাই।


এখন আমরা এই গাছের মধ্য দিয়ে বিপরীত দিকে চালাতে পারি এবং আমরা যে ইন্ডেন্ট, রঙ, বিন্যাস চাই তা দিয়ে অনুরোধটি একত্রিত করতে পারি। না, এটি কনফিগারযোগ্য নয়, তবে আমরা ভেবেছিলাম যে এটি সুবিধাজনক হবে।

ম্যাপিং কোয়েরি এবং প্ল্যান নোড

এখন দেখা যাক কিভাবে আমরা প্রথম ধাপে যে পরিকল্পনাটি বিশ্লেষণ করেছি এবং দ্বিতীয় ধাপে আমরা যে প্রশ্নটি বিশ্লেষণ করেছি তা একত্রিত করতে পারি।

আসুন একটি সাধারণ উদাহরণ নেওয়া যাক - আমাদের একটি অনুরোধ রয়েছে যা একটি CTE গঠন করে এবং এটি দুবার পড়ে। সে এমন একটি পরিকল্পনা তৈরি করে।

কোটে

আপনি যদি এটি মনোযোগ সহকারে দেখেন তবে 12 তম সংস্করণের আগে (বা এটি থেকে কীওয়ার্ড দিয়ে শুরু করুন MATERIALIZED) গঠন CTE শিডিউলারের জন্য একটি নিঃশর্ত বাধা.


এবং, এর মানে হল, যদি আমরা অনুরোধে কোথাও দেখতে পাই CTE এর জেনারেশন এবং কোথাও প্ল্যান নোড CTE, তারপর এই নোডগুলি নিজেদের মধ্যে স্বতন্ত্রভাবে "লড়াই" করে, আমরা অবিলম্বে তাদের একত্রিত করতে পারি।

"একটি তারকাচিহ্ন সহ" টাস্কদ্রষ্টব্য: CTEs নেস্ট করা যেতে পারে।

খুব খারাপ বাসা আছে, এবং এমনকি একই নাম. উদাহরণস্বরূপ, আপনি ভিতরে করতে পারেন CTE A করা CTE X, এবং ভিতরে একই স্তরে CTE B আবার কর CTE X:

WITH A AS (
  WITH X AS (...)
  SELECT ...
)
, B AS (
  WITH X AS (...)
  SELECT ...
)
...

তুলনা করার সময়, আপনাকে অবশ্যই এটি বুঝতে হবে। এটি "চোখ" দিয়ে বোঝা খুব কঠিন - এমনকি পরিকল্পনা দেখে, এমনকি অনুরোধের শরীর দেখেও। যদি আপনার CTE জেনারেশন জটিল হয়, নেস্টেড হয়, অনুরোধগুলি বড় হয়, তাহলে এটি সম্পূর্ণ অচেতন।

মিলন

আমাদের অনুরোধে একটি কীওয়ার্ড থাকলে UNION [ALL] (দুটি নমুনা যোগদানের অপারেটর), তারপর এটি পরিকল্পনার সাথে নোডের সাথে মিলে যায় Append, অথবা কিছু Recursive Union.


যা "উপরে" UNION - এটি আমাদের নোডের প্রথম সন্তান, যা "নীচ থেকে" - দ্বিতীয়টি। মাধ্যমে যদি UNION আমরা একসাথে বেশ কয়েকটি ব্লক "আঠা" করেছি, তারপর Append-নোডের এখনও কেবল একটিই থাকবে, তবে এটিতে দুটি সন্তান থাকবে না, তবে অনেকগুলি থাকবে - যথাক্রমে তারা যেতে হবে:

  (...) -- #1
UNION ALL
  (...) -- #2
UNION ALL
  (...) -- #3

Append
  -> ... #1
  -> ... #2
  -> ... #3

"একটি তারকাচিহ্ন সহ" টাস্ক: পুনরাবৃত্ত আনা প্রজন্মের ভিতরে (WITH RECURSIVE) একাধিক হতে পারে UNION. কিন্তু শেষের পরে শুধুমাত্র একেবারে শেষ ব্লকটি সর্বদা পুনরাবৃত্ত হয় UNION. উপরের সবকিছু এক কিন্তু ভিন্ন UNION:

WITH RECURSIVE T AS(
  (...) -- #1
UNION ALL
  (...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
  (...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...

এই ধরনের উদাহরণগুলিও "পেস্ট" করতে সক্ষম হওয়া দরকার। এই উদাহরণে, আমরা যে দেখতে UNION-আমাদের অনুরোধে সেগমেন্ট ছিল 3 টুকরা। সেই অনুযায়ী, এক UNION অনুরূপ Append-নোড, এবং অন্যান্য - Recursive Union.

তথ্য-পড়ুন

সবকিছু, পাড়া, এখন আমরা জানি অনুরোধের কোন অংশটি পরিকল্পনার কোন অংশের সাথে মিলে যায়। এবং এই টুকরাগুলিতে আমরা সহজেই এবং স্বাভাবিকভাবে সেই বস্তুগুলি খুঁজে পেতে পারি যেগুলি "পঠনযোগ্য"।

ক্যোয়ারীটির দৃষ্টিকোণ থেকে, আমরা জানি না এটি একটি টেবিল বা একটি CTE, তবে তারা একই নোড দ্বারা চিহ্নিত করা হয় RangeVar. এবং "পঠনযোগ্য" পরিকল্পনায়, এটি নোডগুলির একটি মোটামুটি সীমিত সেট:

• Seq Scan on [tbl]
• Bitmap Heap Scan on [tbl]
• Index [Only] Scan [Backward] using [idx] on [tbl]
• CTE Scan on [cte]
• Insert/Update/Delete on [tbl]

আমরা পরিকল্পনা এবং অনুরোধের কাঠামো জানি, আমরা ব্লকগুলির চিঠিপত্র জানি, আমরা বস্তুর নাম জানি - আমরা একটি দ্ব্যর্থহীন তুলনা করি।


আবার কাজ "একটি তারকাচিহ্ন দিয়ে". আমরা একটি অনুরোধ গ্রহণ করি, এটি কার্যকর করি, আমাদের কোনো উপনাম নেই - আমরা এটি শুধুমাত্র একটি CTE থেকে দুবার পড়ি।


প্ল্যানটা দেখে নেওয়া যাক- অসুবিধা কী? কেন আমরা একটি উপনাম আছে? আমরা এটা অর্ডার করিনি। তিনি কোথা থেকে এত "সংখ্যা" পান?

PostgreSQL এটি নিজেই যোগ করে। আপনি শুধু এটা বুঝতে হবে ঠিক যেমন একটি উপনাম আমাদের জন্য, পরিকল্পনার সাথে তুলনা করার উদ্দেশ্যে, এটির কোন মানে হয় না, এটি এখানে সহজভাবে যোগ করা হয়েছে। আসুন তার দিকে মনোযোগ না দেই।

দ্বিতীয় কাজ "একটি তারকাচিহ্ন দিয়ে": যদি আমরা একটি বিভাজিত টেবিল থেকে পড়ি, তাহলে আমরা একটি নোড পাব Append বা Merge Append, যা একটি বৃহৎ সংখ্যক "শিশু" নিয়ে গঠিত হবে, এবং যার প্রত্যেকটি কিছু হবে Scanটেবিল-বিভাগ থেকে 'om: Seq Scan, Bitmap Heap Scan বা Index Scan. তবে, যে কোনও ক্ষেত্রে, এই "শিশুরা" জটিল প্রশ্ন হবে না - এইভাবে এই নোডগুলিকে আলাদা করা যেতে পারে Append এ UNION.


আমরা এই জাতীয় গিঁটগুলিও বুঝি, আমরা সেগুলি "এক গাদাতে" সংগ্রহ করি এবং বলি: "আপনি মেগাটেবল থেকে যা কিছু পড়েছেন তা এখানে এবং গাছের নিচে রয়েছে".

"সহজ" ডেটা অধিগ্রহণ নোড

Values Scan পরিকল্পনা অনুরূপ VALUES অনুরোধে

Result ছাড়া একটি অনুরোধ FROM মত SELECT 1. অথবা যখন আপনি একটি মিথ্যা অভিব্যক্তি আছে WHERE-ব্লক করুন (তারপর বৈশিষ্ট্যটি উপস্থিত হয় One-Time Filter):

EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1

Result  (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
  One-Time Filter: false

Function Scan একই নামের SRF-এ "Mapyatsya"।

কিন্তু নেস্টেড প্রশ্নগুলির সাথে, সবকিছু আরও জটিল - দুর্ভাগ্যবশত, তারা সর্বদা পরিণত হয় না InitPlan/SubPlan. কখনও কখনও তারা পরিণত ... Join বা ... Anti Join, বিশেষ করে যখন আপনি এমন কিছু লেখেন WHERE NOT EXISTS .... এবং সেখানে একত্রিত করা সর্বদা সম্ভব নয় - পরিকল্পনার পাঠ্যে পরিকল্পনার নোডগুলির সাথে সম্পর্কিত কোনও অপারেটর নেই।

আবার কাজ "একটি তারকাচিহ্ন দিয়ে": কিছু VALUES অনুরোধে এই ক্ষেত্রে এবং পরিকল্পনায় আপনি বেশ কয়েকটি নোড পাবেন Values Scan.


"সংখ্যা" প্রত্যয়গুলি তাদের একে অপরের থেকে আলাদা করতে সাহায্য করবে - এটি ঠিক সেই ক্রমে যুক্ত করা হয়েছে যেখানে সংশ্লিষ্ট VALUES-উপর থেকে নিচ পর্যন্ত অনুরোধের সময় ব্লক।

তথ্য প্রক্রিয়াজাতকরণ

মনে হচ্ছে আমাদের অনুরোধের সবকিছুই সাজানো হয়েছে - শুধুমাত্র Limit.


কিন্তু সবকিছু এখানে সহজ - যেমন নোড Limit, Sort, Aggregate, WindowAgg, Unique তারা অনুরোধে সংশ্লিষ্ট অপারেটরদের সাথে ওয়ান-টু-ওয়ান "ম্যাপ" করে, যদি তারা সেখানে থাকে। কোন "তারকা" এবং কোন অসুবিধা নেই।

JOIN

আমরা যখন একত্রিত করতে চাই তখন অসুবিধা দেখা দেয় JOIN নিজেদের মধ্যে এটি সবসময় সম্ভব নয়, তবে এটি সম্ভব।


ক্যোয়ারী পার্সার এর দৃষ্টিকোণ থেকে, আমাদের একটি নোড আছে JoinExpr, যার ঠিক দুটি সন্তান রয়েছে - বাম এবং ডান। এটি, যথাক্রমে, আপনার যোগদানের "উপরে" এবং ক্যোয়ারীতে "নীচে" যা লেখা আছে তা।

আর পরিকল্পনার দিক থেকে এরা দুজন কারো কারো বংশধর * Loop/* Join-নোড। Nested Loop, Hash Anti Join... এরকম কিছু।

আসুন সহজ লজিক ব্যবহার করি: যদি আমাদের টেবিল A ​​এবং B থাকে যা পরিকল্পনায় একে অপরকে "যোগদান" করে, তাহলে ক্যোয়ারীতে তারা উভয়ই অবস্থিত হতে পারে A-JOIN-B, বা B-JOIN-A. এর মত একত্রিত করার চেষ্টা করা যাক, বিপরীত উপায়ে একত্রিত করার চেষ্টা করুন, এবং এই ধরনের জোড়া ফুরিয়ে না যাওয়া পর্যন্ত।

আমাদের সিনট্যাক্স ট্রি নিন, আমাদের পরিকল্পনা নিন, তাদের দিকে তাকান... এটা মনে হচ্ছে না!


গ্রাফ আকারে এটি পুনরায় আঁকা যাক - ওহ, কিছু ইতিমধ্যে কিছু অনুরূপ হয়ে গেছে!


আসুন লক্ষ্য করা যাক যে আমাদের নোড রয়েছে যেগুলিতে একই সময়ে শিশু B এবং C রয়েছে - এটি কোন ক্রমে আমাদের কাছে বিবেচ্য নয়। আসুন তাদের একত্রিত করি এবং নোডের ছবি ফ্লিপ করি।


আবার দেখা যাক. এখন আমাদের কাছে শিশুদের A এবং জোড়া (B + C) সহ নোড রয়েছে - তাদের সাথে সামঞ্জস্যপূর্ণ।


দারুণ! দেখা যাচ্ছে আমরা এই দুজন JOIN পরিকল্পনার নোডের সাথে ক্যোয়ারী থেকে সফলভাবে একত্রিত করা হয়েছে।

হায়, এই সমস্যা সবসময় সমাধান করা হয় না.


উদাহরণস্বরূপ, যদি অনুরোধ A JOIN B JOIN C, এবং পরিকল্পনায়, "চরম" নোডগুলি A এবং C প্রথমে সংযুক্ত ছিল৷ কিন্তু কোয়েরিতে এমন কোনও অপারেটর নেই, আমাদের হাইলাইট করার কিছু নেই, ইঙ্গিতটি আবদ্ধ করার কিছু নেই৷ আপনি যখন লিখবেন তখন "কমা" এর সাথে একই A, B.

তবে, বেশিরভাগ ক্ষেত্রে, প্রায় সমস্ত নোডগুলি "আনটাই" করতে এবং সময়মতো বাম দিকে এই জাতীয় প্রোফাইলিং পেতে পরিচালনা করে - আক্ষরিক অর্থে, গুগল ক্রোমের মতো, আপনি যখন জাভাস্ক্রিপ্ট কোড বিশ্লেষণ করেন। আপনি দেখতে পারেন কতক্ষণ প্রতিটি লাইন এবং প্রতিটি বিবৃতি "সম্পাদিত"।


এবং আপনার জন্য এই সমস্ত ব্যবহার করা সহজ করার জন্য, আমরা স্টোরেজ তৈরি করেছি আর্কাইভ, যেখানে আপনি সংরক্ষণ করতে পারেন এবং তারপরে সংশ্লিষ্ট প্রশ্নের সাথে আপনার পরিকল্পনাগুলি খুঁজে পেতে পারেন বা কারও সাথে একটি লিঙ্ক ভাগ করতে পারেন৷

আপনার যদি পর্যাপ্ত আকারে একটি অপঠনযোগ্য অনুরোধ আনতে হয় তবে ব্যবহার করুন আমাদের "নরমালাইজার".

উত্স: www.habr.com