Here is my select right after initdb: postgres=# select name,setting from pg_settings where name like '%_cost'; name | setting ----------------------+--------- cpu_index_tuple_cost | 0.005 cpu_operator_cost | 0.0025 cpu_tuple_cost | 0.01 parallel_setup_cost | 1000 parallel_tuple_cost | 0.1 random_page_cost | 4 seq_page_cost | 1 Can you generate plan with random_page_cost = 4? -- Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company From: Dmitry Shalashov [mailto:skaurus@xxxxxxxxx]
Sent: Wednesday, November 22, 2017 5:29 PM
To: Alex Ignatov <a.ignatov@xxxxxxxxxxxxxx>
Cc: pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Query became very slow after 9.6 -> 10 upgrade Sure, here it goes: ----------------------+--------- cpu_index_tuple_cost | 0.005 cpu_operator_cost | 0.0025 parallel_setup_cost | 1000 parallel_tuple_cost | 0.1
2017-11-22 17:24 GMT+03:00 Alex Ignatov <a.ignatov@xxxxxxxxxxxxxx>: Hello! What about : select name,setting from pg_settings where name like '%_cost'; -- Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company From: Dmitry Shalashov [mailto:skaurus@xxxxxxxxx]
Sent: Wednesday, November 22, 2017 5:14 PM
To: pgsql-performance@xxxxxxxxxxxxxx
Subject: 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. It was running under 3 seconds (it's our default timeout) and now it runs for 12 minutes. All three tables are analyzed. 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. 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) 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)))) Execution time: 733331.740 ms Dmitry Shalashov, relap.io & surfingbird.ru
|
