Re: R: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22
От | Adrian Klaver |
---|---|
Тема | Re: R: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22 |
Дата | |
Msg-id | 70f38082-567c-36df-1795-31a43cf51e8c@aklaver.com обсуждение исходный текст |
Ответ на | R: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22 (Job <Job@colliniconsulting.it>) |
Ответы |
R: R: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22
(Job <Job@colliniconsulting.it>)
|
Список | pgsql-general |
On 01/08/2017 07:30 AM, Job wrote: > 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 isnot 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 isnot null) and (stato != 2 or stato is null); First this is a change in query from your original post, so see Tom's post for recommendation on how to deal with your original problem. Second just a quick glance seems to indicate that the other filters get the number of rows down to 1 or 2 so there does not seem to be any need to do an index scan on grulist.stato(assuming that the plain stato is a reference to grulist.stato) Then there is this: left join grulist on grulist.nome=webrecord.dominio and grulist.codgruppo='f50132' and grulist.stato in (1,2) ... and (grulist.stato='1' OR grucategorie.codcategoria is not null OR listef_geo_reject.country is not null) and (stato != 2or stato is null) Caveats, it is morning here and coffee is still brewing, but I am not following. The left join limits grulist.stato to NULL,1, 2. Your first condition catches the 1 value. Should not the second 'and' be (stato = 2 or stato is null). In fact I do not see why it needsto exist at all, which seems to be borne out by your experimentation. > 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 ISNOT 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.051rows=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=1loops=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) (actualtime=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) (actualtime=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=1loops=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.021rows=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 > -- Adrian Klaver adrian.klaver@aklaver.com
В списке pgsql-general по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: R: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22