પોસ્ટગ્રેએસક્યુએલ એન્ટિપેટર્ન: નામ દ્વારા શોધના પુનરાવર્તિત શુદ્ધિકરણની વાર્તા, અથવા "આગળ અને આગળ ઑપ્ટિમાઇઝ કરવું"

દેશભરની સેલ્સ ઓફિસના હજારો મેનેજરો રેકોર્ડ કરે છે અમારી CRM સિસ્ટમ દરરોજ હજારો સંપર્કો - સંભવિત અથવા હાલના ગ્રાહકો સાથે વાતચીતની હકીકતો. અને આ માટે, તમારે સૌપ્રથમ ક્લાયંટ શોધવા જ જોઈએ, અને પ્રાધાન્યમાં ખૂબ જ ઝડપથી. અને આ મોટે ભાગે નામ દ્વારા થાય છે.

તેથી, તે આશ્ચર્યજનક નથી કે, ફરી એકવાર સૌથી વધુ લોડ થયેલ ડેટાબેઝમાંના એક પર "ભારે" પ્રશ્નોનું વિશ્લેષણ કરવું - આપણા પોતાના VLSI કોર્પોરેટ એકાઉન્ટ, મને "ટોચમાં" મળ્યું નામ દ્વારા "ઝડપી" શોધ માટે વિનંતી સંસ્થા કાર્ડ માટે.

તદુપરાંત, વધુ તપાસમાં એક રસપ્રદ ઉદાહરણ બહાર આવ્યું પ્રથમ ઓપ્ટિમાઇઝેશન અને પછી કામગીરીમાં ઘટાડો ઘણી ટીમો દ્વારા તેના અનુક્રમિક સંસ્કારિતા સાથે વિનંતી, જેમાંથી દરેક માત્ર શ્રેષ્ઠ હેતુઓ સાથે કાર્ય કરે છે.

0: વપરાશકર્તાને શું જોઈએ છે?

[KDPV અહીંથી]

જ્યારે વપરાશકર્તા નામ દ્વારા "ઝડપી" શોધ વિશે વાત કરે છે ત્યારે સામાન્ય રીતે તેનો અર્થ શું થાય છે? તે લગભગ ક્યારેય સબસ્ટ્રિંગ જેવી "પ્રમાણિક" શોધ બની શકતી નથી ... LIKE '%роза%' - કારણ કે પછી પરિણામમાં માત્ર શામેલ નથી 'Розалия' и 'Магазин Роза'પરંતુ 'Гроза' અને તે પણ 'Дом Деда Мороза'.

વપરાશકર્તા રોજિંદા સ્તરે ધારે છે કે તમે તેને પ્રદાન કરશો શબ્દની શરૂઆત દ્વારા શોધો શીર્ષકમાં અને તેને વધુ સુસંગત બનાવો પર શરૂ થાય છે દાખલ કર્યું. અને તમે તે કરશો લગભગ તરત જ - ઇન્ટરલાઇનર ઇનપુટ માટે.

1: કાર્ય મર્યાદિત કરો

અને તેથી પણ વધુ, એક વ્યક્તિ ખાસ દાખલ કરશે નહીં 'роз магаз', જેથી તમારે દરેક શબ્દને ઉપસર્ગ દ્વારા શોધવાનો રહેશે. ના, વપરાશકર્તા માટે પહેલાના શબ્દોને હેતુપૂર્વક "અન્ડરસ્પેસિફાય" કરવા કરતાં છેલ્લા શબ્દ માટે ઝડપી સંકેતનો પ્રતિસાદ આપવાનું ખૂબ સરળ છે - કોઈપણ શોધ એન્જિન આને કેવી રીતે હેન્ડલ કરે છે તે જુઓ.

સામાન્ય રીતે અધિકાર સમસ્યા માટે જરૂરિયાતો ઘડવી એ અડધા કરતાં વધુ ઉકેલ છે. ક્યારેક સાવચેત ઉપયોગ કેસ વિશ્લેષણ પરિણામને નોંધપાત્ર રીતે પ્રભાવિત કરી શકે છે.

અમૂર્ત વિકાસકર્તા શું કરે છે?

1.0: બાહ્ય શોધ એંજીન

ઓહ, શોધ મુશ્કેલ છે, હું કંઈપણ કરવા માંગતો નથી - ચાલો તેને ડેવોપ્સને આપીએ! તેમને ડેટાબેઝમાં બહારનું સર્ચ એન્જિન જમાવવા દો: સ્ફિન્ક્સ, ઇલાસ્ટિક સર્ચ,...

સિંક્રનાઇઝેશન અને ફેરફારોની ઝડપના સંદર્ભમાં શ્રમ-સઘન હોવા છતાં કામ કરવાનો વિકલ્પ. પરંતુ અમારા કિસ્સામાં નહીં, કારણ કે શોધ દરેક ક્લાયંટ માટે તેના એકાઉન્ટ ડેટાના માળખામાં જ હાથ ધરવામાં આવે છે. અને ડેટામાં એકદમ ઉચ્ચ પરિવર્તનક્ષમતા છે - અને જો મેનેજર હવે કાર્ડમાં દાખલ થયા છે 'Магазин Роза', પછી 5-10 સેકન્ડ પછી તેને પહેલેથી જ યાદ હશે કે તે ત્યાં તેનું ઈમેલ સૂચવવાનું ભૂલી ગયો છે અને તેને શોધીને તેને સુધારવા માંગે છે.

તેથી - ચાલો "ડાટાબેઝમાં સીધું" શોધો. સદનસીબે, PostgreSQL અમને આ કરવાની મંજૂરી આપે છે, અને માત્ર એક વિકલ્પ જ નહીં - અમે તેમને જોઈશું.

1.1: "પ્રામાણિક" સબસ્ટ્રિંગ

અમે "સબસ્ટ્રિંગ" શબ્દને વળગી રહીએ છીએ. પરંતુ સબસ્ટ્રિંગ (અને નિયમિત અભિવ્યક્તિ દ્વારા પણ!) દ્વારા અનુક્રમણિકા શોધ માટે એક ઉત્તમ છે મોડ્યુલ pg_trgm! માત્ર પછી તે યોગ્ય રીતે સૉર્ટ કરવા માટે જરૂરી રહેશે.

ચાલો મોડેલને સરળ બનાવવા માટે નીચેની પ્લેટ લેવાનો પ્રયાસ કરીએ:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

અમે ત્યાં વાસ્તવિક સંસ્થાઓના 7.8 મિલિયન રેકોર્ડ અપલોડ કરીએ છીએ અને તેમને અનુક્રમિત કરીએ છીએ:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

ચાલો આંતરરેખીય શોધ માટે પ્રથમ 10 રેકોર્ડ્સ જોઈએ:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

[explan.tensor.ru પર જુઓ]

સારું, તે... 26ms, 31MB ડેટા વાંચો અને 1.7K કરતાં વધુ ફિલ્ટર કરેલા રેકોર્ડ્સ - 10 શોધેલા લોકો માટે. ઓવરહેડ ખર્ચ ખૂબ વધારે છે, શું ત્યાં કંઈક વધુ કાર્યક્ષમ નથી?

1.2: ટેક્સ્ટ દ્વારા શોધો? તે FTS છે!

ખરેખર, PostgreSQL ખૂબ જ શક્તિશાળી પ્રદાન કરે છે સંપૂર્ણ ટેક્સ્ટ સર્ચ એન્જિન (સંપૂર્ણ લખાણ શોધ), ઉપસર્ગ શોધની ક્ષમતા સહિત. એક ઉત્તમ વિકલ્પ, તમારે એક્સ્ટેંશન ઇન્સ્ટોલ કરવાની પણ જરૂર નથી! ચાલો પ્રયત્ન કરીએ:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

[explan.tensor.ru પર જુઓ]

અહીં ક્વેરી એક્ઝેક્યુશનની સમાંતરતાએ અમને થોડી મદદ કરી, સમયને અડધો કરી દીધો 11ms. અને અમારે 1.5 ગણું ઓછું વાંચવું પડ્યું - કુલ 20MB. પરંતુ અહીં, ઓછું, વધુ સારું, કારણ કે આપણે વાંચીએ છીએ તેટલું મોટું વોલ્યુમ, કેશ ચૂકી જવાની શક્યતાઓ વધારે છે, અને ડિસ્કમાંથી વાંચેલા ડેટાના દરેક વધારાના પૃષ્ઠ વિનંતી માટે સંભવિત "બ્રેક" છે.

1.3: હજુ પણ ગમે છે?

અગાઉની વિનંતી દરેક માટે સારી છે, પરંતુ તમે તેને દિવસમાં એક લાખ વખત ખેંચો તો જ તે આવશે 2TB ડેટા વાંચો. શ્રેષ્ઠ કિસ્સામાં, મેમરીમાંથી, પરંતુ જો તમે કમનસીબ છો, તો પછી ડિસ્કમાંથી. તો ચાલો તેને નાનો કરવાનો પ્રયાસ કરીએ.

ચાલો યાદ કરીએ કે વપરાશકર્તા શું જોવા માંગે છે પ્રથમ "જેની સાથે શરૂ થાય છે...". તેથી આ તેના શુદ્ધ સ્વરૂપમાં છે ઉપસર્ગ શોધ ની મદદ સાથે text_pattern_ops! અને માત્ર જો અમારી પાસે 10 જેટલા રેકોર્ડ્સ "પૂરતા ન હોય" જે અમે શોધી રહ્યા છીએ, તો અમારે FTS શોધનો ઉપયોગ કરીને તેમને વાંચવાનું સમાપ્ત કરવું પડશે:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

[explan.tensor.ru પર જુઓ]

ઉત્તમ પ્રદર્શન - કુલ 0.05ms અને 100KB કરતાં થોડું વધારે વાંચવું! ફક્ત આપણે જ ભૂલી ગયા નામ દ્વારા સૉર્ટ કરોજેથી વપરાશકર્તા પરિણામોમાં ખોવાઈ ન જાય:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[explan.tensor.ru પર જુઓ]

ઓહ, હવે કંઈક એટલું સુંદર નથી - એવું લાગે છે કે ત્યાં કોઈ અનુક્રમણિકા છે, પરંતુ સૉર્ટિંગ તેમાંથી પસાર થઈ જાય છે... અલબત્ત, તે પહેલાના વિકલ્પ કરતાં ઘણી વખત વધુ અસરકારક છે, પરંતુ...

1.4: "ફાઇલ સાથે સમાપ્ત કરો"

પરંતુ ત્યાં એક અનુક્રમણિકા છે જે તમને શ્રેણી દ્વારા શોધવા માટે પરવાનગી આપે છે અને હજુ પણ સામાન્ય રીતે સૉર્ટિંગનો ઉપયોગ કરે છે - નિયમિત btree!

CREATE INDEX ON firms(lower(name));

ફક્ત તેના માટેની વિનંતી "મેન્યુઅલી એકત્રિત" કરવાની રહેશે:

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

[explan.tensor.ru પર જુઓ]

ઉત્તમ - સૉર્ટિંગ કામ કરે છે, અને સંસાધનનો વપરાશ "માઇક્રોસ્કોપિક" રહે છે, "શુદ્ધ" FTS કરતાં હજારો ગણી વધુ અસરકારક! જે બાકી છે તે તેને એક જ વિનંતીમાં એકસાથે મૂકવાનું છે:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

નોંધ કરો કે બીજી સબક્વેરી ચલાવવામાં આવે છે માત્ર જો પ્રથમ અપેક્ષિત કરતાં ઓછું પરત કરે જો છેલ્લા LIMIT રેખાઓની સંખ્યા. હું ક્વેરી ઓપ્ટિમાઇઝેશનની આ પદ્ધતિ વિશે વાત કરી રહ્યો છું પહેલાથી જ લખ્યું હતું.

તો હા, હવે આપણી પાસે ટેબલ પર બીટ્રી અને જિન બંને છે, પરંતુ આંકડાકીય રીતે તે તારણ આપે છે કે 10% થી ઓછી વિનંતીઓ બીજા બ્લોકના અમલ સુધી પહોંચે છે. એટલે કે, કાર્ય માટે અગાઉથી જાણીતી આવી લાક્ષણિક મર્યાદાઓ સાથે, અમે સર્વર સંસાધનોના કુલ વપરાશને લગભગ હજાર ગણો ઘટાડી શક્યા છીએ!

1.5*: આપણે ફાઇલ વિના કરી શકીએ છીએ

ઉપર LIKE અમને ખોટા સૉર્ટિંગનો ઉપયોગ કરવાથી અટકાવવામાં આવ્યા હતા. પરંતુ ઓપરેટરનો ઉપયોગ કરીને તેને "સાચા માર્ગ પર સેટ" કરી શકાય છે:

મૂળભૂત રીતે તે ધારવામાં આવે છે ASC. વધુમાં, તમે કલમમાં ચોક્કસ સૉર્ટ ઑપરેટરનું નામ સ્પષ્ટ કરી શકો છો USING. સૉર્ટ ઑપરેટર બી-ટ્રી ઑપરેટર્સના કેટલાક પરિવારના કરતાં ઓછા અથવા વધુના સભ્ય હોવા જોઈએ. ASC સામાન્ય રીતે સમકક્ષ USING < и DESC સામાન્ય રીતે સમકક્ષ USING >.

અમારા કિસ્સામાં, "ઓછું" છે ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

[explan.tensor.ru પર જુઓ]

2: વિનંતીઓ કેવી રીતે ખાટી થઈ જાય છે

હવે અમે અમારી વિનંતીને છ મહિના અથવા એક વર્ષ માટે "ઉકળવા" માટે છોડી દઈએ છીએ, અને મેમરીના કુલ દૈનિક "પમ્પિંગ" ના સૂચકાંકો સાથે તેને ફરીથી "ટોચ પર" શોધીને અમને આશ્ચર્ય થાય છે (બફર્સ હિટ શેર કરે છે) માં 5.5TB - એટલે કે, તે મૂળ કરતાં પણ વધુ.

ના, અલબત્ત, અમારો ધંધો વધ્યો છે અને અમારો વર્કલોડ વધ્યો છે, પણ તેટલી જ રકમથી નહીં! આનો અર્થ એ છે કે અહીં કંઈક ગૂંચવણભર્યું છે - ચાલો તેને શોધી કાઢીએ.

2.1: પેજિંગનો જન્મ

અમુક સમયે, બીજી ડેવલપમેન્ટ ટીમ ઝડપી સબસ્ક્રિપ્ટ શોધમાંથી રજિસ્ટ્રીમાં સમાન, પરંતુ વિસ્તૃત પરિણામો સાથે "જમ્પ" કરવાનું શક્ય બનાવવા માંગતી હતી. પેજ નેવિગેશન વિના રજિસ્ટ્રી શું છે? ચાલો તેને સ્ક્રૂ કરીએ!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

હવે વિકાસકર્તા માટે કોઈપણ તણાવ વિના "પૃષ્ઠ-દર-પૃષ્ઠ" લોડિંગ સાથે શોધ પરિણામોની રજિસ્ટ્રી બતાવવાનું શક્ય હતું.

અલબત્ત, હકીકતમાં, ડેટાના દરેક અનુગામી પૃષ્ઠ માટે વધુને વધુ વાંચવામાં આવે છે (બધું અગાઉના સમયથી, જેને આપણે કાઢી નાખીશું, ઉપરાંત જરૂરી "પૂંછડી") - એટલે કે, આ એક સ્પષ્ટ એન્ટિપેટર્ન છે. પરંતુ ઇન્ટરફેસમાં સંગ્રહિત કીમાંથી આગલા પુનરાવર્તન પર શોધ શરૂ કરવી વધુ યોગ્ય રહેશે, પરંતુ તે વિશે બીજી વાર.

2.2: મારે કંઈક વિચિત્ર જોઈએ છે

અમુક સમયે વિકાસકર્તા ઇચ્છતો હતો ડેટા સાથે પરિણામી નમૂનાને વિવિધતા આપો અન્ય કોષ્ટકમાંથી, જેના માટે સમગ્ર અગાઉની વિનંતી CTE ને મોકલવામાં આવી હતી:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

અને તેમ છતાં, તે ખરાબ નથી, કારણ કે સબક્વેરીનું મૂલ્યાંકન ફક્ત 10 પરત કરેલા રેકોર્ડ્સ માટે કરવામાં આવે છે, જો નહીં ...

