Re: Queries joining views

Tom Lane wrote:
> Alban Hertroys <alban(at)magproductions(dot)nl> 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)

CREATE OR REPLACE VIEW mm_object AS
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.

Regards,
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //