Re: Very slow postgreSQL 9.3.4 query

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(at)skogoglandskap(dot)no]
Sent: Friday, September 26, 2014 9:55 AM
To: Burgess, Freddie
Cc: pgsql-performance(at)postgresql(dot)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 measurements of time taken, amount of data being processed, hardware used etc).

Graeme.

On 26 Sep 2014, at 15:04, Burgess, Freddie <FBurgess(at)Radiantblue(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance