ಒಂದು SQL ತನಿಖೆಯ ಕಥೆ

ಕಳೆದ ಡಿಸೆಂಬರ್‌ನಲ್ಲಿ ನಾನು VWO ಬೆಂಬಲ ತಂಡದಿಂದ ಆಸಕ್ತಿದಾಯಕ ಬಗ್ ವರದಿಯನ್ನು ಸ್ವೀಕರಿಸಿದ್ದೇನೆ. ದೊಡ್ಡ ಕಾರ್ಪೊರೇಟ್ ಕ್ಲೈಂಟ್‌ಗಾಗಿ ವಿಶ್ಲೇಷಣಾ ವರದಿಗಳಲ್ಲಿ ಒಂದಕ್ಕೆ ಲೋಡ್ ಮಾಡುವ ಸಮಯವು ನಿಷೇಧಿತವಾಗಿದೆ. ಮತ್ತು ಇದು ನನ್ನ ಜವಾಬ್ದಾರಿಯ ಕ್ಷೇತ್ರವಾಗಿರುವುದರಿಂದ, ನಾನು ತಕ್ಷಣ ಸಮಸ್ಯೆಯನ್ನು ಪರಿಹರಿಸುವತ್ತ ಗಮನ ಹರಿಸಿದೆ.

ಪೂರ್ವೇತಿಹಾಸದ

ನಾನು ಏನು ಮಾತನಾಡುತ್ತಿದ್ದೇನೆ ಎಂಬುದನ್ನು ಸ್ಪಷ್ಟಪಡಿಸಲು, ನಾನು ನಿಮಗೆ VWO ಬಗ್ಗೆ ಸ್ವಲ್ಪ ಹೇಳುತ್ತೇನೆ. ಇದು ನಿಮ್ಮ ವೆಬ್‌ಸೈಟ್‌ಗಳಲ್ಲಿ ವಿವಿಧ ಉದ್ದೇಶಿತ ಪ್ರಚಾರಗಳನ್ನು ಪ್ರಾರಂಭಿಸಬಹುದಾದ ವೇದಿಕೆಯಾಗಿದೆ: A/B ಪ್ರಯೋಗಗಳನ್ನು ನಡೆಸುವುದು, ಸಂದರ್ಶಕರು ಮತ್ತು ಪರಿವರ್ತನೆಗಳನ್ನು ಟ್ರ್ಯಾಕ್ ಮಾಡಿ, ಮಾರಾಟದ ಕೊಳವೆಯನ್ನು ವಿಶ್ಲೇಷಿಸಿ, ಶಾಖ ನಕ್ಷೆಗಳನ್ನು ಪ್ರದರ್ಶಿಸಿ ಮತ್ತು ಭೇಟಿ ರೆಕಾರ್ಡಿಂಗ್‌ಗಳನ್ನು ಪ್ಲೇ ಮಾಡಿ.

ಆದರೆ ವೇದಿಕೆಯ ಪ್ರಮುಖ ವಿಷಯವೆಂದರೆ ವರದಿ ಮಾಡುವುದು. ಮೇಲಿನ ಎಲ್ಲಾ ಕಾರ್ಯಗಳು ಪರಸ್ಪರ ಸಂಬಂಧ ಹೊಂದಿವೆ. ಮತ್ತು ಕಾರ್ಪೊರೇಟ್ ಕ್ಲೈಂಟ್‌ಗಳಿಗೆ, ವಿಶ್ಲೇಷಣಾತ್ಮಕ ರೂಪದಲ್ಲಿ ಪ್ರಸ್ತುತಪಡಿಸುವ ಶಕ್ತಿಯುತ ವೇದಿಕೆಯಿಲ್ಲದೆ ಹೆಚ್ಚಿನ ಪ್ರಮಾಣದ ಮಾಹಿತಿಯು ಸರಳವಾಗಿ ನಿಷ್ಪ್ರಯೋಜಕವಾಗಿರುತ್ತದೆ.

ಪ್ಲಾಟ್‌ಫಾರ್ಮ್ ಅನ್ನು ಬಳಸಿಕೊಂಡು, ನೀವು ದೊಡ್ಡ ಡೇಟಾ ಸೆಟ್‌ನಲ್ಲಿ ಯಾದೃಚ್ಛಿಕ ಪ್ರಶ್ನೆಯನ್ನು ಮಾಡಬಹುದು. ಒಂದು ಸರಳ ಉದಾಹರಣೆ ಇಲ್ಲಿದೆ:

Chrome ಅನ್ನು ಬಳಸಿದ ಅಥವಾ (ಯುರೋಪ್‌ನಲ್ಲಿದ್ದು ಮತ್ತು iPhone ಅನ್ನು ಬಳಸಿದ) ಜನರಿಗೆ <date d1> ರಿಂದ <date d2> ವರೆಗೆ ಎಲ್ಲಾ ಕ್ಲಿಕ್‌ಗಳನ್ನು "abc.com" ಪುಟದಲ್ಲಿ ತೋರಿಸಿ

ಬೂಲಿಯನ್ ನಿರ್ವಾಹಕರಿಗೆ ಗಮನ ಕೊಡಿ. ಮಾದರಿಗಳನ್ನು ಪಡೆಯಲು ನಿರಂಕುಶವಾಗಿ ಸಂಕೀರ್ಣವಾದ ಪ್ರಶ್ನೆಗಳನ್ನು ಮಾಡಲು ಪ್ರಶ್ನೆ ಇಂಟರ್ಫೇಸ್‌ನಲ್ಲಿ ಕ್ಲೈಂಟ್‌ಗಳಿಗೆ ಅವು ಲಭ್ಯವಿರುತ್ತವೆ.

ನಿಧಾನ ವಿನಂತಿ

ಪ್ರಶ್ನೆಯಲ್ಲಿರುವ ಕ್ಲೈಂಟ್ ಅಂತರ್ಬೋಧೆಯಿಂದ ತ್ವರಿತವಾಗಿ ಕೆಲಸ ಮಾಡಬೇಕಾದ ಏನನ್ನಾದರೂ ಮಾಡಲು ಪ್ರಯತ್ನಿಸುತ್ತಿದ್ದಾರೆ:

"/jobs" ಹೊಂದಿರುವ URL ನೊಂದಿಗೆ ಯಾವುದೇ ಪುಟಕ್ಕೆ ಭೇಟಿ ನೀಡಿದ ಬಳಕೆದಾರರಿಗೆ ಎಲ್ಲಾ ಸೆಶನ್ ದಾಖಲೆಗಳನ್ನು ತೋರಿಸಿ

ಈ ಸೈಟ್ ಟನ್ ಟ್ರಾಫಿಕ್ ಅನ್ನು ಹೊಂದಿತ್ತು ಮತ್ತು ಅದಕ್ಕಾಗಿಯೇ ನಾವು ಮಿಲಿಯನ್‌ಗಿಂತಲೂ ಹೆಚ್ಚು ಅನನ್ಯ URL ಗಳನ್ನು ಸಂಗ್ರಹಿಸುತ್ತಿದ್ದೇವೆ. ಮತ್ತು ಅವರು ತಮ್ಮ ವ್ಯವಹಾರ ಮಾದರಿಗೆ ಸಂಬಂಧಿಸಿದ ಸರಳವಾದ URL ಟೆಂಪ್ಲೇಟ್ ಅನ್ನು ಹುಡುಕಲು ಬಯಸಿದ್ದರು.

ಪ್ರಾಥಮಿಕ ತನಿಖೆ

ಡೇಟಾಬೇಸ್‌ನಲ್ಲಿ ಏನು ನಡೆಯುತ್ತಿದೆ ಎಂಬುದನ್ನು ನೋಡೋಣ. ಮೂಲ ನಿಧಾನಗತಿಯ SQL ಪ್ರಶ್ನೆಯನ್ನು ಕೆಳಗೆ ನೀಡಲಾಗಿದೆ:

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 ;

ಮತ್ತು ಸಮಯಗಳು ಇಲ್ಲಿವೆ:

ಯೋಜಿತ ಸಮಯ: 1.480 ms ಎಕ್ಸಿಕ್ಯೂಶನ್ ಸಮಯ: 1431924.650 ms

ಪ್ರಶ್ನೆಯು 150 ಸಾವಿರ ಸಾಲುಗಳನ್ನು ಕ್ರಾಲ್ ಮಾಡಿದೆ. ಪ್ರಶ್ನೆ ಯೋಜಕರು ಒಂದೆರಡು ಆಸಕ್ತಿದಾಯಕ ವಿವರಗಳನ್ನು ತೋರಿಸಿದರು, ಆದರೆ ಯಾವುದೇ ಸ್ಪಷ್ಟ ಅಡಚಣೆಗಳಿಲ್ಲ.

ವಿನಂತಿಯನ್ನು ಮತ್ತಷ್ಟು ಅಧ್ಯಯನ ಮಾಡೋಣ. ನೀವು ನೋಡುವಂತೆ, ಅವನು ಮಾಡುತ್ತಾನೆ JOIN ಮೂರು ಕೋಷ್ಟಕಗಳು:

1. ಅವಧಿಗಳು: ಸೆಷನ್ ಮಾಹಿತಿಯನ್ನು ಪ್ರದರ್ಶಿಸಲು: ಬ್ರೌಸರ್, ಬಳಕೆದಾರ ಏಜೆಂಟ್, ದೇಶ, ಇತ್ಯಾದಿ.
2. ರೆಕಾರ್ಡಿಂಗ್_ಡೇಟಾ: ದಾಖಲಾದ URL ಗಳು, ಪುಟಗಳು, ಭೇಟಿಗಳ ಅವಧಿ
3. URL ಗಳು: ಅತ್ಯಂತ ದೊಡ್ಡ URL ಗಳನ್ನು ನಕಲು ಮಾಡುವುದನ್ನು ತಪ್ಪಿಸಲು, ನಾವು ಅವುಗಳನ್ನು ಪ್ರತ್ಯೇಕ ಕೋಷ್ಟಕದಲ್ಲಿ ಸಂಗ್ರಹಿಸುತ್ತೇವೆ.

ನಮ್ಮ ಎಲ್ಲಾ ಕೋಷ್ಟಕಗಳನ್ನು ಈಗಾಗಲೇ ವಿಭಜಿಸಲಾಗಿದೆ ಎಂಬುದನ್ನು ಗಮನಿಸಿ account_id. ಈ ರೀತಿಯಾಗಿ, ಒಂದು ನಿರ್ದಿಷ್ಟವಾಗಿ ದೊಡ್ಡ ಖಾತೆಯು ಇತರರಿಗೆ ಸಮಸ್ಯೆಗಳನ್ನು ಉಂಟುಮಾಡುವ ಪರಿಸ್ಥಿತಿಯನ್ನು ಹೊರಗಿಡಲಾಗುತ್ತದೆ.

ಸುಳಿವುಗಳನ್ನು ಹುಡುಕುತ್ತಿದ್ದೇವೆ

ಸೂಕ್ಷ್ಮವಾಗಿ ಪರಿಶೀಲಿಸಿದಾಗ, ನಿರ್ದಿಷ್ಟ ವಿನಂತಿಯಲ್ಲಿ ಏನೋ ತಪ್ಪಾಗಿದೆ ಎಂದು ನಾವು ನೋಡುತ್ತೇವೆ. ಈ ಸಾಲನ್ನು ಹತ್ತಿರದಿಂದ ನೋಡುವುದು ಯೋಗ್ಯವಾಗಿದೆ:

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

ಮೊದಲ ಆಲೋಚನೆ ಬಹುಶಃ ಕಾರಣ ILIKE ಈ ಎಲ್ಲಾ ದೀರ್ಘ URL ಗಳಲ್ಲಿ (ನಾವು 1,4 ಮಿಲಿಯನ್‌ಗಿಂತಲೂ ಹೆಚ್ಚು ಹೊಂದಿದ್ದೇವೆ ಅನನ್ಯ ಈ ಖಾತೆಗಾಗಿ ಸಂಗ್ರಹಿಸಲಾದ URL ಗಳು) ಕಾರ್ಯಕ್ಷಮತೆಗೆ ತೊಂದರೆಯಾಗಬಹುದು.

ಆದರೆ ಇಲ್ಲ, ಅದು ವಿಷಯವಲ್ಲ!

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

Time: 5231.765 ms

ಟೆಂಪ್ಲೇಟ್ ಹುಡುಕಾಟ ವಿನಂತಿಯು ಕೇವಲ 5 ಸೆಕೆಂಡುಗಳನ್ನು ತೆಗೆದುಕೊಳ್ಳುತ್ತದೆ. ಮಿಲಿಯನ್ ಅನನ್ಯ URL ಗಳಲ್ಲಿ ಮಾದರಿಯನ್ನು ಹುಡುಕುವುದು ಸ್ಪಷ್ಟವಾಗಿ ಸಮಸ್ಯೆಯಲ್ಲ.

ಪಟ್ಟಿಯಲ್ಲಿರುವ ಮುಂದಿನ ಶಂಕಿತರು ಹಲವಾರು JOIN. ಬಹುಶಃ ಅವರ ಅತಿಯಾದ ಬಳಕೆಯು ನಿಧಾನಗತಿಗೆ ಕಾರಣವಾಗಿದೆಯೇ? ಸಾಮಾನ್ಯವಾಗಿ JOINಕಾರ್ಯಕ್ಷಮತೆಯ ಸಮಸ್ಯೆಗಳಿಗೆ ಅತ್ಯಂತ ಸ್ಪಷ್ಟ ಅಭ್ಯರ್ಥಿಗಳು, ಆದರೆ ನಮ್ಮ ಪ್ರಕರಣವು ವಿಶಿಷ್ಟವಾಗಿದೆ ಎಂದು ನಾನು ನಂಬಲಿಲ್ಲ.

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

ಮತ್ತು ಇದು ನಮ್ಮ ಪ್ರಕರಣವಾಗಿರಲಿಲ್ಲ. JOINನ ಸಾಕಷ್ಟು ವೇಗವಾಗಿ ಹೊರಹೊಮ್ಮಿತು.

ಶಂಕಿತರ ವಲಯವನ್ನು ಸಂಕುಚಿತಗೊಳಿಸುವುದು

