ਇੱਕ SQL ਜਾਂਚ ਦੀ ਕਹਾਣੀ

ਪਿਛਲੇ ਦਸੰਬਰ ਵਿੱਚ ਮੈਨੂੰ VWO ਸਹਾਇਤਾ ਟੀਮ ਤੋਂ ਇੱਕ ਦਿਲਚਸਪ ਬੱਗ ਰਿਪੋਰਟ ਮਿਲੀ। ਇੱਕ ਵੱਡੇ ਕਾਰਪੋਰੇਟ ਕਲਾਇੰਟ ਲਈ ਵਿਸ਼ਲੇਸ਼ਣ ਰਿਪੋਰਟਾਂ ਵਿੱਚੋਂ ਇੱਕ ਲਈ ਲੋਡ ਕਰਨ ਦਾ ਸਮਾਂ ਪ੍ਰਤੀਬੰਧਿਤ ਲੱਗ ਰਿਹਾ ਸੀ। ਅਤੇ ਕਿਉਂਕਿ ਇਹ ਮੇਰੀ ਜ਼ਿੰਮੇਵਾਰੀ ਦਾ ਖੇਤਰ ਹੈ, ਮੈਂ ਤੁਰੰਤ ਸਮੱਸਿਆ ਨੂੰ ਹੱਲ ਕਰਨ 'ਤੇ ਧਿਆਨ ਕੇਂਦਰਿਤ ਕੀਤਾ।

prehistory

ਇਹ ਸਪੱਸ਼ਟ ਕਰਨ ਲਈ ਕਿ ਮੈਂ ਕਿਸ ਬਾਰੇ ਗੱਲ ਕਰ ਰਿਹਾ ਹਾਂ, ਮੈਂ ਤੁਹਾਨੂੰ VWO ਬਾਰੇ ਥੋੜਾ ਜਿਹਾ ਦੱਸਾਂਗਾ. ਇਹ ਇੱਕ ਪਲੇਟਫਾਰਮ ਹੈ ਜਿਸ ਨਾਲ ਤੁਸੀਂ ਆਪਣੀਆਂ ਵੈੱਬਸਾਈਟਾਂ 'ਤੇ ਵੱਖ-ਵੱਖ ਨਿਸ਼ਾਨਾ ਮੁਹਿੰਮਾਂ ਨੂੰ ਲਾਂਚ ਕਰ ਸਕਦੇ ਹੋ: A/B ਪ੍ਰਯੋਗਾਂ ਦਾ ਆਯੋਜਨ ਕਰੋ, ਵਿਜ਼ਟਰਾਂ ਅਤੇ ਪਰਿਵਰਤਨਾਂ ਨੂੰ ਟਰੈਕ ਕਰੋ, ਵਿਕਰੀ ਫਨਲ ਦਾ ਵਿਸ਼ਲੇਸ਼ਣ ਕਰੋ, ਗਰਮੀ ਦੇ ਨਕਸ਼ੇ ਪ੍ਰਦਰਸ਼ਿਤ ਕਰੋ ਅਤੇ ਵਿਜ਼ਿਟ ਰਿਕਾਰਡਿੰਗਾਂ ਚਲਾਓ।

ਪਰ ਪਲੇਟਫਾਰਮ ਬਾਰੇ ਸਭ ਤੋਂ ਮਹੱਤਵਪੂਰਨ ਚੀਜ਼ ਰਿਪੋਰਟਿੰਗ ਹੈ. ਉਪਰੋਕਤ ਸਾਰੇ ਫੰਕਸ਼ਨ ਆਪਸ ਵਿੱਚ ਜੁੜੇ ਹੋਏ ਹਨ। ਅਤੇ ਕਾਰਪੋਰੇਟ ਗਾਹਕਾਂ ਲਈ, ਜਾਣਕਾਰੀ ਦੀ ਇੱਕ ਵੱਡੀ ਮਾਤਰਾ ਇੱਕ ਸ਼ਕਤੀਸ਼ਾਲੀ ਪਲੇਟਫਾਰਮ ਤੋਂ ਬਿਨਾਂ ਬੇਕਾਰ ਹੋਵੇਗੀ ਜੋ ਇਸਨੂੰ ਵਿਸ਼ਲੇਸ਼ਣ ਦੇ ਰੂਪ ਵਿੱਚ ਪੇਸ਼ ਕਰਦਾ ਹੈ.

ਪਲੇਟਫਾਰਮ ਦੀ ਵਰਤੋਂ ਕਰਦੇ ਹੋਏ, ਤੁਸੀਂ ਇੱਕ ਵੱਡੇ ਡੇਟਾ ਸੈੱਟ 'ਤੇ ਇੱਕ ਬੇਤਰਤੀਬ ਪੁੱਛਗਿੱਛ ਕਰ ਸਕਦੇ ਹੋ। ਇੱਥੇ ਇੱਕ ਸਧਾਰਨ ਉਦਾਹਰਨ ਹੈ:

ਉਹਨਾਂ ਲੋਕਾਂ ਲਈ <date d1> ਤੋਂ <date d2> ਤੱਕ ਪੰਨੇ "abc.com" 'ਤੇ ਸਾਰੇ ਕਲਿੱਕ ਦਿਖਾਓ ਜੋ Chrome ਦੀ ਵਰਤੋਂ ਕਰਦੇ ਹਨ ਜਾਂ (ਯੂਰਪ ਵਿੱਚ ਸਥਿਤ ਅਤੇ ਇੱਕ ਆਈਫੋਨ ਦੀ ਵਰਤੋਂ ਕਰਦੇ ਹਨ)

ਬੁਲੀਅਨ ਓਪਰੇਟਰਾਂ ਵੱਲ ਧਿਆਨ ਦਿਓ। ਉਹ ਨਮੂਨੇ ਪ੍ਰਾਪਤ ਕਰਨ ਲਈ ਆਪਹੁਦਰੇ ਤੌਰ 'ਤੇ ਗੁੰਝਲਦਾਰ ਪੁੱਛਗਿੱਛ ਕਰਨ ਲਈ ਪੁੱਛਗਿੱਛ ਇੰਟਰਫੇਸ ਵਿੱਚ ਗਾਹਕਾਂ ਲਈ ਉਪਲਬਧ ਹਨ।

ਹੌਲੀ ਬੇਨਤੀ

ਪ੍ਰਸ਼ਨ ਵਿੱਚ ਗਾਹਕ ਕੁਝ ਅਜਿਹਾ ਕਰਨ ਦੀ ਕੋਸ਼ਿਸ਼ ਕਰ ਰਿਹਾ ਸੀ ਜੋ ਅਨੁਭਵੀ ਤੌਰ 'ਤੇ ਤੇਜ਼ੀ ਨਾਲ ਕੰਮ ਕਰਨਾ ਚਾਹੀਦਾ ਹੈ:

ਉਹਨਾਂ ਉਪਭੋਗਤਾਵਾਂ ਲਈ ਸਾਰੇ ਸੈਸ਼ਨ ਰਿਕਾਰਡ ਦਿਖਾਓ ਜੋ "/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. urls: ਬਹੁਤ ਵੱਡੇ 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-ami ਅਤੇ ਸਬਕਵੇਰੀ ਖੁਦ ਵੱਖਰੇ ਤੌਰ 'ਤੇ ਤੇਜ਼ ਹਨ, ਪਰ ਇਕੱਠੇ ਬਹੁਤ ਹੌਲੀ ਹਨ?

• ਸਬਕਵੇਰੀ ਨੂੰ 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[]   )

ਜਿਸਦਾ ਮਤਲਬ ਹੈ ਕਿ ਅਸੀਂ ਆਪਣੇ URLs 'ਤੇ ਇੱਕ ਪੈਟਰਨ ਖੋਜ ਕਰਦੇ ਹਾਂ, ਫਿਰ ਆਮ ਪੋਸਟਾਂ ਵਾਲੇ ਸਾਰੇ URL ਦੇ ਨਾਲ ਇੰਟਰਸੈਕਸ਼ਨ ਲੱਭਦੇ ਹਾਂ। ਇਹ ਥੋੜਾ ਉਲਝਣ ਵਾਲਾ ਹੈ ਕਿਉਂਕਿ ਇੱਥੇ "urls" ਸਾਰੇ URL ਵਾਲੀ ਸਾਰਣੀ ਦਾ ਹਵਾਲਾ ਨਹੀਂ ਦਿੰਦਾ ਹੈ, ਪਰ ਸਾਰਣੀ ਵਿੱਚ "urls" ਕਾਲਮ ਦਾ ਹਵਾਲਾ ਦਿੰਦਾ ਹੈ 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-s ਤੇਜ਼ ਹਨ ਅਤੇ ਸਬਕਵੇਰੀਆਂ ਤੇਜ਼ ਹਨ, ਸਿਰਫ ਇੱਕ ਚੀਜ਼ ਬਾਕੀ ਸੀ && ਆਪਰੇਟਰ

ਇਹ ਸਿਰਫ਼ ਇੱਕ ਮੁੱਖ ਕਾਰਵਾਈ ਹੈ। ਸਾਨੂੰ ਹਮੇਸ਼ਾ ਇੱਕ ਪੈਟਰਨ ਦੀ ਖੋਜ ਕਰਨ ਲਈ URL ਦੀ ਪੂਰੀ ਅੰਡਰਲਾਈੰਗ ਸਾਰਣੀ ਦੀ ਖੋਜ ਕਰਨ ਦੀ ਲੋੜ ਹੁੰਦੀ ਹੈ, ਅਤੇ ਸਾਨੂੰ ਹਮੇਸ਼ਾ ਇੰਟਰਸੈਕਸ਼ਨ ਲੱਭਣ ਦੀ ਲੋੜ ਹੁੰਦੀ ਹੈ। ਅਸੀਂ URL ਰਿਕਾਰਡਾਂ ਦੁਆਰਾ ਸਿੱਧੇ ਤੌਰ 'ਤੇ ਖੋਜ ਨਹੀਂ ਕਰ ਸਕਦੇ, ਕਿਉਂਕਿ ਇਹ ਸਿਰਫ਼ ਆਈ.ਡੀ 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