{"id":35259,"date":"2019-10-31T22:03:17","date_gmt":"2019-10-31T19:03:17","guid":{"rendered":"https:\/\/prohoster.info\/blog\/istoriya-odnogo-sql-rassledovaniya\/"},"modified":"2019-10-31T22:03:17","modified_gmt":"2019-10-31T19:03:17","slug":"istoriya-odnogo-sql-rassledovaniya","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/istoriya-odnogo-sql-rassledovaniya","title":{"rendered":"The story of an SQL investigation.","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Last December, I received an interesting bug report from the VWO support team. The loading time for one of the analytical reports for a major corporate client seemed excessively long. Since this is in my area of responsibility, I immediately focused on solving the problem.<\/p>\n

<\/p>\n

Background<\/h2>\n

<\/p>\n

To provide some context, let me briefly explain what VWO is. It is a platform that allows you to run various targeted campaigns on your websites: conduct A\/B experiments, track visitors and conversions, analyze sales funnels, display heatmaps, and replay visit recordings.<\/p>\n

<\/p>\n

But the most important feature of the platform is reporting. All the aforementioned functions are interconnected. For corporate clients, a vast array of information would be utterly useless without a powerful platform to represent it in an analytical format.<\/p>\n

<\/p>\n

Using the platform, you can make arbitrary queries on a large dataset. Here\u2019s a simple example:<\/p>\n

<\/p>\n

Show all clicks on the page \"abc.com\"\nFROM  TO \nfor people who\nused Chrome OR\n(were in Europe AND used iPhone)<\/pre>\n
<\/p>\n
Notice the boolean operators. They are available for clients in the query interface to create arbitrarily complex queries for obtaining datasets.<\/p>\n
<\/p>\n
Slow query<\/h2>\n
<\/p>\n
The client in question was trying to do something that intuitively should work quickly:<\/p>\n
<\/p>\n
Show all session recordings\nfor users who visited any page\nwith a URL containing \"\/jobs\"<\/pre>\n
<\/p>\n
This site had an enormous amount of traffic, and we stored over a million unique URLs just for it. They wanted to find a fairly simple URL pattern related to their business model.<\/p>\n
\n<\/a><\/noindex><\/p>\n
Preliminary Inquiry<\/h2>\n
<\/p>\n
Let's take a look at what is happening in the database. Below is the original slow SQL query:<\/p>\n
<\/p>\n
SELECT \n    count(*) \nFROM \n    acc_{account_id}.urls as recordings_urls, \n    acc_{account_id}.recording_data as recording_data, \n    acc_{account_id}.sessions as sessions \nWHERE \n    recording_data.usp_id = sessions.usp_id \n    AND sessions.referrer_id = recordings_urls.id \n    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com\/jobs%')::text[]   ) \n    AND r_time > to_timestamp(1542585600) \n    AND r_time = 5 \n    AND recording_data.num_of_pages > 0 ;<\/code><\/pre>\n
<\/p>\n
Here are the timings:<\/p>\n
<\/p>\n
Planned time: 1.480 ms\nExecution time: 1431924.650 ms<\/pre>\n
<\/p>\n
The query scanned 150 thousand rows. The query planner revealed a few interesting details but no obvious bottlenecks.<\/p>\n
<\/p>\n
Let's study the query further. As you can see, it makes JOIN<\/code> three tables:<\/p>\n
<\/p>\n
    \n
  1. sessions<\/strong>: to display session information: browser, user agent, country, and so on.<\/li>\n
  2. recording_data<\/strong>: recorded URLs, pages, duration of visits<\/li>\n
  3. urls<\/strong>: to avoid duplication of extremely large URLs, we store them in a separate table.<\/li>\n<\/ol>\n
    <\/p>\n
    Also, note that all our tables are already partitioned by account_id<\/code>. Thus, the situation where one particularly large account causes issues for others is excluded.<\/p>\n
    <\/p>\n
    In search of clues<\/h2>\n
    <\/p>\n
    Upon closer inspection, we see that something is wrong with this specific query. We should pay attention to this line:<\/p>\n
    <\/p>\n
    urls && array(\n\tselect id from acc_{account_id}.urls \n\twhere url ILIKE '%enterprise_customer.com\/jobs%'\n)::text[]<\/code><\/pre>\n
    <\/p>\n
    The first thought was that perhaps, due to ILIKE<\/code> on all these long URLs (we have more than 1.4 million unique\u00a0<\/strong>URLs collected for this account) the performance might suffer.<\/p>\n
    <\/p>\n
    But no \u2014 that's not the issue!<\/p>\n
    <\/p>\n
    SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com\/jobs%';\n  id\n--------\n ...\n(198661 rows)\n\nTime: 5231.765 ms<\/code><\/pre>\n
    <\/p>\n
    The search query by pattern only takes 5 seconds. Searching by pattern across a million unique URLs is clearly not the problem.<\/p>\n
    <\/p>\n
    The next suspect on the list \u2014 several JOIN<\/code>. Perhaps their excessive use led to the slowdown? Typically JOIN<\/code>'s are the most obvious candidates for performance issues, but I didn't believe our case was typical.<\/p>\n
    <\/p>\n
    analytics_db=# SELECT\n    count(*)\nFROM\n    acc_{account_id}.urls as recordings_urls,\n    acc_{account_id}.recording_data_0 as recording_data,\n    acc_{account_id}.sessions_0 as sessions\nWHERE\n    recording_data.usp_id = sessions.usp_id\n    AND sessions.referrer_id = recordings_urls.id\n    AND r_time > to_timestamp(1542585600)\n    AND r_time = 5\n    AND recording_data.num_of_pages > 0 ;\n count\n-------\n  8086\n(1 row)\n\nTime: 147.851 ms<\/code><\/pre>\n
    <\/p>\n
    And this was not our case either. JOIN<\/code>'s turned out to be quite fast.<\/p>\n
    <\/p>\n
    Narrowing down the suspects<\/h2>\n
    <\/p>\n
    I was ready to start modifying the query in search of any possible performance improvements. My team and I developed two main ideas:<\/p>\n
    <\/p>\n