Sql1 as follows, used in the subquery, and the use of inner joins the explain of the same, the query takes about the same, also for several minutes:
# explain the SELECT * FROM dialog_end WHERE (dialog_end channel_id in (4444)) AND (dialog_end. Unit_id=2858) AND (dialog_end. Create_time & gt;=1540310400 AND dialog_end. Create_time & lt; 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. The token HAVING (sum (CASE WHEN customer_history_url. Url_type=3 AND customer_history_url. Url LIKE '% sq521 %' THEN one ELSE 0 END) & gt; 0));
The QUERY PLAN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Nested Loop (cost=163388.42.. 216185.93 rows=1 width=220)
Join the 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 & gt;=1540310400) AND (create_time & lt; 1540396799) AND (2858) unit_id=)
The Filter: (channel_id=4444)
-> Bitmap Index Scan on index_dialog_end_createtime_unitid_id_token (cost=0.00.. 4710.20 rows=3669 width=0)
The Index Cond: ((create_time & gt;=1540310400) AND (create_time & lt; 1540396799) AND (2858) unit_id=)
- & gt; 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. ~ ~ '% % sq521 url:
: text), THEN one ELSE 0 END) & gt; 0)
- & gt; Gather the Merge (cost=158678.22.. 192260.40 rows=254568 width=23)
Workers Planned: 2
- & gt; Partial GroupAggregate (cost=157678.20.. 161876.91 rows=127284 width=23)
Group Key: customer_history_url token
- & gt; Sort (cost=157678.20.. 158263.37 rows=234070 width=229)
The Sort Key: customer_history_url token
- & gt; The Parallel Seq Scan on customer_history_url (cost=0.00.. 111200.66 rows=234070 widt
H=229)
The Filter: (unit_id=2858)
Rows (19)
Sql2 as follows, using the left join, about two seconds to return to:
# explain the SELECT * FROM (SELECT DISTINCT (customer_history_url. Token) FROM customer_history_url WHERE customer_history_url. Unit_id=2858 GROUP BY customer_history_url. The token HAVING (sum (CASE WHEN customer_history_url. Url_type=3 AND customer_history_url. Url LIKE '% sq521 %' THEN one ELSE 0 END) & gt; 0)) AS the 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 & gt;=1540310400 AND dialog_end. Create_time & lt; 1540396799)) as DLG ON cFN. Token=DLG. Token;
The QUERY PLAN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Nested Loop Left Join (cost=163390.99.. 216503.24 rows=127284 width=235)
Join the Filter: (customer_history_url. Token=dialog_end. Token)
- & gt; Unique (cost=158680.93.. 195127.06 rows=127284 width=15)
- & gt; 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. ~ ~ '% % sq521 url:
: text), THEN one ELSE 0 END) & gt; 0)
- & gt; Gather the Merge (cost=158680.93.. 192263.17 rows=254568 width=23)
Workers Planned: 2
- & gt; Partial GroupAggregate (cost=157680.91.. 161879.69 rows=127284 width=23)
Group Key: customer_history_url token
- & gt; Sort (cost=157680.91.. 158266.10 rows=234075 width=229)
The Sort Key: customer_history_url token
- & gt; The Parallel Seq Scan on customer_history_url (cost=0.00.. 111202.89 rows=234075 widt
H=229)
The Filter: (unit_id=2858)
- & gt; Materialize (cost=4710.06.. 18194.08 rows=1 width=220)
- & gt; Bitmap Heap Scan on dialog_end (cost=4710.06.. 18194.07 rows=1 width=220)
Recheck Cond: ((create_time & gt;=1540310400) AND (create_time & lt; 1540396799) AND (2858) unit_id=)
The Filter: (channel_id=4444)
- & gt; Bitmap Index Scan on index_dialog_end_createtime_unitid_id_token (cost=0.00.. 4710.06 rows=3668 wid
Th=0)
The Index Cond: ((create_time & gt;=1540310400) AND (create_time & lt; 1540396799) AND (2858) unit_id=)
(20 rows)
Amount of data is as follows:
# SELECT count (*) FROM dialog_end WHERE (dialog_end. Channel_id in (4444)) AND (dialog_end. Unit_id=2858) AND (dialog_end. Create_time & gt;=1540310400 AND dialog_end. Create_time & lt; 1540396799);
Count
-- -- -- -- -- -- --
721
(row 1)
# select count (*) from dialog_end;
Count
-- -- -- -- -- -- -- -- --
9102021
(row 1)
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull