Project

General

Profile

« Previous | Next » 

Revision 3299

Added by Aaron Marcuse-Kubitza over 12 years ago

schemas/vegbien.sql: {plantname,namedplace}_update_ancestors(): Fixed slowdown due to removed index on {plantname,namedplace}.parent_id by adding COALESCE to enable using the plantname_unique index for the lookup instead

View differences:

schemas/tree_cross-links.sql
40 40 
    -- recursively tell all descendants
41 41 
    UPDATE plantname
42 42 
    SET plantname_id = plantname_id -- need at least one SET statement
43 
    WHERE parent_id = new.plantname_id -- the plantname_id passed as param
43 
    -- Add COALESCE() to enable using plantname_unique index for lookup
44 
    WHERE COALESCE(parent_id, 2147483647) = new.plantname_id
44 45 
    ;
45 46
46 47 
    /* Note: We don't need an ON DELETE trigger to update the descendants'
schemas/vegbien.sql
230 230 
    -- recursively tell all descendants
231 231 
    UPDATE namedplace
232 232 
    SET namedplace_id = namedplace_id -- need at least one SET statement
233 
    WHERE parent_id = new.namedplace_id -- the namedplace_id passed as param
233 
    -- Add COALESCE() to enable using namedplace_unique index for lookup
234 
    WHERE COALESCE(parent_id, 2147483647) = new.namedplace_id
234 235 
    ;
235 236
236 237 
    /* Note: We don't need an ON DELETE trigger to update the descendants'
......
280 281 
    -- recursively tell all descendants
281 282 
    UPDATE plantname
282 283 
    SET plantname_id = plantname_id -- need at least one SET statement
283 
    WHERE parent_id = new.plantname_id -- the plantname_id passed as param
284 
    -- Add COALESCE() to enable using plantname_unique index for lookup
285 
    WHERE COALESCE(parent_id, 2147483647) = new.plantname_id
284 286 
    ;
285 287
286 288 
    /* Note: We don't need an ON DELETE trigger to update the descendants'

Also available in: Unified diff