ಯಾವುದೇ ಸಂಭಾವ್ಯ ಕಾರ್ಯಕ್ಷಮತೆ ಸುಧಾರಣೆಗಳನ್ನು ಸಾಧಿಸಲು ಪ್ರಶ್ನೆಯನ್ನು ಬದಲಾಯಿಸಲು ನಾನು ಸಿದ್ಧನಾಗಿದ್ದೆ. ನನ್ನ ತಂಡ ಮತ್ತು ನಾನು 2 ಮುಖ್ಯ ಆಲೋಚನೆಗಳನ್ನು ಅಭಿವೃದ್ಧಿಪಡಿಸಿದ್ದೇವೆ:

• ಉಪಪ್ರಶ್ನೆ URL ಗಾಗಿ EXISTS ಅನ್ನು ಬಳಸಿ: URL ಗಳ ಉಪಪ್ರಶ್ನೆಯಲ್ಲಿ ಯಾವುದೇ ಸಮಸ್ಯೆಗಳಿದ್ದರೆ ನಾವು ಮತ್ತೊಮ್ಮೆ ಪರಿಶೀಲಿಸಲು ಬಯಸುತ್ತೇವೆ. ಇದನ್ನು ಸಾಧಿಸಲು ಒಂದು ಮಾರ್ಗವೆಂದರೆ ಸರಳವಾಗಿ ಬಳಸುವುದು EXISTS. EXISTS ಮಾಡಬಹುದು ಕಾರ್ಯನಿರ್ವಹಣೆಯನ್ನು ಹೆಚ್ಚು ಸುಧಾರಿಸಿ ಏಕೆಂದರೆ ಅದು ಸ್ಥಿತಿಗೆ ಹೊಂದಿಕೆಯಾಗುವ ಏಕೈಕ ಸ್ಟ್ರಿಂಗ್ ಅನ್ನು ಕಂಡುಕೊಂಡ ತಕ್ಷಣ ಅದು ಕೊನೆಗೊಳ್ಳುತ್ತದೆ.

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

ಸರಿ, ಹೌದು. ಸುತ್ತಿದಾಗ ಸಬ್ಕ್ವೆರಿ EXISTS, ಎಲ್ಲವನ್ನೂ ಸೂಪರ್ ಫಾಸ್ಟ್ ಮಾಡುತ್ತದೆ. ಇದರೊಂದಿಗೆ ವಿನಂತಿ ಏಕೆ ಎಂಬುದು ಮುಂದಿನ ತಾರ್ಕಿಕ ಪ್ರಶ್ನೆಯಾಗಿದೆ JOIN-ಅಮಿ ಮತ್ತು ಸಬ್‌ಕ್ವೆರಿಯು ಪ್ರತ್ಯೇಕವಾಗಿ ವೇಗವಾಗಿರುತ್ತದೆ, ಆದರೆ ಒಟ್ಟಿಗೆ ಭಯಂಕರವಾಗಿ ನಿಧಾನವಾಗಿದೆಯೇ?

• ಉಪಪ್ರಶ್ನೆಯನ್ನು CTE ಗೆ ಸರಿಸಲಾಗುತ್ತಿದೆ : ಪ್ರಶ್ನೆಯು ತನ್ನದೇ ಆದ ಮೇಲೆ ವೇಗವಾಗಿದ್ದರೆ, ನಾವು ಮೊದಲು ವೇಗದ ಫಲಿತಾಂಶವನ್ನು ಸರಳವಾಗಿ ಲೆಕ್ಕಾಚಾರ ಮಾಡಬಹುದು ಮತ್ತು ನಂತರ ಅದನ್ನು ಮುಖ್ಯ ಪ್ರಶ್ನೆಗೆ ಒದಗಿಸಬಹುದು

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;

ಆದರೆ ಇನ್ನೂ ಬಹಳ ನಿಧಾನವಾಗಿತ್ತು.

ಅಪರಾಧಿಯನ್ನು ಕಂಡುಹಿಡಿಯುವುದು

ಈ ಸಮಯದಲ್ಲಿ, ಒಂದು ಸಣ್ಣ ವಿಷಯ ನನ್ನ ಕಣ್ಣುಗಳ ಮುಂದೆ ಹೊಳೆಯಿತು, ಅದನ್ನು ನಾನು ನಿರಂತರವಾಗಿ ಪಕ್ಕಕ್ಕೆ ತಳ್ಳಿದೆ. ಆದರೆ ಬೇರೇನೂ ಇಲ್ಲದ್ದರಿಂದ ಅವಳನ್ನೂ ನೋಡಬೇಕೆಂದು ನಿರ್ಧರಿಸಿದೆ. ನಾನು ಮಾತನಾಡುತ್ತಿದ್ದೇನೆ && ಆಪರೇಟರ್. ವಿದಾಯ EXISTS ಕೇವಲ ಸುಧಾರಿತ ಕಾರ್ಯಕ್ಷಮತೆ && ನಿಧಾನಗತಿಯ ಪ್ರಶ್ನೆಯ ಎಲ್ಲಾ ಆವೃತ್ತಿಗಳಲ್ಲಿ ಮಾತ್ರ ಉಳಿದಿರುವ ಸಾಮಾನ್ಯ ಅಂಶವಾಗಿದೆ.

ಅತ್ತ ನೋಡುತ್ತ ದಸ್ತಾವೇಜನ್ನು, ನಾವು ಅದನ್ನು ನೋಡುತ್ತೇವೆ && ನೀವು ಎರಡು ಸರಣಿಗಳ ನಡುವೆ ಸಾಮಾನ್ಯ ಅಂಶಗಳನ್ನು ಕಂಡುಹಿಡಿಯಬೇಕಾದಾಗ ಬಳಸಲಾಗುತ್ತದೆ.

ಮೂಲ ವಿನಂತಿಯಲ್ಲಿ ಇದು:

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

ಇದರರ್ಥ ನಾವು ನಮ್ಮ URL ಗಳಲ್ಲಿ ಮಾದರಿ ಹುಡುಕಾಟವನ್ನು ಮಾಡುತ್ತೇವೆ, ನಂತರ ಸಾಮಾನ್ಯ ಪೋಸ್ಟ್‌ಗಳೊಂದಿಗೆ ಎಲ್ಲಾ URL ಗಳೊಂದಿಗೆ ಛೇದಕವನ್ನು ಕಂಡುಹಿಡಿಯಿರಿ. ಇದು ಸ್ವಲ್ಪ ಗೊಂದಲಮಯವಾಗಿದೆ ಏಕೆಂದರೆ ಇಲ್ಲಿ "url" ಎಲ್ಲಾ URL ಗಳನ್ನು ಹೊಂದಿರುವ ಕೋಷ್ಟಕವನ್ನು ಉಲ್ಲೇಖಿಸುವುದಿಲ್ಲ, ಆದರೆ ಕೋಷ್ಟಕದಲ್ಲಿನ "url" ಕಾಲಮ್ ಅನ್ನು ಉಲ್ಲೇಖಿಸುತ್ತದೆ. recording_data.

ಎಂಬ ಬಗ್ಗೆ ಹೆಚ್ಚುತ್ತಿರುವ ಅನುಮಾನಗಳೊಂದಿಗೆ &&, ರಚಿಸಲಾದ ಪ್ರಶ್ನೆ ಯೋಜನೆಯಲ್ಲಿ ನಾನು ಅವರಿಗೆ ದೃಢೀಕರಣವನ್ನು ಹುಡುಕಲು ಪ್ರಯತ್ನಿಸಿದೆ EXPLAIN ANALYZE (ನಾನು ಈಗಾಗಲೇ ಯೋಜನೆಯನ್ನು ಉಳಿಸಿದ್ದೇನೆ, ಆದರೆ ಪ್ರಶ್ನೆ ಯೋಜಕರ ಅಪಾರದರ್ಶಕತೆಯನ್ನು ಅರ್ಥಮಾಡಿಕೊಳ್ಳಲು ಪ್ರಯತ್ನಿಸುವುದಕ್ಕಿಂತ ಹೆಚ್ಚಾಗಿ ನಾನು SQL ನಲ್ಲಿ ಪ್ರಯೋಗ ಮಾಡಲು ಹೆಚ್ಚು ಆರಾಮದಾಯಕವಾಗಿದೆ).

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

ನಿಂದ ಮಾತ್ರ ಹಲವಾರು ಸಾಲುಗಳ ಫಿಲ್ಟರ್‌ಗಳು ಇದ್ದವು &&. ಇದರರ್ಥ ಈ ಕಾರ್ಯಾಚರಣೆಯು ದುಬಾರಿ ಮಾತ್ರವಲ್ಲ, ಹಲವಾರು ಬಾರಿ ನಡೆಸಿತು.

ಸ್ಥಿತಿಯನ್ನು ಪ್ರತ್ಯೇಕಿಸುವ ಮೂಲಕ ನಾನು ಇದನ್ನು ಪರೀಕ್ಷಿಸಿದೆ

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

ಈ ಪ್ರಶ್ನೆ ನಿಧಾನವಾಗಿತ್ತು. ಏಕೆಂದರೆ ದಿ JOIN-ಗಳು ವೇಗವಾಗಿರುತ್ತವೆ ಮತ್ತು ಉಪಪ್ರಶ್ನೆಗಳು ವೇಗವಾಗಿರುತ್ತವೆ, ಉಳಿದಿರುವುದು ಮಾತ್ರ && ಆಪರೇಟರ್.

ಇದು ಕೇವಲ ಒಂದು ಪ್ರಮುಖ ಕಾರ್ಯಾಚರಣೆಯಾಗಿದೆ. ನಮೂನೆಯನ್ನು ಹುಡುಕಲು ನಾವು ಯಾವಾಗಲೂ URL ಗಳ ಸಂಪೂರ್ಣ ಆಧಾರವಾಗಿರುವ ಕೋಷ್ಟಕವನ್ನು ಹುಡುಕಬೇಕಾಗಿದೆ ಮತ್ತು ನಾವು ಯಾವಾಗಲೂ ಛೇದಕಗಳನ್ನು ಹುಡುಕಬೇಕಾಗಿದೆ. ನಾವು ನೇರವಾಗಿ URL ದಾಖಲೆಗಳ ಮೂಲಕ ಹುಡುಕಲು ಸಾಧ್ಯವಿಲ್ಲ, ಏಕೆಂದರೆ ಇವು ಕೇವಲ IDಗಳನ್ನು ಉಲ್ಲೇಖಿಸುತ್ತವೆ urls.

ಪರಿಹಾರದ ದಾರಿಯಲ್ಲಿ

&& ನಿಧಾನವಾಗಿ ಏಕೆಂದರೆ ಎರಡೂ ಸೆಟ್‌ಗಳು ದೊಡ್ಡದಾಗಿರುತ್ತವೆ. ನಾನು ಬದಲಾಯಿಸಿದರೆ ಕಾರ್ಯಾಚರಣೆಯು ತುಲನಾತ್ಮಕವಾಗಿ ತ್ವರಿತವಾಗಿರುತ್ತದೆ urls ಮೇಲೆ { "http://google.com/", "http://wingify.com/" }.

ನಾನು ಬಳಸದೆಯೇ ಪೋಸ್ಟ್‌ಗ್ರೆಸ್‌ನಲ್ಲಿ ಸೆಟ್ ಛೇದಕವನ್ನು ಮಾಡಲು ಒಂದು ಮಾರ್ಗವನ್ನು ಹುಡುಕಲಾರಂಭಿಸಿದೆ &&, ಆದರೆ ಹೆಚ್ಚಿನ ಯಶಸ್ಸು ಇಲ್ಲದೆ.

ಕೊನೆಯಲ್ಲಿ, ನಾವು ಸಮಸ್ಯೆಯನ್ನು ಪ್ರತ್ಯೇಕವಾಗಿ ಪರಿಹರಿಸಲು ನಿರ್ಧರಿಸಿದ್ದೇವೆ: ನನಗೆ ಎಲ್ಲವನ್ನೂ ನೀಡಿ urls URL ಮಾದರಿಗೆ ಹೊಂದಿಕೆಯಾಗುವ ಸಾಲುಗಳು. ಹೆಚ್ಚುವರಿ ಷರತ್ತುಗಳಿಲ್ಲದೆ ಅದು ಇರುತ್ತದೆ - 

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

ಬದಲಾಗಿ JOIN ಸಿಂಟ್ಯಾಕ್ಸ್ ನಾನು ಉಪಪ್ರಶ್ನೆಯನ್ನು ಬಳಸಿದ್ದೇನೆ ಮತ್ತು ವಿಸ್ತರಿಸಿದ್ದೇನೆ recording_data.urls ಅರೇ ಆದ್ದರಿಂದ ನೀವು ನೇರವಾಗಿ ಸ್ಥಿತಿಯನ್ನು ಅನ್ವಯಿಸಬಹುದು WHERE.

ಇಲ್ಲಿ ಅತ್ಯಂತ ಮುಖ್ಯವಾದ ವಿಷಯವೆಂದರೆ ಅದು && ನೀಡಿರುವ ನಮೂದು ಹೊಂದಾಣಿಕೆಯ URL ಅನ್ನು ಹೊಂದಿದೆಯೇ ಎಂದು ಪರಿಶೀಲಿಸಲು ಬಳಸಲಾಗುತ್ತದೆ. ನೀವು ಸ್ವಲ್ಪ ಕಣ್ಣು ಹಾಯಿಸಿದರೆ, ಈ ಕಾರ್ಯಾಚರಣೆಯು ರಚನೆಯ ಅಂಶಗಳ ಮೂಲಕ ಚಲಿಸುತ್ತದೆ (ಅಥವಾ ಟೇಬಲ್‌ನ ಸಾಲುಗಳು) ಮತ್ತು ಸ್ಥಿತಿಯನ್ನು (ಪಂದ್ಯ) ಪೂರೈಸಿದಾಗ ನಿಲ್ಲುತ್ತದೆ. ನಿಮಗೆ ಯಾವುದನ್ನೂ ನೆನಪಿಸುವುದಿಲ್ಲವೇ? ಹೌದು, EXISTS.

ಅಂದಿನಿಂದ recording_data.urls ಸಬ್ಕ್ವೆರಿ ಸಂದರ್ಭದ ಹೊರಗಿನಿಂದ ಉಲ್ಲೇಖಿಸಬಹುದು, ಇದು ಸಂಭವಿಸಿದಾಗ ನಾವು ನಮ್ಮ ಹಳೆಯ ಸ್ನೇಹಿತನ ಮೇಲೆ ಹಿಂತಿರುಗಬಹುದು EXISTS ಮತ್ತು ಅದರೊಂದಿಗೆ ಸಬ್ಕ್ವೆರಿಯನ್ನು ಕಟ್ಟಿಕೊಳ್ಳಿ.

ಎಲ್ಲವನ್ನೂ ಒಟ್ಟುಗೂಡಿಸಿ, ನಾವು ಅಂತಿಮ ಆಪ್ಟಿಮೈಸ್ಡ್ ಪ್ರಶ್ನೆಯನ್ನು ಪಡೆಯುತ್ತೇವೆ:

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

ಮತ್ತು ಅಂತಿಮ ಪ್ರಮುಖ ಸಮಯ Time: 1898.717 ms ಆಚರಿಸಲು ಸಮಯ?!?

ಅಷ್ಟು ಬೇಗ ಅಲ್ಲ! ಮೊದಲು ನೀವು ಸರಿಯಾದತೆಯನ್ನು ಪರಿಶೀಲಿಸಬೇಕು. ನನಗೆ ಬಹಳ ಅನುಮಾನವಿತ್ತು EXISTS ಆಪ್ಟಿಮೈಸೇಶನ್ ತರ್ಕವನ್ನು ಮೊದಲೇ ಕೊನೆಗೊಳಿಸಲು ಬದಲಾಯಿಸುತ್ತದೆ. ವಿನಂತಿಗೆ ನಾವು ಸ್ಪಷ್ಟವಲ್ಲದ ದೋಷವನ್ನು ಸೇರಿಸಿಲ್ಲ ಎಂದು ನಾವು ಖಚಿತಪಡಿಸಿಕೊಳ್ಳಬೇಕು.

ಸರಳ ಪರೀಕ್ಷೆಯನ್ನು ನಡೆಸಬೇಕಾಗಿತ್ತು count(*) ಹೆಚ್ಚಿನ ಸಂಖ್ಯೆಯ ವಿಭಿನ್ನ ಡೇಟಾ ಸೆಟ್‌ಗಳಿಗಾಗಿ ನಿಧಾನ ಮತ್ತು ವೇಗದ ಪ್ರಶ್ನೆಗಳೆರಡರಲ್ಲೂ. ನಂತರ, ಡೇಟಾದ ಸಣ್ಣ ಉಪವಿಭಾಗಕ್ಕಾಗಿ, ಎಲ್ಲಾ ಫಲಿತಾಂಶಗಳು ಸರಿಯಾಗಿವೆ ಎಂದು ನಾನು ಹಸ್ತಚಾಲಿತವಾಗಿ ಪರಿಶೀಲಿಸಿದ್ದೇನೆ.

ಎಲ್ಲಾ ಪರೀಕ್ಷೆಗಳು ಸತತವಾಗಿ ಧನಾತ್ಮಕ ಫಲಿತಾಂಶಗಳನ್ನು ನೀಡಿವೆ. ನಾವು ಎಲ್ಲವನ್ನೂ ಸರಿಪಡಿಸಿದ್ದೇವೆ!

ಕಲಿತ ಪಾಠಗಳು

ಈ ಕಥೆಯಿಂದ ಕಲಿಯಲು ಹಲವು ಪಾಠಗಳಿವೆ:

1. ಪ್ರಶ್ನೆ ಯೋಜನೆಗಳು ಸಂಪೂರ್ಣ ಕಥೆಯನ್ನು ಹೇಳುವುದಿಲ್ಲ, ಆದರೆ ಅವು ಸುಳಿವುಗಳನ್ನು ನೀಡಬಹುದು
2. ಮುಖ್ಯ ಶಂಕಿತರು ಯಾವಾಗಲೂ ನಿಜವಾದ ಅಪರಾಧಿಗಳಲ್ಲ
3. ಅಡಚಣೆಗಳನ್ನು ಪ್ರತ್ಯೇಕಿಸಲು ನಿಧಾನವಾದ ಪ್ರಶ್ನೆಗಳನ್ನು ವಿಭಜಿಸಬಹುದು
4. ಎಲ್ಲಾ ಆಪ್ಟಿಮೈಸೇಶನ್‌ಗಳು ನಿಸರ್ಗದಲ್ಲಿ ಕಡಿಮೆಯಾಗುವುದಿಲ್ಲ
5. ಬಳಸಿ EXIST, ಸಾಧ್ಯವಾದರೆ, ಉತ್ಪಾದಕತೆಯಲ್ಲಿ ನಾಟಕೀಯ ಹೆಚ್ಚಳಕ್ಕೆ ಕಾರಣವಾಗಬಹುದು

ತೀರ್ಮಾನಕ್ಕೆ

ನಾವು ~24 ನಿಮಿಷಗಳ ಪ್ರಶ್ನೆಯ ಸಮಯದಿಂದ 2 ಸೆಕೆಂಡುಗಳವರೆಗೆ ಹೋಗಿದ್ದೇವೆ - ಸಾಕಷ್ಟು ಗಮನಾರ್ಹ ಕಾರ್ಯಕ್ಷಮತೆಯ ಹೆಚ್ಚಳ! ಈ ಲೇಖನವು ದೊಡ್ಡದಾಗಿ ಬಂದಿದ್ದರೂ, ನಾವು ಮಾಡಿದ ಎಲ್ಲಾ ಪ್ರಯೋಗಗಳು ಒಂದೇ ದಿನದಲ್ಲಿ ಸಂಭವಿಸಿದವು ಮತ್ತು ಆಪ್ಟಿಮೈಸೇಶನ್ ಮತ್ತು ಪರೀಕ್ಷೆಗಾಗಿ ಅವರು 1,5 ರಿಂದ 2 ಗಂಟೆಗಳವರೆಗೆ ತೆಗೆದುಕೊಂಡಿದ್ದಾರೆ ಎಂದು ಅಂದಾಜಿಸಲಾಗಿದೆ.

ನೀವು ಅದರ ಬಗ್ಗೆ ಭಯಪಡದಿದ್ದರೆ SQL ಅದ್ಭುತ ಭಾಷೆಯಾಗಿದೆ, ಆದರೆ ಅದನ್ನು ಕಲಿಯಲು ಮತ್ತು ಬಳಸಲು ಪ್ರಯತ್ನಿಸಿ. SQL ಪ್ರಶ್ನೆಗಳನ್ನು ಹೇಗೆ ಕಾರ್ಯಗತಗೊಳಿಸಲಾಗುತ್ತದೆ, ಡೇಟಾಬೇಸ್ ಪ್ರಶ್ನೆ ಯೋಜನೆಗಳನ್ನು ಹೇಗೆ ರಚಿಸುತ್ತದೆ, ಸೂಚ್ಯಂಕಗಳು ಹೇಗೆ ಕಾರ್ಯನಿರ್ವಹಿಸುತ್ತವೆ ಮತ್ತು ಸರಳವಾಗಿ ನೀವು ವ್ಯವಹರಿಸುತ್ತಿರುವ ಡೇಟಾದ ಗಾತ್ರದ ಬಗ್ಗೆ ಉತ್ತಮ ತಿಳುವಳಿಕೆಯನ್ನು ಹೊಂದಿರುವ ಮೂಲಕ, ನೀವು ಪ್ರಶ್ನೆಗಳನ್ನು ಅತ್ಯುತ್ತಮವಾಗಿಸುವಲ್ಲಿ ಯಶಸ್ವಿಯಾಗಬಹುದು. ಆದಾಗ್ಯೂ, ವಿಭಿನ್ನ ವಿಧಾನಗಳನ್ನು ಪ್ರಯತ್ನಿಸುವುದನ್ನು ಮುಂದುವರಿಸುವುದು ಮತ್ತು ಸಮಸ್ಯೆಯನ್ನು ನಿಧಾನವಾಗಿ ಮುರಿಯುವುದು, ಅಡಚಣೆಗಳನ್ನು ಕಂಡುಹಿಡಿಯುವುದು ಅಷ್ಟೇ ಮುಖ್ಯ.

ಈ ರೀತಿಯ ಫಲಿತಾಂಶಗಳನ್ನು ಸಾಧಿಸುವಲ್ಲಿ ಉತ್ತಮ ಭಾಗವೆಂದರೆ ಗಮನಿಸಬಹುದಾದ, ಗೋಚರಿಸುವ ವೇಗ ಸುಧಾರಣೆಯಾಗಿದೆ - ಅಲ್ಲಿ ಹಿಂದೆ ಲೋಡ್ ಆಗದಿರುವ ವರದಿಯು ಈಗ ಬಹುತೇಕ ತಕ್ಷಣವೇ ಲೋಡ್ ಆಗುತ್ತದೆ.

ವಿಶೇಷ ಧನ್ಯವಾದಗಳು ನನ್ನ ಒಡನಾಡಿಗಳು ಆದಿತ್ಯ ಮಿಶ್ರಾ ಅವರ ಆದೇಶದಂತೆ, ಆದಿತ್ಯ ಗೌರು и ವರುಣ್ ಮಲ್ಹೋತ್ರಾ ಬುದ್ದಿಮತ್ತೆಗಾಗಿ ಮತ್ತು ದಿನಕರ್ ಪಂಡಿರ್ ನಾವು ಅಂತಿಮವಾಗಿ ವಿದಾಯ ಹೇಳುವ ಮೊದಲು ನಮ್ಮ ಅಂತಿಮ ವಿನಂತಿಯಲ್ಲಿ ಪ್ರಮುಖ ದೋಷವನ್ನು ಕಂಡುಕೊಂಡಿದ್ದಕ್ಕಾಗಿ!

ಮೂಲ: www.habr.com