D'Geschicht vun enger SQL Enquête

Am leschte Dezember krut ech en interessante Feelerbericht vum VWO Support Team. D'Laaschtzäit fir ee vun den Analyseberichter fir e groussen Entreprise Client schéngt verbueden ze sinn. A well dëst mäi Verantwortungsgebitt ass, hunn ech mech direkt op d'Léisung vum Problem konzentréiert.

Virgeschicht

Fir kloer ze maachen wat ech schwätzen, wäert ech Iech e bëssen iwwer VWO erzielen. Dëst ass eng Plattform mat där Dir verschidde geziilte Kampagnen op Äre Websäiten lancéiere kënnt: A/B Experimenter maachen, Besucher a Konversiounen verfollegen, de Verkafstriichter analyséieren, Hëtztkaarten affichéieren a Visiteopnamen spillen.

Awer déi wichtegst Saach iwwer d'Plattform ass Berichterstattung. All déi uewe genannte Funktiounen sinn matenee verbonnen. A fir Firmeclienten wier eng enorm Quantitéit un Informatioun einfach nëtzlos ouni eng mächteg Plattform déi se an analytesch Form presentéiert.

Mat der Plattform kënnt Dir eng zoufälleg Ufro op engem groussen Datesaz maachen. Hei ass en einfacht Beispill:

All Klicks op der Säit "abc.com" VUN <Datum d1> BIS <Datum d2> fir Leit weisen, déi Chrome benotzt hunn ODER (an Europa waren AN benotzt hunn) iPhone)

Opgepasst op Boolschen Bedreiwer. Si sinn verfügbar fir Clienten an der Ufro-Interface fir arbiträr komplex Ufroen ze maachen fir Proben ze kréien.

Lues Ufro

De Client a Fro huet probéiert eppes ze maachen wat intuitiv séier sollt funktionnéieren:

Weist all Sessiounsrecords fir Benotzer déi all Säit besicht hunn mat enger URL mat "/jobs"

Dëse Site hat eng Tonne Traffic a mir hunn iwwer eng Millioun eenzegaarteg URLen gelagert just dofir. A si wollten eng zimlech einfach URL-Schabloun fannen déi mat hirem Geschäftsmodell verbonnen ass.

Virleefeg Enquête

Loosst eis kucken wat an der Datebank lass ass. Drënner ass déi ursprénglech lues SQL Ufro:

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 ;

An hei sinn d'Zäiten:

Geplangte Zäit: 1.480 ms Ausféierungszäit: 1431924.650 ms

D'Ufro ass 150 Tausend Reihen gekrabbelt. De Query Planner huet e puer interessant Detailer gewisen, awer keng offensichtlech Flaschenhals.

Loosst eis d'Demande weider studéieren. Wéi Dir kënnt gesinn, mécht hien JOIN dräi Dëscher:

1. Manifestatiounen: Sessiounsinformatioun ze weisen: Browser, User Agent, Land, asw.
2. recording_data: opgeholl URLen, Säiten, Dauer vun de Visiten
3. URLen: Fir ze vermeiden datt extrem grouss URLen duplizéieren, späichere mir se an enger separater Tabell.

Notéiert och datt all eis Dëscher scho opgedeelt sinn account_id. Esou gëtt eng Situatioun ausgeschloss, wou ee besonnesch grousse Kont Problemer fir anerer mécht.

Sich no Hiweiser

Bei enger méi genauer Inspektioun gesi mer datt eppes mat enger bestëmmter Ufro falsch ass. Et ass derwäert dës Linn méi no ze kucken:

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

Den éischte Gedanken war, datt vläicht well ILIKE op all dës laang URLen (mir hunn iwwer 1,4 Milliounen eenzegaarteg URLen gesammelt fir dëse Kont) Leeschtung kann leiden.

Awer nee, dat ass net de Punkt!

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

Time: 5231.765 ms

D'Schabloun Sich Ufro selwer dauert nëmme 5 Sekonnen. Sich no engem Muster an enger Millioun eenzegaarteg URLen ass kloer kee Problem.

