Re: Very slow postgreSQL 9.3.4 query

Поиск
Список
Период
Сортировка
От Burgess, Freddie
Тема Re: Very slow postgreSQL 9.3.4 query
Дата
Msg-id 3BBE635F64E28D4C899377A61DAA9FE03F0910AD@NBSVR-MAIL01.radiantblue.local
обсуждение исходный текст
Ответ на Very slow postgreSQL 9.3.4 query  ("Burgess, Freddie" <FBurgess@Radiantblue.com>)
Список pgsql-performance
Дерево обсуждения
We also have in our postgresql.conf file

autovaccum = on
default_statistics_target = 100

Do you recommend any changes?

This partitioned table doti_sensor_report contains in total approximately 15 billion rows, autovaccum current has three processes that are running continuously on the box and specifically targeting this table to keep up.

What does the upgrade to 9.3.5 buy us in terms of performance improvements?

thanks Victor

Freddie

From: Victor Yegorov [vyegorov@gmail.com]
Sent: Friday, September 26, 2014 4:25 PM
To: Burgess, Freddie
Subject: Re: [PERFORM] Very slow postgreSQL 9.3.4 query

2014-09-26 23:07 GMT+03:00 Burgess, Freddie <FBurgess@radiantblue.com>:
I have a cron job that updates the statistics on the "doti_sensor_report" table on the first Saturday of every month. Do you think I should re-generate these statistics more often? This table receives streaming inserts to the volume of about 350 million tuples per-month.

There's autovacuum that does the same job for you, I hope you have it enabled on your upgraded DB.
If not, then once-a-month stats is definitely not enough.

I recommend you to look into autovacuum instead of using cron and tune per-table autovacuum settings:
http://www.postgresql.org/docs/current/interactive/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS

Default parameters will cause autovacuum to process the table if 10% (analyze) or 20% (vacuum) of the table had changed. The bigger the table,
the longer it'll take to reach the threshold. Try lowering scale factors on a per-table basis, like:

    ALTER TABLE doti_sensor_report_y2014m09 SET (autovacuum_analyze_scale_factor=0.02, autovacuum_vacuum_scale_factor=0.05);

Also, given your tables are quite big, I would recommend to increase statistics targets for commonly used columns, like:

    ALTER TABLE doti_sensor_report_y2014m09 ALTER node_date_time SET STATISTICS 1000;

Have a look at the docs on these topics and pick they way that suits you most. 


P.S. Consider upgrading to 9.3.5 also, it is a minor one: only restart is required.


--
Victor Y. Yegorov

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Burgess, Freddie"
Дата:
Сообщение: Re: Very slow postgreSQL 9.3.4 query
Следующее
От: Matúš Svrček
Дата:
Сообщение: Re: after upgrade 8.4->9.3 query is slow not using index scan