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@xxxxxxxxxxxxxxxxx] Sent: Friday, September 26, 2014 9:55 AM To: Burgess, Freddie Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
