🥇Fabula inquisitionis unius SQL

Ultimo Decembri accepi an interesting cimex fama ex VWO subsidii quadrigis. Tempus onerationis unius analyticorum relationum pro cliente corporato magno prohibitiva videbatur. Quae cum responsabilitas mea haec sit, statim solvendo quaestionem notavimus.

erectus

Ut pateat quid dicam, pauca de VWO dicam. Hoc suggestum est cum quo varias iaculis in tuis websites expeditiones mittere potes: experimenta conduce A/B, visitatores et conversiones indagare, infundibulum venditionesque resolvere, tabulas calefacere et tabulas invisere recordationes ludere.

Sed in diam sit amet magna. Omnes praedictae functiones inter se coniunguntur. Et pro clientibus corporatis, ingens moles informationum simpliciter inutilis esset sine valido suggestu quod in analytica forma exhibet.

Rostra utens, temere interrogationem facere potes in data copia magna. En simplex exemplum:

Ostende omnia clicks in pagina "abc.com" FROM <date d1> TO <date d2> hominibus, qui Chrome OR usi sunt (in Europa sitae AC iPhone utuntur)

Operatores Boolean attende. Clientibus praesto sunt in interrogatione interfaciendi ut ad libitum implicatas interrogationes ad exemplaria obtinenda exhibeant.

Patiens petitionem

Cliens in quaestione aliquid facere conatur quod celeriter intuitive laborare debet;

Ostende omnia sessionis monumenta pro usoribus qui aliquam paginam in URL continentem "/jobs" visitaverunt

Hic situs ton negotiationis habuit et plus quam decies centena millia singularia URLs pro eo accommodabamus. Et quaerebant satis simplicem URL Formulam quae ad exemplar negotii sui pertinentia.

Investigatio praevia

Inspice quid agatur in database. Infra quaestionem originalem tardum SQL est:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

Et hic sunt timationes;

Medium tempus: 1.480 ms Execution time: 1431924.650 ms

Quaestio repit 150 milium ordinum. Quaesitio consiliumque duos singularum rerum iucundarum ostendit, sed nullas ampullas manifestas.

Petentibus ulterius studeamus. Ut videre potes, facit JOIN tres tabulae;

sessions: ad informationem sessionis ostentandam: navigatrum, procuratorem usoris, patriam et cetera.
recording_data: delata, paginae, tempus visitationis
URLs: Ad delata praegrandes duplicationes vitandas, eas in mensa separata condimus.

Item nota quod omnes tabulae nostrae iam partitae sunt account_id. Excluditur ergo locus in quo una praecipue magna ratio problemata aliis causat, excluditur.

Vultus pro clues

Quo diligentius inspiciamus, perspicimus aliquid cum peculiari petitione falsum esse. Propius inspice hanc lineam: pretium est accipere:

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

Fortassis quia prima cogitatio fuit ILIKE de omnibus his longis URLs (habemus supra 1,4 decies centena millia unique URLs ob hanc rationem collecti) observantia pati potest.

Sed nulla, id non est!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

Formula inquisitionis petitio tantum 5 secundis accipit. Exquirens exemplar inter decies centena millia singularium URLs plane non est quaestio.

Proximus suspicio in indice varia est JOIN. Forsitan accensus eorum tarditatem effecit? plerumque JOINSunt candidati manifestissimi ad quaestiones perficiendas, sed nostram causam typicam non credidi.

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

Etiam nec ipsum erat. JOINsatis celeriter evasit s.

Angustus circulus suspectorum

Paratus eram incipere interrogationem mutare ad emendamenta quaevis perficienda. Turma mea et elaboravi 2 principales notiones:

Usus est pro subquery URL: Reprehendere voluimus iterum si quae essent problemata cum inscriptione pro URLs. Uno modo ad hoc assequendum simpliciter utendum est EXISTS. EXISTS potes multum melioris effectus cum statim desinit simul ac unicum chordum condicionem invenit.

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

Bene, est. Subquery involutus EXISTSomnia facit super- nium. Sequens quaestio logica est cur petitio con JOIN-ami et stratio ipsa velocia singula, sed valde tardum simul?

Movens subqueriam ad CTE : Si quaestio sua sponte velox est, simpliciter primum eventum festinanter computare possumus et deinde illud principale interrogationi praebere.

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

sed adhuc tardissimum erat.

Inveniens reus

Toto hoc tempore, una res parva ante oculos emicabat, quam ego perpetuo dimovit. Sed quia nihil aliud restat, hanc quoque intueri decrevi. Ego de && operator. vale EXISTS sicut melius perficientur && solus elementum commune reliquum erat per omnes versiones interrogationis tardae.

