Обсуждение: [PERFORM] performance drop after upgrade (9.6 > 10)
Hi, I wrote a query that joins several tables usually returning less than 1000 rows, groups them and generates a JSON object of the result. In 9.6 is was a question of milliseconds for that query to return the requested data. Now, after upgrading to 10, the query never returns - at least it hasn't returned in the last hour. To see what happens, I requested the query plan [1]. It looks complex and shows a lot of parallelization. I don't have the query plan from 9.6, but I remember it being considerably simpler. Can anyone have a guess what altered the performance here so dramatically? Is there a way to disable new parallelization features just for this query to see if it makes any difference? Best Johannes [1] https://explain.depesz.com/s/xsPP -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
2017-10-11 13:06 GMT+02:00 johannes graën <johannes@selfnet.de>:
Hi,
I wrote a query that joins several tables usually returning less than
1000 rows, groups them and generates a JSON object of the result. In
9.6 is was a question of milliseconds for that query to return the
requested data. Now, after upgrading to 10, the query never returns -
at least it hasn't returned in the last hour.
To see what happens, I requested the query plan [1]. It looks complex
and shows a lot of parallelization. I don't have the query plan from
9.6, but I remember it being considerably simpler.
Can anyone have a guess what altered the performance here so
dramatically? Is there a way to disable new parallelization features
just for this query to see if it makes any difference?
have you fresh statistics? After upgrade is necessary to run ANALYZE command
Regards
Pavel
Best
Johannes
[1] https://explain.depesz.com/s/xsPP
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Wed, Oct 11, 2017 at 1:11 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > have you fresh statistics? After upgrade is necessary to run ANALYZE command Yes, that was missing indeed. I did ANALYZE but apparently on all databases but this one. I could have guessed that 1,098,956,679,131,935,754,413,282,631,696,252,928 is not a reasonable cost value. Thanks, Pavel. Best Johannes -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Hi Pavel, *, you were right with ANALYZing the DB first. However, even after doing so, I frequently see Seq Scans where an index was used before. This usually cooccurs with parallelization and looked different before upgrading to 10. I can provide an example for 10 [1], but I cannot generate a query plan for 9.6 anymore. Any ideas what makes the new version more seqscanny? [1] https://explain.depesz.com/s/gXD3 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
On Tue, Oct 24, 2017 at 04:15:59PM +0200, johannes graën wrote: > upgrading to 10. I can provide an example for 10 [1], but I cannot > generate a query plan for 9.6 anymore. You could (re)install PG96 alongside PG10 and run a copy of the DB (even from your homedir, or on a difference server) and pg_dump |pg_restore the relevant tables (just be sure to specify the alternate host/port/user/etc as needed for the restore invocation). Justin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
On 2017-10-24 17:18, Justin Pryzby wrote: > You could (re)install PG96 alongside PG10 and run a copy of the DB (even from > your homedir, or on a difference server) and pg_dump |pg_restore the relevant > tables (just be sure to specify the alternate host/port/user/etc as needed for > the restore invocation). I considered that but it is far too expensive just for getting the old query plan. The database is more than 1 TB big and replaying it from a dump to another server took us several days, primarily due to the heavy use of materialized views that are calculated over all rows of some large tables. As long as there is no safe pg_downgrade --link I'd rather keep trying to improve query performance on the current version. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
johannes graën wrote: > Hi Pavel, *, > > you were right with ANALYZing the DB first. However, even after doing > so, I frequently see Seq Scans where an index was used before. This > usually cooccurs with parallelization and looked different before > upgrading to 10. I can provide an example for 10 [1], but I cannot > generate a query plan for 9.6 anymore. https://wiki.postgresql.org/wiki/Slow_Query_Questions -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
On Tue, Oct 24, 2017 at 04:15:59PM +0200, johannes graën wrote: > Hi Pavel, *, > > you were right with ANALYZing the DB first. However, even after doing > so, I frequently see Seq Scans where an index was used before. This > usually cooccurs with parallelization and looked different before > upgrading to 10. I can provide an example for 10 [1], but I cannot > generate a query plan for 9.6 anymore. > > Any ideas what makes the new version more seqscanny? Is it because max_parallel_workers_per_gather now defaults to 2 ? BTW, I would tentatively expect a change in default to be documented in the release notes but can't see that it's. 77cd477c4ba885cfa1ba67beaa82e06f2e182b85 Justin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
On Thu, Oct 26, 2017 at 02:45:15PM -0500, Justin Pryzby wrote: > On Tue, Oct 24, 2017 at 04:15:59PM +0200, johannes graën wrote: > > Hi Pavel, *, > > > > you were right with ANALYZing the DB first. However, even after doing > > so, I frequently see Seq Scans where an index was used before. This > > usually cooccurs with parallelization and looked different before > > upgrading to 10. I can provide an example for 10 [1], but I cannot > > generate a query plan for 9.6 anymore. > > > > Any ideas what makes the new version more seqscanny? > > Is it because max_parallel_workers_per_gather now defaults to 2 ? > > BTW, I would tentatively expect a change in default to be documented in the > release notes but can't see that it's. > 77cd477c4ba885cfa1ba67beaa82e06f2e182b85 Oops, you are correct. The PG 10 release notes, which I wrote, should have mentioned this. :-( https://www.postgresql.org/docs/10/static/release-10.html -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +