Re: Very slow postgreSQL 9.3.4 query

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

Thanks
________________________________________
From: Graeme B. Bell [grb(at)skogoglandskap(dot)no]
Sent: Monday, September 29, 2014 7:08 AM
To: Burgess, Freddie
Cc: pgsql-performance(at)postgresql(dot)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. function indices for st_transform'd geo columns.

Graeme

On 26 Sep 2014, at 18:17, Burgess, Freddie <FBurgess(at)Radiantblue(dot)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(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
>
>
>
> --
> 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