From: Ragnar Hafstaư <gnari@simnet.is>
To: Ken Egervari <ken@upfactor.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help with tuning this query
Date: Wed, 02 Mar 2005 18:49:53 +0000
Message-ID: <1109789394.31084.20.camel@localhost.localdomain> (raw)
In-Reply-To: <000901c51f55$aa4b7400$6a01a8c0@KEN>
References: <001001c51ef4$3849eeb0$6a01a8c0@KEN>
<1109787227.31084.12.camel@localhost.localdomain>
<000901c51f55$aa4b7400$6a01a8c0@KEN>
On Wed, 2005-03-02 at 13:28 -0500, Ken Egervari wrote:
> >> select s.*
> >> from shipment s
> >> inner join carrier_code cc on s.carrier_code_id = cc.id
> >> inner join carrier c on cc.carrier_id = c.id
> >> inner join carrier_to_person ctp on ctp.carrier_id = c.id
> >> inner join person p on p.id = ctp.person_id
> >> inner join shipment_status cs on s.current_status_id = cs.id
> >> inner join release_code rc on cs.release_code_id = rc.id
> >> left join shipment_status ss on ss.shipment_id = s.id
> >> where
> >> p.id = :personId and
> >> s.is_purged = false and
> >> rc.number = '9' and
> >> cs is not null and
> >> cs.date >= current_date - 31
> >> order by cs.date desc
> >
> > I may be missing something, but it looks like the second join
> > on shipment_status (the left join) is not adding anything to your
> > results, except more work. ss is not used for output, nor in the where
> > clause, so what is its purpose ?
> ... The second
> left join is for eager loading so that I don't have to run a seperate query
> to fetch the children for each shipment. This really does improve
> performance because otherwise you'll have to make N+1 queries to the
> database, and that's just too much overhead.
are you saying that you are actually doing a
select s.*,ss.* ...
?
> > if cs.date has an upper limit, it might be helpful to change the
> > condition to a BETWEEN
>
> Well, I could create an upper limit. It would be the current date. Would
> adding in this redundant condition improve performance?
it might help the planner estimate better the number of cs rows
affected. whether this improves performance depends on whether
the best plans are sensitive to this.
an EXPLAIN ANALYSE might reduce the guessing.
gnari
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: gnari@simnet.is, ken@upfactor.com
Subject: Re: Help with tuning this query
In-Reply-To: <1109789394.31084.20.camel@localhost.localdomain>
* 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