PostgreSQL ప్రశ్న ప్రొఫైలర్: ప్లాన్ మరియు క్వెరీని ఎలా మ్యాచ్ చేయాలి

ఇప్పటికే చాలా మంది ఉపయోగిస్తున్నారు వివరించండి.tensor.ru - మా PostgreSQL ప్లాన్ విజువలైజేషన్ సర్వీస్‌కి దాని సూపర్ పవర్‌లలో ఒకదాని గురించి తెలియకపోవచ్చు - సర్వర్ లాగ్‌లోని ఒక హార్డ్-టు-రీడ్ భాగాన్ని మార్చడం...

... సంబంధిత ప్లాన్ నోడ్‌ల కోసం సందర్భోచిత సూచనలతో అందంగా రూపొందించిన ప్రశ్నలోకి:

అతని రెండవ భాగం యొక్క ఈ ట్రాన్స్క్రిప్ట్లో PGConf.Russia 2020లో నివేదిక మేము దీన్ని ఎలా నిర్వహించగలిగామో నేను మీకు చెప్తాను.

మొదటి భాగం యొక్క ట్రాన్స్క్రిప్ట్, సాధారణ ప్రశ్న పనితీరు సమస్యలు మరియు వాటి పరిష్కారాలకు అంకితం చేయబడింది, వ్యాసంలో చూడవచ్చు "అనారోగ్య SQL ప్రశ్నల కోసం వంటకాలు".

మొదట, కలరింగ్ ప్రారంభిద్దాం - మరియు మేము ఇకపై ప్లాన్‌కు రంగు వేయము, మేము ఇప్పటికే రంగులు వేసుకున్నాము, మేము ఇప్పటికే అందంగా మరియు అర్థమయ్యేలా కలిగి ఉన్నాము, కానీ ఒక అభ్యర్థన.

అటువంటి ఫార్మాట్ చేయని "షీట్" తో లాగ్ నుండి తీసిన అభ్యర్థన చాలా అసహ్యంగా కనిపిస్తుంది మరియు అందువల్ల అసౌకర్యంగా ఉన్నట్లు మాకు అనిపించింది.


డెవలపర్లు కోడ్‌లోని అభ్యర్థన యొక్క శరీరాన్ని "జిగురు" చేసినప్పుడు (ఇది, వాస్తవానికి, యాంటీప్యాటర్న్, కానీ ఇది జరుగుతుంది) ఒక లైన్‌లో. భయంకరమైనది!

దీన్ని మరింత అందంగా గీద్దాం.


మరియు మేము దీన్ని అందంగా గీయగలిగితే, అనగా, అభ్యర్థన యొక్క శరీరాన్ని విడదీసి, తిరిగి కలపగలిగితే, అప్పుడు మేము ఈ అభ్యర్థన యొక్క ప్రతి వస్తువుకు సూచనను “అటాచ్” చేయవచ్చు - ప్లాన్‌లోని సంబంధిత పాయింట్ వద్ద ఏమి జరిగింది.

ప్రశ్న సింటాక్స్ చెట్టు

దీన్ని చేయడానికి, అభ్యర్థనను ముందుగా అన్వయించాలి.


ఎందుకంటే మన దగ్గర ఉంది సిస్టమ్ యొక్క ప్రధాన భాగం NodeJSపై నడుస్తుంది, అప్పుడు మేము దాని కోసం ఒక మాడ్యూల్ చేసాము, మీరు చెయ్యగలరు దానిని GitHubలో కనుగొనండి. వాస్తవానికి, ఇవి PostgreSQL పార్సర్ యొక్క అంతర్గత భాగాలకు విస్తరించబడిన “బైండింగ్‌లు”. అంటే, వ్యాకరణం కేవలం బైనరీ కంపైల్ చేయబడింది మరియు దానికి NodeJS నుండి బైండింగ్‌లు చేయబడతాయి. మేము ఇతరుల మాడ్యూళ్ళను ప్రాతిపదికగా తీసుకున్నాము - ఇక్కడ పెద్ద రహస్యం లేదు.

మేము అభ్యర్థన యొక్క శరీరాన్ని మా ఫంక్షన్‌కు ఇన్‌పుట్‌గా అందిస్తాము - అవుట్‌పుట్ వద్ద మేము JSON ఆబ్జెక్ట్ రూపంలో అన్వయించిన సింటాక్స్ ట్రీని పొందుతాము.


ఇప్పుడు మనం ఈ చెట్టును వ్యతిరేక దిశలో పరిగెత్తవచ్చు మరియు మనకు కావలసిన ఇండెంట్‌లు, కలరింగ్ మరియు ఫార్మాటింగ్‌తో అభ్యర్థనను సమీకరించవచ్చు. లేదు, ఇది అనుకూలీకరించదగినది కాదు, కానీ ఇది సౌకర్యవంతంగా ఉంటుందని మాకు అనిపించింది.

మ్యాపింగ్ ప్రశ్న మరియు ప్లాన్ నోడ్‌లు

ఇప్పుడు మనం మొదటి దశలో విశ్లేషించిన ప్రణాళికను మరియు రెండవ దశలో విశ్లేషించిన ప్రశ్నను ఎలా కలపవచ్చో చూద్దాం.

ఒక సాధారణ ఉదాహరణ తీసుకుందాం - CTEని రూపొందించి దాని నుండి రెండుసార్లు చదివే ఒక ప్రశ్న ఉంది. అతను అలాంటి ప్రణాళికను రూపొందిస్తాడు.

CTE

మీరు దానిని జాగ్రత్తగా పరిశీలిస్తే, వెర్షన్ 12 వరకు (లేదా కీవర్డ్‌తో ప్రారంభించండి MATERIALIZED) ఏర్పాటు ప్లానర్‌కు CTE ఒక సంపూర్ణ అవరోధం.


అంటే రిక్వెస్ట్‌లో ఎక్కడో ఒక చోట CTE జనరేషన్ మరియు ప్లాన్‌లో ఎక్కడో ఒక నోడ్ కనిపిస్తే CTE, అప్పుడు ఈ నోడ్స్ ఖచ్చితంగా ఒకదానితో ఒకటి "పోరాడుతాయి", మేము వెంటనే వాటిని కలపవచ్చు.

నక్షత్రం గుర్తుతో సమస్య: CTEలు గూడు కట్టుకోవచ్చు.

చాలా పేలవంగా గూడు కట్టినవి ఉన్నాయి, మరియు అదే పేరుతో కూడా ఉన్నాయి. ఉదాహరణకు, మీరు లోపల చేయవచ్చు CTE A తయారు CTE X, మరియు లోపల అదే స్థాయిలో CTE B మళ్ళి చేయండి CTE X:

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

పోల్చినప్పుడు, మీరు దీన్ని అర్థం చేసుకోవాలి. “మీ కళ్ళతో” దీన్ని అర్థం చేసుకోవడం - ప్రణాళికను చూడటం, అభ్యర్థన యొక్క శరీరాన్ని కూడా చూడటం - చాలా కష్టం. మీ CTE తరం సంక్లిష్టంగా, సమూహంగా ఉంటే మరియు అభ్యర్థనలు పెద్దగా ఉంటే, అది పూర్తిగా అపస్మారక స్థితిలో ఉంటుంది.

UNION

ప్రశ్నలో మనకు కీవర్డ్ ఉంటే 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'ఓం టేబుల్-సెక్షన్ నుండి: Seq Scan, Bitmap Heap Scan లేదా Index Scan. కానీ, ఏ సందర్భంలోనైనా, ఈ “పిల్లలు” సంక్లిష్ట ప్రశ్నలు కావు - ఈ నోడ్‌లను ఈ విధంగా వేరు చేయవచ్చు Append వద్ద UNION.


మేము అలాంటి నాట్లను కూడా అర్థం చేసుకుంటాము, వాటిని "ఒక కుప్పలో" సేకరించి ఇలా చెప్పండి: "మీరు megable నుండి చదివినవన్నీ ఇక్కడ మరియు చెట్టు క్రింద ఉన్నాయి".

నోడ్‌లను స్వీకరించే "సాధారణ" డేటా

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లకు “మ్యాప్”.

కానీ సమూహ ప్రశ్నలతో ప్రతిదీ మరింత క్లిష్టంగా ఉంటుంది - దురదృష్టవశాత్తు, అవి ఎల్లప్పుడూ మారవు 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.

కానీ, చాలా సందర్భాలలో, దాదాపు అన్ని నోడ్‌లను “విప్పివేయవచ్చు” మరియు మీరు ఈ రకమైన ప్రొఫైలింగ్‌ను సకాలంలో ఎడమ వైపున పొందవచ్చు - అక్షరాలా, మీరు జావాస్క్రిప్ట్ కోడ్‌ను విశ్లేషించినప్పుడు Google Chrome లాగా. ప్రతి పంక్తి మరియు ప్రతి స్టేట్‌మెంట్ “ఎగ్జిక్యూట్” చేయడానికి ఎంత సమయం పట్టిందో మీరు చూడవచ్చు.


మరియు మీరు వీటన్నింటిని ఉపయోగించడానికి మరింత సౌకర్యవంతంగా చేయడానికి, మేము నిల్వ చేసాము ఆర్కైవ్, ఇక్కడ మీరు అనుబంధిత అభ్యర్థనలతో పాటు మీ ప్లాన్‌లను సేవ్ చేయవచ్చు మరియు తర్వాత కనుగొనవచ్చు లేదా లింక్‌ను ఎవరితోనైనా భాగస్వామ్యం చేయవచ్చు.

మీరు చదవలేని ప్రశ్నను తగిన రూపంలోకి తీసుకురావాలంటే, ఉపయోగించండి మా "నార్మలైజర్".

మూలం: www.habr.com