Déi nächst Verdächteg op der Lëscht sinn e puer JOIN. Vläicht huet hir Iwwerverbrauch de Verlängerung verursaacht? Normalerweis JOIN's sinn déi offensichtlechst Kandidate fir Leeschtungsproblemer, awer ech hunn net gegleeft datt eise Fall typesch war.

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

An dat war och net eise Fall. JOINEt huet sech zimlech séier erausgestallt.

De Krees vu Verdächtegen verklengert

Ech war prett fir d'Ufro ze änneren fir all méiglech Leeschtungsverbesserungen z'erreechen. Meng Equipe an ech hunn 2 Haaptideeën entwéckelt:

• Benotzt EXISTS fir Subquery URL: Mir wollten nach eng Kéier kucken, ob et Problemer mat der Ënnerquery fir d'URLen gëtt. Ee Wee fir dëst z'erreechen ass einfach ze benotzen EXISTS. EXISTS kann d'Performance staark verbesseren well se direkt ophält soubal et déi eenzeg String fënnt déi d'Konditioun entsprécht.

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

Gutt, jo. Subquery wann agewéckelt EXISTS, mécht alles super séier. Déi nächst logesch Fro ass firwat d'Demande mat JOIN-ami an d'Ënnerquery selwer si séier individuell, awer si schrecklech lues zesummen?

• Beweegt d'Subquery op den CTE : Wann d'Ufro eleng séier ass, kënne mir einfach dat séier Resultat als éischt berechnen an et dann un d'Haaptufro ubidden

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;

Mee et war nach ganz lues.

Den Täter ze fannen

Déi ganz Zäit huet eng kleng Saach virun mengen Ae geblénkt, déi ech dauernd op der Säit gebastelt hunn. Mee well et soss näischt méi war, hunn ech décidéiert hatt och ze kucken. Ech schwätzen iwwer && Bedreiwer. Äddi EXISTS just verbessert Leeschtung && war deen eenzege verbleiwen gemeinsame Faktor iwwer all Versioune vun der lueser Ufro.

Kucken op Dokumentatioun, mir gesinn dat && benotzt wann Dir gemeinsam Elementer tëscht zwee Arrays muss fannen.

An der ursprénglecher Ufro ass dëst:

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

Wat heescht datt mir eng Muster Sich op eise URLen maachen, dann d'Kräizung mat all den URLen mat gemeinsame Posts fannen. Dëst ass e bëssen konfus well "URL" hei net op d'Tabell bezitt déi all d'URLen enthält, mee op d'"URL" Kolonn an der Tabell recording_data.

Mat wuessende Mësstrauen iwwer &&, Ech hu probéiert eng Bestätegung fir si am Ufroplang ze fannen EXPLAIN ANALYZE (Ech hat schonn e Plang gespäichert, awer ech si meeschtens méi bequem an SQL ze experimentéieren wéi d'Opazitéit vun den Ufroplaner ze verstoen).

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

Et waren e puer Linnen vun Filtere nëmmen aus &&. Wat bedeit datt dës Operatioun net nëmmen deier war, mä och e puer Mol gemaach gouf.

Ech hunn dëst getest andeems ech d'Konditioun isoléiert

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[]

Dës Ufro war lues. Well déi JOIN-s sinn séier an subqueries si séier, déi eenzeg Saach lénks war && Bedreiwer.

Dëst ass just eng Schlësseloperatioun. Mir mussen ëmmer déi ganz ënnerierdesch Tabell vun URLen sichen fir no engem Muster ze sichen, a mir mussen ëmmer Kräizungen fannen. Mir kënnen net direkt no URL-Records sichen, well dës sinn nëmmen IDen déi op referenzéieren urls.

Um Wee fir eng Léisung

&& lues well béid Sätz enorm sinn. D'Operatioun wäert relativ séier sinn wann ech ersetzen urls op { "http://google.com/", "http://wingify.com/" }.

Ech hunn ugefaang no engem Wee ze sichen fir Set Kräizung am Postgres ze maachen ouni ze benotzen &&, awer ouni vill Erfolleg.

Um Enn hu mir décidéiert de Problem just an Isolatioun ze léisen: gitt mir alles urls Linnen fir déi d'URL mam Muster entsprécht. Ouni zousätzlech Konditioune wäert et sinn - 

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%'

Amplaz ewechzehuelen JOIN Syntax Ech hunn just eng Ënnerquery benotzt an erweidert recording_data.urls Array sou datt Dir d'Konditioun direkt an WHERE.

Dat Wichtegst hei ass dat && benotzt fir ze kontrolléieren ob e bestëmmten Entrée eng passende URL enthält. Wann Dir e bëssen squint, Dir kënnt gesinn, datt dës Operatioun duerch d'Elementer vun enger Array (oder Zeile vun engem Dësch) bewegt a stoppt wann eng Konditioun (Match) erfëllt ass. Erënnert Iech un näischt? jo, EXISTS.

Well op recording_data.urls kënne vu baussent dem Ënnerquery-Kontext referenzéiert ginn, wann dat passéiert kënne mir op eisen ale Frënd zréckfalen EXISTS a wéckelt d'Subquery domat.

Alles zesumme setzen, kréie mir déi lescht optimiséiert Ufro:

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%'
    );

