Tom Lane wrote:
Alban Hertroys <[EMAIL PROTECTED]> writes:
I'm confused too.  Would it be possible for you to send me a dump of
your database?

Attached is a cleaned out database, the full schema is included, but only the relevant tables contain any data.

Thanks.  After digging through it a bit, I understand what's happening,
but I'm not seeing any simple fix.  The problem is basically that
you've got

create or replace view mm_product as
 SELECT ... FROM mm_product_table JOIN mm_object mmop USING (number);

and then the problem query has WHERE mm_product.number = insrel.snumber
which causes the planner to conclude that mm_product_table.number,
mm_object.number, and mm_insrel_table.snumber are all basically
interchangeable.  In particular it ends up performing the join between
mm_product_table.number and mm_object.number as though
mm_product_table.number were being joined to mm_insrel_table.snumber.

It's even worse, I guess, as the mm_insrel view joins mm_insrel_table with mm_object again. So basically the query performs a self-join on mm_object with a detour through mm_insrel_table and mm_product_table...

Which is fine, except that it's thinking that the statistics for
mm_object.number are applicable in this context, and they're completely
misleading.  After the join to mm_insrel_table, the statistics of the
variable are really like mm_insrel_table.number --- in particular the
fraction of the table that has to be visited is much larger than it
would've been for mm_object as a whole.

I don't entirely understand what you're saying here.

Mm_object is always larger than any other table in the database, as every table joins with (different) records in it to determine it's otype and owner. So I don't understand how a fraction of any of those tables could be larger than mm_object as a whole...

In fact, originally the schema used inheritance; every table inherited (directly or indirectly) from mm_object. As this resulted in unions, which caused much more performance problems than the current view-approach, I implemented the current approach.
In fact, this approach was lent from what MMBase uses for the MSSQL layer.

Well, as I implemented the way the views are defined, there is room for changes in that area. Suggestions are welcome.

This is a problem we've understood in a generic form for awhile:
a join or selection might change the statistics of a variable,
and so the info stored in the catalogs ought to be modified somehow
to predict what will happen at upper join levels.  We've not seen
it in this particular form before, though.

I'm not sure if there's a whole lot you can do about it in the near term
other than refactor your schema to avoid having different tables joining
to different subranges of mm_object.number.  (You don't necessarily have
to get rid of mm_object --- just try assigning its keys from a serial,
or something, so that there's no correlation to the ranges of keys in
other tables.)

Unfortunately the number key is required to correlate to the number keys in other tables. That's the whole point of that table. It's also already generated from a sequence...

I am looking at a view options at the moment:

1.) Cluster mm_object on an index over otype - I'm not sure how that would influence the statistics; if it doesn't then this wouldn't change much.

2.) Change mm_object into a view over the tables that now join with it. I'll have to devise some way to get the otype and owner columns into the other tables.

3.) An extension to option 2; Creating seperate tables, only containing the relevant sections from mm_object, combining them into a view-version of mm_object. Like this:

CREATE TABLE mm_product_object (
        number integer PRIMARY KEY,
        otype integer,
        owner text
CREATE TABLE mm_insrel_object (
        number integer PRIMARY KEY,
        otype integer,
        owner text

(I recall seeing an inheritance-like statement that makes copies of table definitions - seems useful in this case)

        SELECT * FROM mm_product_object
        UNION ALL
        SELECT * FROM mm_insrel_object;

It remains to be seen that MMBase can handle mm_object being a view, but (if not) it probably will work if it's an updatable view.

I'm leaning to option 3, but if option 1 helps it saves me a lot of trouble.

We might be able to do something about actually solving the statistical
problem in 8.3, but I fear it's too late to think about it for 8.2.

Well, I had hoped for a suitable workaround, and I believe I may have a few options now. Waiting for the next PostgreSQL release never really was an option for us (deadline is somewhere next week). So it doesn't really matter to us that there won't be a solution until 8.3, or maybe even later.

Thanks for the help so far, glad to be able to point out an actual problem.

Alban Hertroys

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to