PostgreSQL Antipatterna: narratio de elegantia iterativae investigationis nominatim vel "Optimizationis ultro citroque"

Milia procuratorum ex officiis venditionibus per patriam recordum nostra ratio CRM decem milia contactus cotidie - facta communicationis cum potential vel existentium clients. Et ad hoc debes prius clientem invenire, et potius celerrime. Idque frequentius nominatim evenit.

Non est igitur mirum si interrogationes "gravissimas" iterum examinare in una e amplissimis databases - nostra. VLSI corporatum propter, inveni "in summo" petitio "velox" per nomen quaerere ad ordinationem pecto.

Praeterea, investigatio ulterior exemplum interesting retexit Primum ipsum et perficientur degradation rogationem sequentem cum sua subtilitate a compluribus iugis, quorum unumquodque unice cum optima intentione egit.

0: quid usor vis?

[KDPV hic]

Quid usor consuevit significare cum de nomine "velox" quaerere? Fere numquam evadit ut "honestum" quaerere substringi simile ... LIKE '%роза%' - quia tunc effectus includit non solum 'Розалия' и 'Магазин Роза'Autem 'Гроза' atque adeo 'Дом Деда Мороза'.

Usor sumit in gradu communi quem ei praebebis quaerere a principio verbi in titulo et hoc magis pertinet quod incipit cum intraverunt. Et hoc facies paene statim — pro input interlineari.

I: circumscribere negotium

Et multo magis homo non specialiter 'роз магаз'ita ut singula verba per praepositionem habeas. Imo multo facilius est pro usori respondere ad verbum velox innuere pro ultimo quam consulto "underspecificare" priores - vide quomodo machinationem quaerendi hoc tractat.

plerumque, recte postulationem proponere ad problema plus quam dimidium solutionis. Interdum diligenter uti causa analysis potest signanter influere effectus.

Quid faciam elit abstractum?

1.0: externum quaero engine

O difficile, quaerere quid omnino facere nolo - eam devops demus! Quaesitum machinam externam datorum explicant: Sphinx, ElasticSearch,...

Optio operativa, licet laboriosa, secundum synchronizationem et velocitatem mutationum. Sed non in nostro casu, cum inquisitio exerceatur singulis clientibus solum intra ambitum rationis suae datae. Et notitia satis alte variabilitas habet - et si procurator iam chartam intravit 'Магазин Роза'deinde post 5-10 secundas iam meminisse se oblitus es indicare se inscriptionem suam indicare et eam invenire et corrigere velle.

Ergo - let's quaerere "recta in database". Fortunate, PostgreSQL nos hoc facere permittit, et non una optio - nos eas aspiciemus.

1.1: "honestum" substring

Inhaeremus verbo "substring". Sed ad indicem inquisitionis per substringendum (et etiam per expressiones regulares!) optimum est modulus pg_trgm! Tantum ergo opus erit ut recte exponam.

Conemur ad sequentia laminam simpliciorem reddere exemplar:

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

7.8 decies centena millia monumentorum realium consociationum ibi inserimus et eas indicemus:

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

Quaeramus primos 10 libros pro inquisitione interlineari:

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

[Aspice explain.tensor.ru]

Bene, id... 26ms, 31MB notitias legere et plusquam 1.7K monumenta percolta - pro 10 quaesitis. Altiora sunt gratuita supra caput, annon aliquid efficacius?

1.2: per textum quaerere? Est FTS!

Immo PostgreSQL validissimum praebet plenum illud quaero engine (Quaestio plena text) possidet facultatem quaerendi praepositionem. Optionem magnam, etiam extensiones instituere non debes! Experiamur:

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;

[Aspice explain.tensor.ru]

Hic parallelisatio inquisitionis executionis nobis aliquantulum adiuvit secans tempus ad dimidium 11ms. Et legere debebamus 1.5 temporibus minus in toto 20MB. Sed hic, eo minus melior, quia quo maius volumen legitur, eo superior casus latibulum missurus est, et quaelibet pagina extra notitiae orbis lecta est pro petitione potentiae "sclopeta".

1.3: usque AMO?

Antecedens postulatio bona est omnibus, sed si centies millies in die eam extraxeris, veniet 2TB lege data. Optime, ex memoria, sed si infelicis es, ex disc. Minorem facere conemur ergo.

Memento quod user vult videre primum quod incipiunt a.... Hoc igitur est in purissima sua forma praeposita quaerere propter text_pattern_ops! Et modo si "non satis habemus" usque ad 10 tabulas quas quaerimus, tunc debebimus legere illis utentes FTS quaerere:

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

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

[Aspice explain.tensor.ru]

Optimum perficientur - total 0.05ms et paulo plus quam 100KB legere! Tantum obliti sumus digerere nomenut usor in eventibus non deerret:

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

[Aspice explain.tensor.ru]

