Re: Help with tuning this query

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