Re: Very slow postgreSQL 9.3.4 query - Mailing list pgsql-performance

From Burgess, Freddie
Subject Re: Very slow postgreSQL 9.3.4 query
Date
Msg-id 3BBE635F64E28D4C899377A61DAA9FE03F09104D@NBSVR-MAIL01.radiantblue.local
Whole thread Raw
In response to Re: Very slow postgreSQL 9.3.4 query  ("Graeme B. Bell" <grb@skogoglandskap.no>)
Responses Re: Very slow postgreSQL 9.3.4 query  ("Graeme B. Bell" <grb@skogoglandskap.no>)
List pgsql-performance
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



pgsql-performance by date:

Previous
From: "Graeme B. Bell"
Date:
Subject: Re: Very slow postgreSQL 9.3.4 query
Next
From: Victor Yegorov
Date:
Subject: Re: after upgrade 8.4->9.3 query is slow not using index scan