Oh aliquid tam pulchrum amplius non est - ut videtur index est, sed praetervolat illud genus... Hoc quidem iam multoties plus valet quam optio prior, sed...

1.4: "cum lima consummare"

Sed index est qui permittit te ut per amplitudinem quaerere et adhuc voluptua normaliter utere - regularis btree!

CREATE INDEX ON firms(lower(name));

Tantum rogatio pro "manualiter colligenda" erit:

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

[Aspice explain.tensor.ru]

Praeclara - sorting opera et subsidia consummatio "microscopica" manet; millies efficacius quam "purus" FTS! Reliquum est ut in unam petitionem redigas;

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

Nota quod secunda quaestio fit nisi prius rediit quam expectata tandem LIMIT versuum numerus. De hac methodo optimization interrogationis loquor iam ante scripsit.

Ita etiam nunc in mensa et gemmam habemus et tenemus, sed peraeque evenit minus quam X% of petitiones ad supplicium secundi obstructionum. Hoc est, talibus limitibus praecognitis ad opus proprium praecognitis, totam consummationem facultatum servientium per mille fere tempora reducere potuimus!

1.5 *: non possumus facere sine lima

altiorem LIKE Prohibiti sumus uti falsa Sorting. Sed potest "constitui in semitam rectam", denotans utentis operantis;

Per default assumitur ASC. Accedit, nomen specificare generis operantis in clausula USING. Qualis operator debet esse membrum minus quam vel maius quam alicuius familiae operariorum B-arboris. ASC plerumque equivalent USING < и DESC plerumque equivalent USING >.

In nobis, "minus" est ~<~:

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

[Aspice explain.tensor.ru]

II: quomodo petitiones acescunt?

Nunc petitionem nostram relinquimus sex menses vel annum, et miramur iterum invenire "in summitate" cum indicibus totius diei "flare" memoriae.buffers participatur hit) apud 5.5TB — hoc est, plus etiam quam fuit.

Negotium nostrum increvit et quod inposuit, crevit, sed non tantundem! Hoc significat aliquid piscosum hic - sit ex figura.

2.1: ortus pag

In aliquo puncto, alius dolor evolutionis facere voluit ut "salire" posset ex inquisitione celeri subscripta subcriptio cum eodem, sed eventus dilatatus. Quid subcriptio sine pagina navigationis? Let's screw it up!

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

Nunc actis inquisitionis subcriptio cum "page-by-paginam" monstrare licebat sine ullis evitationibus onerantium elaborandi.

Quippe, hercule, pro unaquaque pagina sequentium notitiarum magis ac magis legitur hoc est, hoc est manifesta antipatterna. Sed rectius esset investigationem incipere sequenti iteratione e clavis interfaciei repositae, sed de illo alio tempore.

2.2: Volo aliquid exoticis

Aliquam in elit velit inde cum specimen notitia diversificari ex alia tabula, qua tota prior petitio ad CTE;

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

Et etiam sic non est malum, cum subqueratio tantum aestimatur pro 10 tabulis redditis, nisi ...

2.3: DISTINCTUS est insipiens et sine misericordia

Alicubi in processu talis evolutionis ex subquery 2 perdidisti NOT LIKE conditione,. Patet quod post hoc UNION ALL coepi reverti quidam entries bis — primo in principio linee, et deinde iterum — in principio primi verbi hujus versus. In fine, omnes monumenta secundae subqueriae monumentis primae aequare potuerunt.

Quid elit loco causam quaerit? Nulla quaestio!

• in duplo originale exempla
• applicare distinctumut nisi singulae instantiae cuiusque lineae

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>;

Hoc est, quod eventum, in fine, prorsus idem esse patet, sed casus "volare" in subquisitionem 2 CTE multo altior factus est, immo sine hoc; evidenter magis readable.

Sed id tristique neque. Cum elit interrogavit eligere DISTINCT non pro certis, sed pro omnibus simul agris tabulae, deinde campus sub_query - eventus subquery - eo ipso inclusus est. Nunc facere DISTINCT, database exsequi iam non 10 subquisitiones, sed omnes <2*N> + 10!

2.4: cooperatio super omnia!

Ita tincidunt vixerunt sine molesto, quod user clare satis patientiam non habuit ad "accommodare" subcriptio ad significantes N valores cum tarditate chronica in accipiendo singulas "paginas" sequentes.

Donec tincidunt ex alia parte ad eos venirent et tali convenienti methodo uti vellent ad iterative search - hoc est, frustum ex aliquo specimen accipimus, id additis conditionibus eliquamus, eventum trahunt, deinde pars altera (quod in casu nostro N augendo perficitur), et sic deinceps donec velum impleamus.

Fere in specimine captus N valores fere 17K . attigitet in tantum uno die ad minimum 4K petitionum "per catenam" exsecuta sunt. Eorum novissimi audacter inspecti sunt 1GB memoriae per iterationem...

in summa

Source: www.habr.com