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. function indices for st_transform'd geo columns. Graeme On 26 Sep 2014, at 18:17, Burgess, Freddie <FBurgess@xxxxxxxxxxxxxxx> 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@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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