2.3: DISTINCT સંવેદનાહીન અને નિર્દય છે

2જી સબક્વેરીમાંથી આવી ઉત્ક્રાંતિની પ્રક્રિયામાં ક્યાંક ખોવાઈ ગયો NOT LIKE શરત. તે સ્પષ્ટ છે કે આ પછી UNION ALL પાછા ફરવાનું શરૂ કર્યું કેટલીક એન્ટ્રીઓ બે વાર - પ્રથમ લીટીની શરૂઆતમાં જોવા મળે છે, અને પછી ફરીથી - આ લીટીના પ્રથમ શબ્દની શરૂઆતમાં. મર્યાદામાં, 2જી સબક્વેરીનાં તમામ રેકોર્ડ્સ પહેલાનાં રેકોર્ડ્સ સાથે મેચ કરી શકે છે.

કારણ શોધવાને બદલે વિકાસકર્તા શું કરે છે?.. કોઈ પ્રશ્ન નથી!

• કદ બમણું મૂળ નમૂનાઓ
• DISTINCT લાગુ કરોદરેક લાઇનના માત્ર એક જ દાખલા મેળવવા માટે

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

એટલે કે, તે સ્પષ્ટ છે કે પરિણામ, અંતે, બરાબર એ જ છે, પરંતુ 2જી CTE સબક્વેરી માં "ઉડવાની" તક ઘણી વધી ગઈ છે, અને આ વિના પણ, સ્પષ્ટપણે વધુ વાંચવા યોગ્ય.

પરંતુ આ સૌથી દુઃખદ બાબત નથી. વિકાસકર્તાએ પસંદ કરવાનું કહ્યું ત્યારથી DISTINCT ચોક્કસ લોકો માટે નહીં, પરંતુ એક સાથે તમામ ક્ષેત્રો માટે રેકોર્ડ્સ, પછી સબ_ક્વેરી ફીલ્ડ — સબક્વેરીનું પરિણામ — ત્યાં આપમેળે સમાવવામાં આવ્યું હતું. હવે, ચલાવવા માટે DISTINCT, ડેટાબેઝ પહેલાથી જ એક્ઝિક્યુટ કરવાનું હતું 10 સબક્વેરીઝ નહીં, પરંતુ તમામ <2 * N> + 10!

2.4: બધા ઉપર સહકાર!

તેથી, વિકાસકર્તાઓ જીવતા હતા - તેઓ પરેશાન કરતા નહોતા, કારણ કે વપરાશકર્તા પાસે સ્પષ્ટપણે દરેક અનુગામી "પૃષ્ઠ" પ્રાપ્ત કરવામાં તીવ્ર મંદી સાથે નોંધપાત્ર એન મૂલ્યો સાથે રજિસ્ટ્રીને "વ્યવસ્થિત" કરવા માટે પૂરતી ધીરજ નહોતી.

જ્યાં સુધી અન્ય વિભાગના વિકાસકર્તાઓ તેમની પાસે આવ્યા અને આવી અનુકૂળ પદ્ધતિનો ઉપયોગ કરવા માંગતા ન હતા પુનરાવર્તિત શોધ માટે - એટલે કે, અમે કેટલાક નમૂનામાંથી એક ટુકડો લઈએ છીએ, તેને વધારાની શરતો દ્વારા ફિલ્ટર કરીએ છીએ, પરિણામ દોરીએ છીએ, પછી આગળનો ભાગ (જે અમારા કિસ્સામાં N વધારીને પ્રાપ્ત થાય છે), અને તેથી જ્યાં સુધી આપણે સ્ક્રીન ભરીએ નહીં.

સામાન્ય રીતે, પકડાયેલા નમૂનામાં N લગભગ 17K ના મૂલ્યો પર પહોંચી ગયા, અને માત્ર એક જ દિવસમાં ઓછામાં ઓછી 4K આવી વિનંતીઓ "સાંકળ સાથે" ચલાવવામાં આવી હતી. તેમાંથી છેલ્લાને હિંમતભેર સ્કેન કરવામાં આવ્યા હતા પુનરાવર્તન દીઠ 1GB મેમરી...

કુલ

સોર્સ: www.habr.com