Inspicens LitterarumVidemus quod && usus est cum debes invenire communia elementa inter duas vestit.

In originali petitione haec est:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

Quod significat formam inquisitionis de nostris URLs facimus, deinde intersectionem cum omnibus URLs cum communibus cursoribus invenimus. Hoc paulum est quod "delata" hic non refert ad tabulam continens omnia delata, sed "delata" columnae in tabula. recording_data.

Cum suspiciones de crescente &&, Conatus sum invenire confirmationem pro eis in consilio interrogationis generatae EXPLAIN ANALYZE (Iam consilium servavi, sed experimentum in SQL plerumque commodius sum quam opacitatem interrogationis consiliumque cognoscere conaris).

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

Filtra lineae variae erant solum ex &&. Unde significabat hanc operationem non solum pretiosam esse, sed etiam pluries facere.

Hanc condicionem segregando temptavi

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

Haec quaestio tarda fuit. Quod JOIN-s sunt velocia et subqueria celeria, nulla res remanet && operator.

Haec operatio est clavis. Semper necesse est totam tabulam URLs quaeramus ut exemplar quaeramus, semperque intersectiones invenire opus est. Non possumus quaerere per actis domicilii directe, quia haec de modo IDs referuntur urls.

In via ad solutionem

&& tardum est, quia utrumque ingens es. Operatio relative velox erit si reponere urls on { "http://google.com/", "http://wingify.com/" }.

Coepi quaerens viam facere intersectionem in Postgres sine usura &&sed parum feliciter.

Ad extremum, quaestionem modo separatim solvere decrevimus: omnia mihi da urls lineae pro quibus URL exemplaris aequet. Sine adiectis condicionibus erit -

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

pro JOIN syntaxin Modo usus est subquery et expandi recording_data.urls ordinata ut possis immediate applicare condicionem WHERE.

Maxime hic est && inhibere solebat num ingressum datum domicilium congruentem contineat. Si paululum squiris, hanc operationem videre potes per elementa ordinatae (vel ordines tabulae) et cessat cum occurrit conditio. Nihilne te admonere? Yeah, EXISTS.

Cum in recording_data.urls ex contextu extra ambitum referri potest, cum id inciderit in veterem amicum nostrum EXISTS et involvunt stratam cum eo.

Omnibus simul dispositis, interrogationem finalem optimized:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

et extremum tempus ducunt Time: 1898.717 ms Tempus celebrandi???

Non tam celeriter! Primo debes ad reprimendam rectitudinem. Maxime suspectum de me EXISTS ipsum sicut logicam mutat ad perficiendam antea. Certo nobis opus est nos non manifesto errori petitionem addidisse.

Innocens test erat currere count(*) de tam tarde et ieiuno queries pro magna multitudine diversarum notitiarum occidit. Deinde, pro parva copia notitiarum, manually verificavit omnes eventus recti esse.

Omnes probationes constanter positivum eventum dederunt. Statuimus omnia!

Lectiones didicit

Multae lectiones ex hac historia discendae sunt;

Quaero consilia non totam fabulam narrare, sed clues praebere possunt
Pelagus suspectos non semper realis reis
Tardus quaero rescindi potest segregare bottlenecks
Non omnes optimizationes deducuntur in natura
usum EXISTubi fieri potest, scenica incrementa in fructibus ducere potest

conclusio,

Ex interrogatione venimus tempus 24 minutarum ad 2 secundas - satis notabile incrementum! Etsi hic articulus magnus exivit, omnia experimenta uno die in nobis facta sunt, et aestimatum est quod inter 1,5 et 2 horas optimizationes et experimentum sumpserunt.

SQL Lingua mirabilis est si eam non times, sed ea discere et uti conare. Habendo bonam intelligentiam quomodo SQL queries efficiuntur, quomodo datorum interrogationes consilia generat, quomodo indices operantur, et simpliciter magnitudo notitiarum quas tu tractas, nimis prospere ad quaestiones optimizing potes. Pariter refert, diversos aditus experiri pergere ac quaestionem lente dissolvere, utres vinarios inveniendo.

Optima pars circa effectus assequendos huiusmodi est notabilis, visibili meliorationis celeritas — ubi fama quae antea ne onerare quidem nunc fere statim onerat.

Specialis gratias socii mei iubente Aditya Mishra, Aditya Gauru и Varun Malhotra nam et brainstorming Dinkar Pandir ut error magni momenti in nostra ultima petitione inveniatur antequam tandem vale dixerunt!

Source: www.habr.com

Historia inquisitionis unius SQL