Forum OpenACS Q&A: PG: extremely slow to add a folder without parent to the CR!

Collapse
1: PG: extremely slow to add a folder without parent to the CR!
Posted by Rocael Hernández Rizzardini on 09/18/04 12:21 AM
Basically takes ages for perform an add when you have about > 400K cr_items,
the problem is at cr_items_tree_insert_tr (), that is called each time you add a cr_item.

The query problem is:

select max(tree_leaf_key_to_int(child.tree_sortkey)) into v_max_value
          from cr_items child
        where child.parent_id not in (select item_id from cr_items);

                                  QUERY PLAN
-----------------------------------------------------------------------------------
Aggregate  (cost=2676484485.10..2676484485.10 rows=1 width=15)
  ->  Seq Scan on cr_items child  (cost=0.00..2676483963.52 rows=208631 width=15)
        Filter: (NOT (subplan))
        SubPlan
          ->  Seq Scan on cr_items  (cost=0.00..11785.61 rows=417261 width=4)

any suggestions for workarounds?

we detected that when trying to install etp and was waiting forever in the installation step:
select content_folder__new (
    'trash',
    'Trash',
    'Deleted content items get put here',
    0,
    null,
    -400,
    now(),
    null,
    null
  );

Collapse
2: Re: PG: extremely slow to add a folder without parent to the CR! (response to 1)
Posted by Rocael Hernández Rizzardini on 09/18/04 06:16 PM
for the etp installation, I temporarily do this:
select max(tree_leaf_key_to_int(child.tree_sortkey)) into v_max_value
          from cr_items child
        where child.parent_id =0;

Worked fine and then back to the original function. But a general solution is needed here.

Collapse
3: Improved (response to 1)
Posted by Rocael Hernández Rizzardini on 09/21/04 06:43 PM
http://xarg.net/tools/cvs/change-set-details?key=14456

though a better solution still is needed.