ืืขืฆืืข ืืขืฆืขืืืขืจ ืืื ืืืงืืืขื ืึท ืืฉืืงืึทืืืข ืืฉืืง ืืึทืจืืื ืคืื ืื VWO ืฉืืืฆื ืืึทื ืฉืึทืคึฟื. ืื ืืึธืืืื ื ืฆืืื ืคึฟืึทืจ ืืืื ืขืจ ืคืื ืื ืึทื ืึทืืืืืงืก ืจืืคึผืึธืจืฅ ืคึฟืึทืจ ืึท ืืจืืืก ืคึฟืืจืืข ืงืืืขื ื ืืื ืืขืืืขื ืคึผืจืึธืืืืืึทืืืื. ืืื ืืื ื ืืึธืก ืืื ืืืื ืืขืื ื ืคืื ืคึฟืึทืจืึทื ืืืืึธืจืืืขืืงืืื, ืืื ืืื ืคืึธืืงืืกื ืืืืฃ ืกืึทืืืืื ื ืื ืคึผืจืึธืืืขื.
ืคึผืจืขืืืกืืึธืจื
ืฆื ืืึทืื ืขืก ืงืืึธืจ ืืืึธืก ืืื ืืื ืืขืจืขืื ืืืขืื, ืืื ืืืขื ืืึธืื ืืืจ ืึท ืืืกื ืืืขืื ืืืืืึธ. ืืึธืก ืืื ืึท ืคึผืืึทืืคืึธืจืืข ืืื ืืืึธืก ืืืจ ืงืขื ืขื ืงืึทืืขืจ ืคืึทืจืฉืืื ืืึทืจืืขืืขื ืงืึทืืคึผืืื ื ืืืืฃ ืืืื ืืืขืืกืืืขืก: ืึธื ืคืืจื ืึท / ื ืืงืกืคึผืขืจืึทืืึทื ืฅ, ืฉืคึผืืจ ืืืืืึทืืขืจื ืืื ืงืึทื ืืืขืจืืฉืึทื ื, ืึทื ืึทืืืื ืื ืคืืจืงืืืคืื ื ืืืืงืข, ืึทืจืืืกืืืืึทืื ืืืฅ ืืึทืคึผืก ืืื ืฉืคึผืื ืืืืืื ืจืขืงืึธืจืืื ืืก.
ืึธืืขืจ ืื ืืขืจืกื ืืืืืืืง ืืึทื ืืืขืื ืืขืจ ืคึผืืึทืืคืึธืจืืข ืืื ืจืืคึผืึธืจืืื ื. ืึทืืข ืคืื โโืื ืืืืื ืคืึทื ืืงืฉืึทื ื ืืขื ืขื ืื ืืขืจืงืึทื ืขืงืืื. ืืื ืคึฟืึทืจ ืคึฟืืจืืข ืงืืืืึทื ืฅ, ืึท ืจืืืืง ืกืืืข ืคืื โโืืื ืคึฟืึธืจืืึทืฆืืข ืืืึธืื ืืืื ืคืฉืื ืึทืจืืืกืืขืืืึธืจืคื ืึธื ืึท ืฉืืึทืจืง ืคึผืืึทืืคืึธืจืืข ืืืึธืก ืืื ืขืก ืืื ืึทื ืึทืืืืืงืก ืคืึธืจืขื.
ื ืืฆื ืื ืคึผืืึทืืคืึธืจืืข, ืืืจ ืงืขื ืขื ืืึทืื ืึท ืืจืึทืค ืึธื ืคึฟืจืขื ืืืืฃ ืึท ืืจืืืก ืืึทืื ืฉืืขืื. ืืึธ ืก ืึท ืคึผืฉืื ืืืึทืฉืคึผืื:
ืืืืึทืื ืึทืืข ืงืืืงืก ืืืืฃ ืืืึทื "abc.com" ืคึฟืื <ืืึทืืข ื1> ืฆื <ืืึทืืข ื2> ืคึฟืึทืจ ืืขื ืืฉื ืืืึธืก ืืึธืื ืืขืืืืื ื ืงืจืึธืื ืึธืืขืจ (ืืืื ืืื ืืืืจืึธืคึผืข ืืื ืืขืืืืื ื ืึท iPhone)
ืืึทืฆืึธืื ืืคืืขืจืงืืึทืืงืืึทื ืฆื ืืืืืึทื ืึธืคึผืขืจืืืืขืจื. ืืื ืืขื ืขื ืืืจืขืืืืื ืคึฟืึทืจ ืงืืืืึทื ืฅ ืืื ืื ืึธื ืคึฟืจืขื ืฆืืืื ื ืฆื ืืึทืื ืึทืจืืืืจืขืจืึทืื ืงืึธืืคึผืืืฆืืจื ืงืืืืจืื ืฆื ืืึทืงืืืขื ืกืึทืืคึผืึทืื.
ืคึผืึทืืขืืขื ืืขืื
ืืขืจ ืงืืืขื ื ืืื ืงืฉืื ืืื ืืจืืื ื ืฆื ืืึธื ืขืคึผืขืก ืืืึธืก ืื ืืืึทืืืืืื ืืึธื ืึทืจืืขืื ืืขืฉืืืื ื:
ืืืืึทืื ืึทืืข ืกืขืกืืข ืจืขืงืึธืจืืก ืคึฟืึทืจ ืืืืขืจื ืืืึธืก ืืึธืื ืืืืืื ืงืืื ืืืึทื ืืื ืึท 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 ืืื ืืืจืืคืืจืื ื ืฆืืื: 1431924.650 ืืื
ืื ืึธื ืคึฟืจืขื ืงืจืึธืื 150 ืืืืื ื ืจืึธืื. ืื ืึธื ืคึฟืจืขื ืคึผืืึทื ื ืขืจ ืืขืืืืื ืึท ืคึผืึธืจ ืคืื ืืฉืืงืึทืืืข ืืขืืึทืืืก, ืึธืืขืจ ืงืืื ืงืืึธืจ ืืื ืืขืจ ืืึธื ืืึทืืึทืื ืขืงืก.
ืืืืืจ ืืืืืืขืจ ืืขืจื ืขื ืื ืืงืฉื. ืืื ืืืจ ืงืขื ืขื ืืขื, ืขืจ ืืื JOIN ืืจืื ืืืฉื:
- ืกืขืฉืึทื ื: ืฆื ืืืืึทืื ืกืขืกืืข ืืื ืคึฟืึธืจืืึทืฆืืข: ืืืขืืขืจืขืจ, โโืืึทื ืืฆืขืจ ืึทืืขื ื, ืืึทื ื, ืืื ืึทืืื ืืืืฃ.
- ืจืขืงืึธืจืืื ื_ืืึทืื: ืจืขืงืึธืจืืขื URL ืก, ืืืขืืขืจ, ืืขืืืืขืจ ืคืื ืืืืืืฅ
- 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 ืืืืคึผื ืืขืืื ืงืขื:
- ื ืืฆื EXISTS ืคึฟืึทืจ ืกืืืงืืืขืจื URL: ืืืจ ืืขืืืืื ืฆื ืงืึธื ืืจืึธืืืจื ืืืืืขืจ ืืืื ืขืก ืืขื ืขื ืงืืื ืคืจืืืืขืืขื ืืื ืื ืกืึทืืงืืืขืจื ืคึฟืึทืจ ืื 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 ืก ืืื ืคึผืจืึธืกื ืึทืจืืืงืืขื. ืืึธืก ืืื ืึท ืืืกื ืงืึทื ืคืืืืื ื ืืืืึทื "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 ืจืขืงืึธืจืืก ืืืืึทื, ืืืืึทื ืืึธืก ืืขื ืขื ืืืืื IDs ืืืึธืก ืจืืคืขืจืื ื ืฆื urls.
ืืืืฃ ืื ืืืขื ืฆื ืึท ืืืืืื ื
&& ืคึผืึทืืขืืขื ืืืืึทื ืืืืืข ืฉืืขืื ืืขื ืขื ืจืืืืง. ืื ืึธืคึผืขืจืึทืฆืืข ืืืขื ืืืื ืืขืคืืขืจืขื ืฉื ืขื ืืืื ืืื ืคืึทืจืืืึทืื urls ืืืืฃ { "http://google.com/", "http://wingify.com/" }.
ืืื ืกืืึทืจืืขื ืงืืงื ืคึฟืึทืจ ืึท ืืืขื ืฆื ืฉืืขืื ืื ืืขืจืกืขืงืฉืึทื ืืื Postgres ืึธื ื ืืฆื &&, ืึธืืขืจ ืึธื ืคืื ืืฆืืื.
ืืื ืื ืกืืฃ, ืืืจ ืืึทืฉืืึธืกื ืฆื ื ืึธืจ ืกืึธืืืืข ืื ืคึผืจืึธืืืขื ืืื ืืคืืขืืื ืืขืจืืงืืื: ืืขืื ืืืจ ืึทืืฅ 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(*) ืืืืฃ ืืืืืข ืคึผืึทืืขืืขื ืืื ืฉื ืขื ืงืืืืจืื ืคึฟืึทืจ ืึท ืืจืืืก ื ืืืขืจ ืคืื ืคืึทืจืฉืืืขื ืข ืืึทืื ืฉืืขืื. ืืขืจื ืึธื, ืคึฟืึทืจ ืึท ืงืืืื ืกืึทืืกืขื ืคืื ืื ืืึทืื, ืืื ืืึทื ืืืึทืื ืืืขืจืึทืคืืื ืึทื ืึทืืข ืจืขืืืืืึทืื ืืขื ืขื ืจืืืืืง.
ืึทืืข ืืขืกืฅ ืืึธืื ืงืึทื ืกืืกืืึทื ืืื positive ืจืขืืืืืึทืื. ืืืจ ืคืึทืจืคืขืกืืืงื ืึทืืฅ!
ืืขืงืฆืืขืก ืืขืืขืจื ื
ืขืก ืืขื ืขื ืคืืืข ืืขืงืฆืืขืก ืฆื ืืขืจื ืขื ืคืื ืืขื ืืขืฉืืืืข:
- ืึธื ืคึฟืจืขื ืคึผืืึทื ื ืืึธื ื ืื ืืึธืื ืื ืืื ืฆืข ืืขืฉืืืืข, ืึธืืขืจ ืืื ืงืขื ืขื ืฆืืฉืืขืื ืงืืื
- ืื ืืืืคึผื ืกืึทืกืคึผืขืงืฅ ืืขื ืขื ื ืืฉื ืฉืืขื ืืืง ืื ืคืึทืงืืืฉ ืงืืืคึผืจืืฅ
- ืคึผืึทืืขืืขื ืคึฟืจืืื ืงืขื ืขื ืืืื ืฆืขืืจืืื ืึทืจืึธืคึผ ืฆื ืืืึธืืืจื ืืึทืืึทืื ืขืงืก
- ื ืื ืึทืืข ืึธืคึผืืืืืืึทืืืึธื ืก ืืขื ืขื ืจืขืืืงืืืืืข ืืื ื ืึทืืืจ
- ื ืืฆื
EXIST, ืืื ืืขืืืขื, ืงืขื ืขื ืคืืจื ืฆื ืืจืึทืืึทืืืง ืื ืงืจืืกืึทื ืืื ืคึผืจืึธืืืึทืงืืืืืึทืื
ืจืขืืืืืึทื
ืืืจ ืืขื ืขื ืคึฟืื ืึท ืึธื ืคึฟืจืขื ืฆืืื ืคืื ~ 24 ืืื ืื ืฆื 2 ืกืขืงืื ืืขืก - ืืึทื ืฅ ืึท ืืึทืืืืืืง ืคืึธืจืฉืืขืืื ื ืคืึทืจืืจืขืกืขืจื! ืืึธืืฉ ืืขืจ ืึทืจืืืงื ืืื ืืขืืืขื ืืจืืืก, ืึทืืข ืื ืืงืกืคึผืขืจืึทืืึทื ืฅ ืืืึธืก ืืืจ ืืึธืื ืืืจืืืขืงืึธืื ืืื ืืืื ืืึธื, ืืื ืขืก ืืื ืืขืืืขื ืขืกืืืืึทืืขื ืึทื ืืื ืืขื ืืืขื ืฆืืืืฉื 1,5 ืืื 2 ืฉืขื ืคึฟืึทืจ ืึธืคึผืืืืืืึทืืืึธื ืก ืืื ืืขืกืืื ื.
SQL ืืื ืึท ืืืื ืืขืจืืขื ืฉืคึผืจืึทื ืืืื ืืืจ ืืึธื ื ืื ืืืื ืืขืจืฉืจืึธืงื ืคืื ืขืก, ืึธืืขืจ ืคึผืจืืืืจื ืฆื ืืขืจื ืขื ืืื ื ืืฆื ืขืก. ืืื ืึท ืืืื ืคืืจืฉืืื ื ืคืื ืืื SQL ืงืืืืจืื ืืขื ืขื ืขืงืกืึทืงืืืืึทื, ืืื ืื ืืึทืืึทืืืืก ืืืฉืขื ืขืจืืืฅ ืึธื ืคึฟืจืขื ืคึผืืึทื ื, ืืื ืื ืืขืงืกืื ืึทืจืืขื, ืืื ืคืฉืื ืื ืืจืืืก ืคืื ืื ืืึทืื ืืืจ ืืึทื ืืืขื ืืื, ืืืจ ืงืขื ืขื ืืืื ืืืืขืจ ืืขืจืึธืื ืืื ืึธืคึผืืืืืืื ื ืงืืืืจืื. ืขืก ืืื ืืืืึทื ืืืืืืืง, ืึธืืขืจ, ืฆื ืคืึธืจืืขืฆื ืฆื ืคึผืจืืืืจื ืคืึทืจืฉืืืขื ืข ืึทืคึผืจืึธืืืฉืื ืืื ืกืืึธืืื ืืจืขืื ืึทืจืึธืคึผ ืื ืคึผืจืึธืืืขื, ืืขืคึฟืื ืขื ืื ืืึทืืึทืื ืขืงืก.
ืืขืจ ืืขืกืืขืจ ืืืื ืืืขืื ืืขืจืืจืืืื ืจืขืืืืืึทืื ืืื ืื ืืื ืื ืืืืขืจืงื, ืงืขื ืืืง ืืืืงืืึทื ืคึฟืึทืจืืขืกืขืจืื ื - ืืื ืึท ืืึทืจืืื ืืืึธืก ืืื ืึทืืขืจ ืืืึธืื ื ืืฉื ืืคืืื ืืึธืื ืืืฆื ืืึธืืื ืึผืืขื ืืืืงืขืฃ.
ืกืคึผืขืฆืืขืืข ืืึทื ืง ืฆื ืืืื ืข ืืืจืื ืึฐึผืคึดืงึฐืึทืช ืึฒืึดืืชึธื ืึดืฉึธึผืืจึธื, Aditya Gauru ะธ ืคึฟืึทืจ ืืจืืื ืกืืึธืจืืื ื ืืื ืืื ืงืึทืจ ืคึผืึทื ืืืจ ืคึฟืึทืจ ืืขืจืืืืื ื ืึท ืืืืืืืง ืืขืืช ืืื ืืื ืืืขืจ ืืขืฆื ืืขืื ืืืืืขืจ ืืืจ ืืขืกืึธืฃ ืืขืืืื ืืืึท ืืขืืื ื ืฆื ืืื!
ืืงืืจ: www.habr.com