From: Ioannis Anagnostopoulos <ioannis@anatec.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: A very long running query....
Date: Sat, 21 Jul 2012 21:29:28 +0100
Message-ID: <500B1128.6020001@anatec.com> (raw)
In-Reply-To: <CAGTBQpb7XZ3Q_tkC86qB9jy0AEoy-BZZAZPQ+qRQhsgOsN3K_A@mail.gmail.com>
References: <5007FC82.9070007@anatec.com>
<5009CB5A.7070801@anatec.com>
<CAGTBQpY6Ma44cvGmbcsjc_BBcScec1KOv7kd2_VRU1Ss9OM3RQ@mail.gmail.com>
<CAGTBQpb7XZ3Q_tkC86qB9jy0AEoy-BZZAZPQ+qRQhsgOsN3K_A@mail.gmail.com>
On 21/07/2012 21:11, Claudio Freire wrote:
> On Sat, Jul 21, 2012 at 5:10 PM, Claudio Freire <klaussfreire@gmail.com> wrote:
>> <ioannis@anatec.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.....
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ioannis@anatec.com, klaussfreire@gmail.com
Subject: Re: A very long running query....
In-Reply-To: <500B1128.6020001@anatec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox