R: Postgres 9.6.1 big slowdown by upgrading 8.4.22

From: Job <Job(at)colliniconsulting(dot)it>
To: 'Chris Mair' <chris(at)1006(dot)org>, Job <Job(at)colliniconsulting(dot)it>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: R: Postgres 9.6.1 big slowdown by upgrading 8.4.22
Date: 2017-01-08 15:30:46
Message-ID: 88EF58F000EC4B4684700C2AA3A73D7A08054EACC254@W2008DC01.ColliniConsulting.lan
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi, here it is, excuse me for the delay:

select * from webrecord
left join grucategorie on grucategorie.codcategoria=webrecord.categoria and grucategorie.codgruppo='f50132'
left join grulist on grulist.nome=webrecord.dominio and grulist.codgruppo='f50132' and grulist.stato in (1,2)
left join listef_geo_reject on listef_geo_reject.country=webrecord.country and listef_geo_reject.codgruppo='f50132'
where dominio='PATTERN' and (grulist.stato='1' OR grucategorie.codcategoria is not null OR listef_geo_reject.country is not null) and (stato != 2 or stato is null);

flashstart=# explain analyze select * from webrecord
left join grucategorie on grucategorie.codcategoria=webrecord.categoria and grucategorie.codgruppo='f50132'
left join grulist on grulist.nome=webrecord.dominio and grulist.codgruppo='f50132' and grulist.stato in (1,2)
left join listef_geo_reject on listef_geo_reject.country=webrecord.country and listef_geo_reject.codgruppo='f50132'
where dominio='PATTERN' and (grulist.stato='1' OR grucategorie.codcategoria is not null OR listef_geo_reject.country is not null) and (stato != 2 or stato is null);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=246.29..801.33 rows=2 width=260) (actual time=0.364..0.364 rows=0 loops=1)
Filter: ((grulist.stato = '1'::numeric) OR (grucategorie.codcategoria IS NOT NULL) OR (listef_geo_reject.country IS NOT NULL))
-> Nested Loop Left Join (cost=164.84..558.37 rows=2 width=241) (actual time=0.364..0.364 rows=0 loops=1)
Join Filter: ((grulist.nome)::text = (webrecord.dominio)::text)
Filter: ((grulist.stato <> '2'::numeric) OR (grulist.stato IS NULL))
Rows Removed by Filter: 1
-> Nested Loop Left Join (cost=124.54..448.46 rows=2 width=103) (actual time=0.297..0.299 rows=1 loops=1)
-> Index Scan using dominio_idx on webrecord (cost=0.56..120.46 rows=2 width=74) (actual time=0.050..0.051 rows=1 loops=1)
Index Cond: ((dominio)::text = 'PATTERN'::text)
-> Bitmap Heap Scan on grucategorie (cost=123.98..163.99 rows=1 width=29) (actual time=0.243..0.243 rows=1 loops=1)
Recheck Cond: (((codgruppo)::text = 'f50132'::text) AND ((codcategoria)::text = (webrecord.categoria)::text))
Heap Blocks: exact=3
-> BitmapAnd (cost=123.98..123.98 rows=1 width=0) (actual time=0.230..0.230 rows=0 loops=1)
-> Bitmap Index Scan on grucategorie_codgruppo_idx (cost=0.00..40.39 rows=14 width=0) (actual time=0.035..0.035 rows=135 loops=1)
Index Cond: ((codgruppo)::text = 'f50132'::text)
-> Bitmap Index Scan on grucategorie_categoria_idx (cost=0.00..83.33 rows=406 width=0) (actual time=0.187..0.187 rows=1579 loops=1)
Index Cond: ((codcategoria)::text = (webrecord.categoria)::text)
-> Materialize (cost=40.30..109.87 rows=1 width=138) (actual time=0.051..0.051 rows=1 loops=1)
-> Bitmap Heap Scan on grulist (cost=40.30..109.87 rows=1 width=138) (actual time=0.047..0.047 rows=1 loops=1)
Recheck Cond: ((nome)::text = 'PATTERN'::text)
Filter: ((stato = ANY ('{1,2}'::numeric[])) AND ((codgruppo)::text = 'f50132'::text))
Rows Removed by Filter: 1
Heap Blocks: exact=2
-> Bitmap Index Scan on grulist_nome_idx (cost=0.00..40.30 rows=2 width=0) (actual time=0.021..0.021 rows=2 loops=1)
Index Cond: ((nome)::text = 'PATTERN'::text)
-> Bitmap Heap Scan on listef_geo_reject (cost=81.46..121.47 rows=1 width=19) (never executed)
Recheck Cond: (((codgruppo)::text = 'f50132'::text) AND ((country)::text = (webrecord.country)::text))
-> BitmapAnd (cost=81.46..81.46 rows=1 width=0) (never executed)
-> Bitmap Index Scan on listef_reject_codgruppo_idx (cost=0.00..40.44 rows=21 width=0) (never executed)
Index Cond: ((codgruppo)::text = 'f50132'::text)
-> Bitmap Index Scan on listef_reject_country_idx (cost=0.00..40.76 rows=63 width=0) (never executed)
Index Cond: ((country)::text = (webrecord.country)::text)
Planning time: 0.881 ms
Execution time: 0.510 ms

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2017-01-08 16:21:41 Re: R: Postgres 9.6.1 big slowdown by upgrading 8.4.22
Previous Message Chris Mair 2017-01-08 15:25:12 Re: Postgres 9.6.1 big slowdown by upgrading 8.4.22