An déi lescht Leadzäit Time: 1898.717 ms Zäit fir ze feieren?!?

Net sou séier! Als éischt musst Dir d'Korrektheet kontrolléieren. Ech war extrem verdächteg iwwer EXISTS Optimiséierung wéi et d'Logik ännert fir méi fréi opzehalen. Mir musse sécher sinn datt mir keen net offensichtleche Feeler op d'Ufro bäigefüügt hunn.

En einfachen Test war ze lafen count(*) op béid lues a séier Ufroe fir eng grouss Zuel vu verschiddenen Datesets. Dann, fir e klengen Ënnerdeel vun den Donnéeën, hunn ech manuell verifizéiert datt all Resultater richteg waren.

All Tester hunn konsequent positiv Resultater ginn. Mir hunn alles fixéiert!

Lektioune geléiert

Et gi vill Lektioune vun dëser Geschicht ze léieren:

1. Query Pläng erzielen net déi ganz Geschicht, awer si kënne Hiweiser ubidden
2. Déi Haaptverdächteg sinn net ëmmer déi richteg Täter
3. Luesen Ufroe kënnen opgedeelt ginn fir Flaschenhalsen ze isoléieren
4. Net all Optimisatiounen sinn reduktiv an der Natur
5. Benotzt EXIST, wa méiglech, kann zu dramateschen Erhéijunge vun der Produktivitéit féieren

Konklusioun

Mir sinn vun enger Ufrozäit vu ~ 24 Minutten op 2 Sekonnen gaang - zimmlech eng bedeitend Leeschtungserhéijung! Och wann dësen Artikel grouss erauskomm ass, sinn all d'Experimenter, déi mir gemaach hunn, an engem Dag geschitt, an et gouf geschat datt se tëscht 1,5 an 2 Stonnen fir Optimisatiounen an Tester gedauert hunn.

SQL ass eng wonnerbar Sprooch wann Dir keng Angscht dofir hutt, awer probéiert se ze léieren an ze benotzen. Andeems Dir e gutt Verständnis hutt wéi SQL Ufroen ausgefouert ginn, wéi d'Datebank Ufropläng generéiert, wéi Indizes funktionnéieren, an einfach d'Gréisst vun den Donnéeën mat deem Dir beschäftegt, kënnt Dir ganz erfollegräich sinn fir Ufroen ze optimiséieren. Et ass awer gläich wichteg fir weider verschidden Approchen ze probéieren an de Problem lues a lues ofzebriechen, d'Flaschenhals ze fannen.

Dee beschten Deel iwwer d'Resultater wéi dëst z'erreechen ass déi merkbar, siichtbar Geschwindegkeetsverbesserung - wou e Bericht dee virdru net emol lued elo bal direkt lued.

Besonnesche Merci un meng Komeroden um Kommando vun Aditya Mishra, Aditya Gauru и Varun Malhotra fir Brainstorming an Dinkar Pandir fir e wichtege Feeler an eiser leschter Ufro ze fannen ier mer endlech Äddi gesot hunn!

Source: will.com