Query became very slow after 9.6 -> 10 upgrade

Hi!

I've seen few letters like this on mailing list and for some reason thought
that probably it won't happen to us, but here I am lol.

It's "nestloop hits again" situation.

I'll try to provide plan from 9.6 later, but right now I have only plan
from 10.1.

Query: https://pastebin.com/9b953tT7
It was running under 3 seconds (it's our default timeout) and now it runs
for 12 minutes.

\d adroom: https://pastebin.com/vBrPGtxT (3800 rows)
\d adroom_stat: https://pastebin.com/CkBArCC9 (47mln rows, 1.5mln satisfy
condition on day column)
\d domains: https://pastebin.com/65hk7YCm (73000 rows)

All three tables are analyzed.

EXPLAIN ANALYZE: https://pastebin.com/PenHEgf0
EXPLAIN ANALYZE with nestloop off: https://pastebin.com/zX35CPCV (0.8s)

Regarding server parameters - it's a mighty beast with 2x E5-2630 v3, 192Gb
of RAM and two very, very fast NVME server class SSD's in RAID1.

What can I do with it?

Also maybe this will be useful:

1st query, runs under 1ms
select title, id, groups->0->>'provider' provider, domain_ids from adroom
where groups->0->>'provider' ~ '^target_mail_ru' and not is_paused and
current_timestamp between start_ts and stop_ts

2nd query that uses 1st one, runs under 3 ms
select distinct unnest(domain_ids) FROM (select title, id,
groups->0->>'provider' provider, domain_ids from adroom where
groups->0->>'provider' ~ '^target_mail_ru' and not is_paused and
current_timestamp between start_ts and stop_ts) t1

3rd query which returns 1.5mln rows, runs in about 0.6s
SELECT adroom_id, domain_id, shows, clicks FROM adroom_stat WHERE day
between date_trunc('day', current_timestamp - interval '1 week') and
date_trunc('day', current_timestamp)

BUT if I'll add to 3rd query one additional condition, which is basically
2nd query, it will ran same 12 minutes:
SELECT adroom_id, domain_id, shows, clicks FROM adroom_stat WHERE day
between date_trunc('day', current_timestamp - interval '1 week') and
date_trunc('day', current_timestamp) AND domain_id IN (select distinct
unnest(domain_ids) FROM (select title, id, groups->0->>'provider' provider,
domain_ids from adroom where groups->0->>'provider' ~ '^target_mail_ru' and
not is_paused and current_timestamp between start_ts and stop_ts) t1)

Plan of last query:
Nested Loop (cost=88.63..25617.31 rows=491 width=16) (actual
time=3.512..733248.271 rows=1442797 loops=1)
-> HashAggregate (cost=88.06..88.07 rows=1 width=4) (actual
time=3.380..13.561 rows=3043 loops=1)
Group Key: (unnest(adroom.domain_ids))
-> HashAggregate (cost=88.03..88.04 rows=1 width=4) (actual
time=2.199..2.607 rows=3043 loops=1)
Group Key: unnest(adroom.domain_ids)
-> ProjectSet (cost=0.28..87.78 rows=100 width=4) (actual
time=0.701..1.339 rows=3173 loops=1)
-> Index Scan using adroom_active_idx on adroom
(cost=0.28..87.27 rows=1 width=167) (actual time=0.688..1.040 rows=4
loops=1)
Index Cond: ((CURRENT_TIMESTAMP >= start_ts) AND
(CURRENT_TIMESTAMP <= stop_ts))
Filter: (((groups -> 0) ->> 'provider'::text) ~
'^target_mail_ru'::text)
Rows Removed by Filter: 41
-> Index Scan using
adroom_stat_day_adroom_id_domain_id_url_id_is_wlabp_idx on adroom_stat
(cost=0.58..25524.33 rows=491 width=16) (actual time=104.847..240.846
rows=474 loops=3043)
Index Cond: ((day >= date_trunc('day'::text, (CURRENT_TIMESTAMP -
'7 days'::interval))) AND (day <= date_trunc('day'::text,
CURRENT_TIMESTAMP)) AND (domain_id = (unnest(adroom.domain_ids))))
Planning time: 1.580 ms
Execution time: 733331.740 ms

Dmitry Shalashov, relap.io & surfingbird.ru