[OPEN-ILS-DEV] Reshelving Complete

[Mike Rylander]

On Wed, Sep 23, 2009 at 12:36 PM, Bill Ott <bott at grpl.org> wrote:
> Continuing to look for our anomalous reshelving times...
>
> Should the "AND circ.id IS NULL", in the second portion of the query, be
> part of the WHERE instead?  Isolating and running only this portion, the
> JOIN seems to add a null circ row to every item.
>

Hrm ... that's possible, but JOIN conditions are supposed to be
evaluated as (essentially) extensions of the WHERE clause.

>
>           SELECT  cp.id
>                          FROM $cp cp
>                   LEFT JOIN $setting setting
>                       ON (cp.circ_lib = setting.org_unit AND setting.name =
> 'circ.reshelving_complete.interval')
>                   LEFT JOIN $circ circ ON (circ.target_copy = cp.id AND
> circ.id IS NULL)
>             WHERE cp.status = 7
>                   AND cp.create_date < NOW() - CAST( COALESCE( BTRIM(
> setting.value,'"' ), ? )  AS INTERVAL)
>
>

[testing ...]

Here are the plans (ignore the costs) of the to variants.  The first
is a trimmed down version of the current query, discarding the org
settings lookup and hard-coding a 2 day interval.  The second moves
the IS NULL condition from the JOIN to the WHERE:

                                   QUERY PLAN
---------------------------------------------------------------------------------
 Hash Left Join  (cost=916269.06..1246331.57 rows=105272 width=8)
   Hash Cond: ("outer".id = "inner".target_copy)
   ->  Seq Scan on "copy" cp  (cost=0.00..329536.14 rows=105272 width=8)
         Filter: ((status = 7) AND (create_date < (now() - '2 days'::interval)))
   ->  Hash  (cost=916269.06..916269.06 rows=1 width=8)
         ->  Seq Scan on circulation circ  (cost=0.00..916269.06 rows=1 width=8)
               Filter: (id IS NULL)
(7 rows)

                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Merge Right Join  (cost=338317.81..2581704.13 rows=105272 width=8)
   Merge Cond: ("outer".target_copy = "inner".id)
   Filter: ("outer".id IS NULL)
   ->  Index Scan using circ_copy_idx on circulation circ
(cost=0.00..2106575.02 rows=51822906 width=16)
   ->  Sort  (cost=338317.81..338580.99 rows=105272 width=8)
         Sort Key: cp.id
         ->  Seq Scan on "copy" cp  (cost=0.00..329536.14 rows=105272 width=8)
               Filter: ((status = 7) AND (create_date < (now() - '2
days'::interval)))
(8 rows)

So, yes, it looks like the JOIN-IS-NULL version is indeed doing the
wrong thing, looking for circs with no ids, which is, of course,
completely wrong.  I'm correcting that in trunk now, and I'll backport
all around back to 1.4.0.

-- 
Mike Rylander
 | VP, Research and Design
 | Equinox Software, Inc. / The Evergreen Experts
 | phone:  1-877-OPEN-ILS (673-6457)
 | email:  miker at esilibrary.com
 | web:  http://www.esilibrary.com