Skip site navigation (1) Skip section navigation (2)

Re: A very long running query....

From: Ioannis Anagnostopoulos <ioannis(at)anatec(dot)com>
To: Claudio Freire <klaussfreire(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: A very long running query....
Date: 2012-07-21 20:29:28
Message-ID: 500B1128.6020001@anatec.com (view raw or flat)
Thread:
Lists: pgsql-novicepgsql-performance
On 21/07/2012 21:11, Claudio Freire wrote:
> On Sat, Jul 21, 2012 at 5:10 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
>> <ioannis(at)anatec(dot)com> wrote:
>>>              (feed_all_y2012m07.ship_pos_messages join
>>> ais_server.ship_objects on (ship_pos_messages.obj_id = ship_objects.obj_id))
>>>              on (message_copies.msg_id = ship_pos_messages.msg_id)
>> It's this part of the query that's taking 3.2 hours.
>>
>> Move the filtered message_copies to a CTE, and the filtered
>> ship_pos_messages join to another CTE. That should (in my experience)
>> get you better performance.
> Btw... did you try the hash thing?
Not yet as I am trying at present to simplify the index getting the 
georefs out of it. Don't know if this is a good idea but I though that 
since I am not testing (yet) any equality other than making sure that 
the georefs are not null or empty, I could avoid having it in the index, 
thus reducing its size a lot... At least for now.....

In response to

pgsql-novice by date

Next:From: David RaznickDate: 2012-07-21 23:18:33
Subject: copy a text type.
Previous:From: Claudio FreireDate: 2012-07-21 20:11:27
Subject: Re: A very long running query....

pgsql-performance by date

Next:From: David FetterDate: 2012-07-23 04:37:33
Subject: Re: Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)
Previous:From: Claudio FreireDate: 2012-07-21 20:11:27
Subject: Re: A very long running query....

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group