From: Dan Harris <fbsd@drivefaster.net>
To: pgsql-performance@postgresql.org
Subject: Re: Query tuning help
Date: Sun, 8 May 2005 20:49:07 -0600
Message-ID: <93c69c01ae38d2b95c902e05da1ffe19@drivefaster.net> (raw)
In-Reply-To: <200505091232.05495.mr-russ@pws.com.au>
References: <924ac6c029f5453c442eacdda06b132c@drivefaster.net>
<200505091051.14689.mr-russ@pws.com.au>
<6f4a543a2fbd348c7e82876b8e477600@drivefaster.net>
<200505091232.05495.mr-russ@pws.com.au>
On May 8, 2005, at 8:32 PM, Russell Smith wrote:
>> I have run this, and while it is very fast, I'm concerned it's not
>> doing what I need.
> How fast is very fast?
>
It took 35 seconds to complete versus ~450 my old way.
>
> select distinct em.incidentid, ea.recordtext as retdata, eg.long,
> eg.lat
> FROM em JOIN ea ON (em.incidentid = ea.incidentid AND em.entrydate >=
> '2005-1-1 00:00'
> AND em.entrydate <= '2005-5-9 00:00' AND (ea.recordtext like '%RED%'
> OR ea.recordtext like '%CORVETTE%'))
> JOIN eg ON em.incidentid = eg.incidentid WHERE
> em.incidentid IN
> (select distinct em.incidentid, ea.recordtext as retdata, eg.long,
> eg.lat
> FROM em JOIN ea ON (em.incidentid = ea.incidentid AND em.entrydate >=
> '2005-1-1 00:00'
> AND em.entrydate <= '2005-5-9 00:00' AND ea.recordtext like
> '%CORVETTE%'))
> JOIN eg ON em.incidentid = eg.incidentid) AND
> em.incidentid IN
> (select distinct em.incidentid, ea.recordtext as retdata, eg.long,
> eg.lat
> FROM em JOIN ea ON (em.incidentid = ea.incidentid AND em.entrydate >=
> '2005-1-1 00:00'
> AND em.entrydate <= '2005-5-9 00:00' AND ea.recordtext like '%RED%'))
> JOIN eg ON em.incidentid = eg.incidentid)
>
Yes, it is nasty, but so was my previous query :) So long as this is
faster, I'm ok with that. I'll see if i can make this work. Thank you
very much.
-Dan
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: fbsd@drivefaster.net
Subject: Re: Query tuning help
In-Reply-To: <93c69c01ae38d2b95c902e05da1ffe19@drivefaster.net>
* 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