Forum OpenACS Development: Re: acs_object__new slowness...

Collapse
20: Re: acs_object__new slowness... (response to 1)
Posted by Don Baccus on 11/17/03 05:19 PM
Just for future reference ... I haven't looked at the various views buried in this innocent-looking query but ...

"materialize" in a plan is a good indication that one will find a UNION in the query.  PG doesn't optimize UNION at this point in time, probably the optimizer's most glaring weakness.

Oracle will optimize UNIONs in some cases, not in others, it's somewhat hit or miss.

That's why I worked so hard to rework the permissions model to get rid of UNIONs in VIEWs.  This sped up every PG query against the model by orders of magnitude (when there are lots of objects) and in Oracle sped up a significant percentage of the equivalent queries a great deal.

Collapse
21: Re: acs_object__new slowness... (response to 20)
Posted by Dirk Gomez on 11/17/03 09:10 PM
In Oracle the biggest caveat with UNIONs is that they have an implicit sort attached: to weed out duplicates Oracle needs to sort the result at some point.

E.g. in permissons where you only want to know that there is a row, not how many, you would use a UNION ALL (which returns duplicates, so it doesn't need to sort)

The same holds true probably for Oracle.

Collapse
22: Re: acs_object__new slowness... (response to 20)
Posted by Dirk Gomez on 11/17/03 09:20 PM
Oh and the really big caveat I just forgot about:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/optimops.htm

says:

The optimizer can merge a view into a referencing query block when the view has one or more base tables, provided the view does not contain the following:

    * Set operators (UNION, UNION ALL, INTERSECT, MINUS)

    * A CONNECT BY clause

    * A ROWNUM pseudocolumn

    * Aggregate functions (AVG, COUNT, MAX, MIN, SUM) in the select list