Forum OpenACS Development: What is the general usefulness of the dir_all_users view?

create view dir_all_users as
   select p.party_id as user_id, p.email, pe.first_names, pe.last_name, p.url
     from parties p, persons pe
    where p.party_id = pe.person_id;
 replaced by all_users now in acs-kernal

Nested Loop  (cost=11105.20..14252.40 rows=1 width=1482)
  ->  Index Scan using forums_messages_pk on forums_messages f  (cost=0.00..3.02 rows=1 width=983)
  ->  Materialize  (cost=13829.18..13829.18 rows=33616 width=499)
        ->  Hash Join  (cost=11105.20..13829.18 rows=33616 width=499)
              ->  Merge Join  (cost=0.00..2494.57 rows=33621 width=354)
                    ->  Index Scan using parties_pk on parties pa  (cost=0.00..812.76 rows=33637 width=146)
                    ->  Index Scan using users_pk on users u  (cost=0.00..1093.40 rows=33621 width=208)
              ->  Hash  (cost=11021.16..11021.16 rows=33616 width=145)
                    ->  Hash Join  (cost=2448.11..11021.16 rows=33616 width=145)
                          ->  Hash Join  (cost=1812.82..9713.52 rows=33621 width=129)
                                ->  Hash Join  (cost=1055.56..8642.72 rows=33628 width=99)
                                      ->  Seq Scan on acs_objects o  (cost=0.00..6014.54 rows=230454 width=91)
                                      ->  Hash  (cost=971.49..971.49 rows=33628 width=8)
                                            ->  Seq Scan on group_element_index  (cost=0.00..971.49 rows=33628 width=8)
                                ->  Hash  (cost=673.21..673.21 rows=33621 width=30)
                                      ->  Seq Scan on persons pe  (cost=0.00..673.21 rows=33621 width=30)
                          ->  Hash  (cost=551.23..551.23 rows=33623 width=16)
                                ->  Seq Scan on membership_rels mr  (cost=0.00..551.23 rows=33623 width=16)

I have not committed the acs__magic_object with(iscachable) since I am scared it will break something (oh, and isstrict should be there as well I think). Should I commit it?

I posted on pgsql-performance about this problem and got an answer from Tom Lane about what is really going on here. Says Tom Lane on pgsql-performance:

This is not actually using GEQO. The reason you are seeing an effect from raising geqo_threshold is that geqo_threshold determines whether or not the view will be flattened into the upper query. For this particular query situation, flattening the view is essential (since you don't want the thing to compute the whole view). The relevant source code tidbit is

/*
 * Yes, so do we want to merge it into parent?    Always do
 * so if child has just one element (since that doesn't
 * make the parent's list any longer).  Otherwise we have
 * to be careful about the increase in planning time
 * caused by combining the two join search spaces into
 * one.  Our heuristic is to merge if the merge will
 * produce a join list no longer than GEQO_RELS/2.
 * (Perhaps need an additional user parameter?)
 */

AFAICS, your only good solution is to make geqo_threshold at least 14, since you want a 7-way join after flattening.

regards, tom lane

These attributes inform the system whether it is safe to replace multiple evaluations of the function with a single evaluation, for run-time optimization. At most one choice should be specified. If none of these appear, VOLATILE is the default assumption.

IMMUTABLE indicates that the function always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its parameter list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.

STABLE indicates that within a single table scan the function will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. Also note that the CURRENT_TIMESTAMP family of functions qualify as stable, since their values do not change within a transaction.

VOLATILE indicates that the function value can change even within a single table scan, so no optimizations can be made. Relatively few database functions are volatile in this sense; some examples are random(), currval(), timeofday(). Note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval().