From: | "Graeme B(dot) Bell" <grb(at)skogoglandskap(dot)no> |
---|---|
To: | "Burgess, Freddie" <FBurgess(at)Radiantblue(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Very slow postgreSQL 9.3.4 query |
Date: | 2014-09-29 11:08:22 |
Message-ID: | CCC8246E-B204-4AC2-811D-EB9CA9F7814B@skogoglandskap.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2014-09-29 15:00:26 | Re: Yet another abort-early plan disaster on 9.3 |
Previous Message | Matúš Svrček | 2014-09-28 20:24:15 | Re: after upgrade 8.4->9.3 query is slow not using index scan |