Re: horrendous query challenge :-) - Mailing list pgsql-general

>
>
>What is findsite doing exactly?  If it's a table lookup, maybe you could
>inline it into this query and get some optimization.
>
>            regards, tom lane
>
>
Tom,

You hit the nail on the head.

The findsite(entity_id) plpgsql function queries the 'entity' table
recursively.  'Entity' table has entity_id, parent_id (which is another
entity_id in the 'entity' table) and type (such as S for site, H for
host, etc...).  My data is organized in a heirarchy
site-host-app-practice, so in the worst case findsite recurses three
times when called with an entity_id of a practice.  However, to optimize
findsite (and it's cousins findhost and findregion), I created a table
called findparent_cache which has entity_id, parent_id, and type (of the
parent).  When you call findsite() it checks first to see if it's
computed this particular value before (it would find it in the
findparent_cache).  There are only approx. 800 entity ids in the entity
table, so after 1 loop over the entity table with findsite(), it should
be hitting 100% cache, and thus it becomes a simple table lookup on
findparent_cache.

To test Tom's hypothesis, I ensured that findparent_cache was fully
populated, and changed the query to...

SELECT wm.entity_id, e.type, e.name, w.interface_label,
      wm.last_contact AS remote_ts, s.name, r.name  FROM
entity_watch_map wm, entity e, site s,
      region r, watch w, findparent_cache fpc
WHERE wm.last_contact > "timestamp"(now() - 180)
  AND wm.current = false
  AND wm.msg_type = w.msg_type
  AND wm.entity_id = e.entity_id
  AND e.active = true
  AND wm.entity_id = fpc.entity_id
  AND fpc.type = 'S'
  AND fpc.parent_id = s.site_id
  AND s.region_id = r.region_id
ORDER BY wm.last_contact desc, r.name, s.name;

at which point the query runs pretty much instantly.

That's an awful lot of overhead for that poor function.  findsite() is a
key function that we use all over the place.  I thought it was fairly
efficient but this demonstrates how it can quickly get out of hand.  I
suppose if I could always ensure that findsite_cache was completely
populated, we could always just hit that directly.  Since "what is the
site id of the site that holds this entity?" is such a common question,
we really should have it in a table as opposed to a function lookup,
huh?  Does even the simplest plpgsql function have this kind of
overhead?  Or is my function poorly written?  Here is the function code....

create function findsite(int4) returns int4 as '
  declare
    child alias for $1;
    thesite int4;
  begin
    select parent_id into thesite from findparent_cache where entity_id
= child and type = ''S'';
    if not found then
      select findparenttype(child, ''S'') into thesite;
      execute ''insert into findparent_cache values ('' || child ||
'','' || thesite || '',''''S'''')'';
    else
    end if;
    return thesite;
  end;
' language 'plpgsql';

create function findparenttype(int4, varchar) returns int4 as '
  select parent.entity_id from entity parent, entity child
  where child.entity_id = $1
    and child.lft between parent.lft and parent.rgt
    and parent.type = $2;
' language 'sql';

The 'entity' table implements the Celko nested set model, thus the lft's
and rgt's and self-join.

Could these functions be written more efficiently, or am I just
witnessing the overhead of functions, and I should avoid them unless I'm
using them in O(1) situations?

Thanks for all the help,
Fran