954
社区成员
发帖
与我相关
我的任务
分享
# explain SELECT * FROM dialog_end WHERE (dialog_end.channel_id in (4444)) AND (dialog_end.unit_id = 2858) AND (dialog_end.create_time >= 1540310400 AND dialog_end.create_time < 1540396799) AND token in (SELECT DISTINCT(customer_history_url.token) FROM customer_history_url WHERE customer_history_url.unit_id=2858 GROUP BY customer_history_url.token HAVING (sum(CASE WHEN customer_history_url.url_type=3 AND customer_history_url.url LIKE '%sq521%' THEN 1 ELSE 0 END) > 0));
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
--------------------------------
Nested Loop (cost=163388.42..216185.93 rows=1 width=220)
Join Filter: (dialog_end.token = customer_history_url.token)
-> Bitmap Heap Scan on dialog_end (cost=4710.20..18197.75 rows=1 width=220)
Recheck Cond: ((create_time >= 1540310400) AND (create_time < 1540396799) AND (unit_id = 2858))
Filter: (channel_id = 4444)
-> Bitmap Index Scan on index_dialog_end_createtime_unitid_id_token (cost=0.00..4710.20 rows=3669 width=0)
Index Cond: ((create_time >= 1540310400) AND (create_time < 1540396799) AND (unit_id = 2858))
-> Unique (cost=158678.22..195124.29 rows=127284 width=15)
-> Finalize GroupAggregate (cost=158678.22..194806.08 rows=127284 width=15)
Group Key: customer_history_url.token
Filter: (sum(CASE WHEN ((customer_history_url.url_type = 3) AND (customer_history_url.url ~~ '%sq521%':
:text)) THEN 1 ELSE 0 END) > 0)
-> Gather Merge (cost=158678.22..192260.40 rows=254568 width=23)
Workers Planned: 2
-> Partial GroupAggregate (cost=157678.20..161876.91 rows=127284 width=23)
Group Key: customer_history_url.token
-> Sort (cost=157678.20..158263.37 rows=234070 width=229)
Sort Key: customer_history_url.token
-> Parallel Seq Scan on customer_history_url (cost=0.00..111200.66 rows=234070 widt
h=229)
Filter: (unit_id = 2858)
(19 rows)
# explain SELECT * FROM (SELECT DISTINCT(customer_history_url.token) FROM customer_history_url WHERE customer_history_url.unit_id=2858 GROUP BY customer_history_url.token HAVING (sum(CASE WHEN customer_history_url.url_type=3 AND customer_history_url.url LIKE '%sq521%' THEN 1 ELSE 0 END) > 0)) AS cFN LEFT JOIN (select dialog_end.* from dialog_end WHERE (dialog_end.channel_id in (4444)) AND (dialog_end.unit_id = 2858) AND (dialog_end.create_time >= 1540310400 AND dialog_end.create_time < 1540396799) ) as dlg ON cFN.token=dlg.token;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
--------------------------------
Nested Loop Left Join (cost=163390.99..216503.24 rows=127284 width=235)
Join Filter: (customer_history_url.token = dialog_end.token)
-> Unique (cost=158680.93..195127.06 rows=127284 width=15)
-> Finalize GroupAggregate (cost=158680.93..194808.85 rows=127284 width=15)
Group Key: customer_history_url.token
Filter: (sum(CASE WHEN ((customer_history_url.url_type = 3) AND (customer_history_url.url ~~ '%sq521%':
:text)) THEN 1 ELSE 0 END) > 0)
-> Gather Merge (cost=158680.93..192263.17 rows=254568 width=23)
Workers Planned: 2
-> Partial GroupAggregate (cost=157680.91..161879.69 rows=127284 width=23)
Group Key: customer_history_url.token
-> Sort (cost=157680.91..158266.10 rows=234075 width=229)
Sort Key: customer_history_url.token
-> Parallel Seq Scan on customer_history_url (cost=0.00..111202.89 rows=234075 widt
h=229)
Filter: (unit_id = 2858)
-> Materialize (cost=4710.06..18194.08 rows=1 width=220)
-> Bitmap Heap Scan on dialog_end (cost=4710.06..18194.07 rows=1 width=220)
Recheck Cond: ((create_time >= 1540310400) AND (create_time < 1540396799) AND (unit_id = 2858))
Filter: (channel_id = 4444)
-> Bitmap Index Scan on index_dialog_end_createtime_unitid_id_token (cost=0.00..4710.06 rows=3668 wid
th=0)
Index Cond: ((create_time >= 1540310400) AND (create_time < 1540396799) AND (unit_id = 2858))
(20 rows)
# SELECT count(*) FROM dialog_end WHERE (dialog_end.channel_id in (4444)) AND (dialog_end.unit_id = 2858) AND (dialog_end.create_time >= 1540310400 AND dialog_end.create_time < 1540396799);
count
-------
721
(1 row)
# select count(*) from dialog_end;
count
---------
9102021
(1 row)
# SELECT count(*) FROM customer_history_url WHERE customer_history_url.unit_id=2858 and customer_history_url.url_type=3 AND customer_history_url.url LIKE '%sq521%';
count
-------
286
(1 row)
# select count(*) from customer_history_url;
count
---------
2147435
(1 row)