Home > mailing lists

Thread: Very slow postgreSQL 9.3.4 query

Very slow postgreSQL 9.3.4 query

From

"Burgess, Freddie"

Date:

26 September 2014, 16:04:33

Help, please can anyone offer suggestions on how to speed this query up.

thanks

Attachment

Poor Pref query.txt

Re: Very slow postgreSQL 9.3.4 query

From

"Graeme B. Bell"

Date:

26 September 2014, 17:15:19

A good way to start would be to introduce the query - describe what it is meant to do, give some performance data (your
measurementsof time taken, amount of data being processed, hardware used etc). 

Graeme.

On 26 Sep 2014, at 15:04, Burgess, Freddie <FBurgess@Radiantblue.com> wrote:

> Help, please can anyone offer suggestions on how to speed this query up.
>
> thanks
>
>
> <Poor Pref query.txt>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Re: Very slow postgreSQL 9.3.4 query

From

"Burgess, Freddie"

Date:

26 September 2014, 19:20:50

Workflow description:

1.) User draws a polygon around an area of interest, via UI.
2.) UI responses with how many sensors reside within the area of the polygon.
3.) Hibernate generates the count query detailed in the attachment.

Performance data is included in the attachment, via EXPLAIN PLAN, query takes approx 6 minutes to return count to UI.
Amount of data processed is also included in the attachment, 185 million row partition.

Hardware

VM
80GB memory
8 CPU Xeon
Linux 2.6.32-431.3.1.el6.x86-64
40TB disk, Database size: 8TB
PostgreSQL 9.3.4 with POSTGIS 2.1.1, Red Hat 4.4.7-4, 64 bit
streaming replication

Postgresql.conf

max_connection = 100
shared_buffers = 32GB
work_mem = 16MB
maintenance_work_mem = 1GB
seq_page_cost = 1.0
random_page_cost = 2.0
cpu_tuple_cost = 0.03
effective_cache_size = 48GB

________________________________________
From: Graeme B. Bell [grb@skogoglandskap.no]
Sent: Friday, September 26, 2014 9:55 AM
To: Burgess, Freddie
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Very slow postgreSQL 9.3.4 query

A good way to start would be to introduce the query - describe what it is meant to do, give some performance data (your
measurementsof time taken, amount of data being processed, hardware used etc). 

Graeme.

On 26 Sep 2014, at 15:04, Burgess, Freddie <FBurgess@Radiantblue.com> wrote:

> Help, please can anyone offer suggestions on how to speed this query up.
>
> thanks
>
>
> <Poor Pref query.txt>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Re: Very slow postgreSQL 9.3.4 query

From

"Burgess, Freddie"

Date:

26 September 2014, 23:07:19

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.

I'll generate new stat's over the weekend, and then execute a new plan

thanks

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

2014-09-26 19:17 GMT+03:00 Burgess, Freddie <FBurgess@radiantblue.com>:

Performance data is included in the attachment, via EXPLAIN PLAN, query takes approx 6 minutes to return count to UI.
Amount of data processed is also included in the attachment, 185 million row partition.

It looks like your statistics are off:

-> Index Scan using idx_sensor_report_query_y2014m09 on doti_sensor_report_y2014m09 this__1 (cost=0.57..137498.17 rows=3883 width=0) (actual time=168.416..348873.308 rows=443542 loops=1)

Optimizer expects to find ~ 4k rows, while in reality there're 2 orders of magnitude more rows that matches the condition.

Perhaps BitmapIndexScan could be faster here.

--
Victor Y. Yegorov

Re: Very slow postgreSQL 9.3.4 query

From

"Burgess, Freddie"

Date:

27 September 2014, 01:25:25

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

Re: Very slow postgreSQL 9.3.4 query

From

"Graeme B. Bell"

Date:

29 September 2014, 22:04:55

Hi,

Two things:

- Make sure you are creating a GIST index on your geometry column in postgis.
- Try using st_intersects rather than &&. I've noticed that && isn't using indices correctly in some situations e.g.
functionindices for st_transform'd geo columns. 

Graeme


On 26 Sep 2014, at 18:17, Burgess, Freddie <FBurgess@Radiantblue.com> wrote:

> Workflow description:
>
> 1.) User draws a polygon around an area of interest, via UI.
> 2.) UI responses with how many sensors reside within the area of the polygon.
> 3.) Hibernate generates the count query detailed in the attachment.
>
> Performance data is included in the attachment, via EXPLAIN PLAN, query takes approx 6 minutes to return count to UI.
> Amount of data processed is also included in the attachment, 185 million row partition.
>
> Hardware
>
> VM
> 80GB memory
> 8 CPU Xeon
> Linux 2.6.32-431.3.1.el6.x86-64
> 40TB disk, Database size: 8TB
> PostgreSQL 9.3.4 with POSTGIS 2.1.1, Red Hat 4.4.7-4, 64 bit
> streaming replication
>
> Postgresql.conf
>
> max_connection = 100
> shared_buffers = 32GB
> work_mem = 16MB
> maintenance_work_mem = 1GB
> seq_page_cost = 1.0
> random_page_cost = 2.0
> cpu_tuple_cost = 0.03
> effective_cache_size = 48GB
>
> ________________________________________
> From: Graeme B. Bell [grb@skogoglandskap.no]
> Sent: Friday, September 26, 2014 9:55 AM
> To: Burgess, Freddie
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Very slow postgreSQL 9.3.4 query
>
> A good way to start would be to introduce the query - describe what it is meant to do, give some performance data
(yourmeasurements of time taken, amount of data being processed, hardware used etc). 
>
> Graeme.
>
>
> On 26 Sep 2014, at 15:04, Burgess, Freddie <FBurgess@Radiantblue.com> wrote:
>
>> Help, please can anyone offer suggestions on how to speed this query up.
>>
>> thanks
>>
>>
>> <Poor Pref query.txt>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Re: Very slow postgreSQL 9.3.4 query

From

"Burgess, Freddie"

Date:

30 September 2014, 05:59:27

I changed the query from (st_within or st_touches) to ST_intersects, that sped up the execution. Reference progress in
Attachmentplease. 

Thanks
________________________________________
From: Graeme B. Bell [grb@skogoglandskap.no]
Sent: Monday, September 29, 2014 7:08 AM
To: Burgess, Freddie
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Very slow postgreSQL 9.3.4 query

Hi,

Two things:

- Make sure you are creating a GIST index on your geometry column in postgis.
- Try using st_intersects rather than &&. I've noticed that && isn't using indices correctly in some situations e.g.
functionindices for st_transform'd geo columns. 

Graeme


On 26 Sep 2014, at 18:17, Burgess, Freddie <FBurgess@Radiantblue.com> wrote:

> Workflow description:
>
> 1.) User draws a polygon around an area of interest, via UI.
> 2.) UI responses with how many sensors reside within the area of the polygon.
> 3.) Hibernate generates the count query detailed in the attachment.
>
> Performance data is included in the attachment, via EXPLAIN PLAN, query takes approx 6 minutes to return count to UI.
> Amount of data processed is also included in the attachment, 185 million row partition.
>
> Hardware
>
> VM
> 80GB memory
> 8 CPU Xeon
> Linux 2.6.32-431.3.1.el6.x86-64
> 40TB disk, Database size: 8TB
> PostgreSQL 9.3.4 with POSTGIS 2.1.1, Red Hat 4.4.7-4, 64 bit
> streaming replication
>
> Postgresql.conf
>
> max_connection = 100
> shared_buffers = 32GB
> work_mem = 16MB
> maintenance_work_mem = 1GB
> seq_page_cost = 1.0
> random_page_cost = 2.0
> cpu_tuple_cost = 0.03
> effective_cache_size = 48GB
>
> ________________________________________
> From: Graeme B. Bell [grb@skogoglandskap.no]
> Sent: Friday, September 26, 2014 9:55 AM
> To: Burgess, Freddie
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Very slow postgreSQL 9.3.4 query
>
> A good way to start would be to introduce the query - describe what it is meant to do, give some performance data
(yourmeasurements of time taken, amount of data being processed, hardware used etc). 
>
> Graeme.
>
>
> On 26 Sep 2014, at 15:04, Burgess, Freddie <FBurgess@Radiantblue.com> wrote:
>
>> Help, please can anyone offer suggestions on how to speed this query up.
>>
>> thanks
>>
>>
>> <Poor Pref query.txt>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

Attachment

Poor Pref query new.txt