Re: Queries joining views - Mailing list pgsql-general

Tom Lane wrote:
> Alban Hertroys <alban@magproductions.nl> writes:
>> However, mm_product.number always matches either mm_insrel.snumber or
>> mm_insrel.dnumber (source and destination respectively). The other way
>> around this isn't the case; then snumber and dnumber match number-fields
>> in other tables (they always do).
>
> Oh, then we are looking at the wrong things: we should be comparing the
> histograms of the fields that are being used as the join keys in this
> query.  I had thought they were both "number", but I must be confused.

The design is certainly a bit confusing until you get used to it. It
usually takes new devs here a while to find their way around MMBase
(www.mmbase.org) and its peculiarities. It doesn't help that the
documentation is in rather bad English.

>             regards, tom lane

So this is what we're looking for, right? I can't say I understand how
to interpret this, let alone come to conclusions. I'm afraid I totally
depend on your interpretation here...

zorgweb_solaris=> select * from pg_stats where (attname in ('snumber',
'dnumber') and tablename = 'mm_insrel_table') or (attname = 'number' and
tablename = 'mm_product_table');
-[ RECORD 1
]-----+-----------------------------------------------------------------------------------------------------------
schemaname        | public
tablename         | mm_product_table
attname           | number
null_frac         | 0
avg_width         | 4
n_distinct        | -1
most_common_vals  |
most_common_freqs |
histogram_bounds  | {2930,3244,3558,3872,4186,4500,4814,5128,5442,5756,6070}
correlation       | 0.993398
-[ RECORD 2
]-----+-----------------------------------------------------------------------------------------------------------
schemaname        | public
tablename         | mm_insrel_table
attname           | snumber
null_frac         | 0
avg_width         | 4
n_distinct        | 14336
most_common_vals  | {4300,5210,5366,2994,3724,4118,2982,3058,3072,3460}
most_common_freqs |
{0.00266667,0.002,0.002,0.00166667,0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333}
histogram_bounds  |
{135,3768,4780,14822,57048,92958,125442,158954,433002,502836,610034}
correlation       | 0.083602
-[ RECORD 3
]-----+-----------------------------------------------------------------------------------------------------------
schemaname        | public
tablename         | mm_insrel_table
attname           | dnumber
null_frac         | 0
avg_width         | 4
n_distinct        | 11028
most_common_vals  | {1117583,279,415,291,343,389,635,839,1043,319}
most_common_freqs |
{0.00433333,0.00333333,0.003,0.00266667,0.00266667,0.00266667,0.00266667,0.00266667,0.00266667,0.00233333}
histogram_bounds  |
{147,717,3770,263126,327054,429524,461026,490094,518872,544098,1117603}
correlation       | 0.0571927

Regards,
--
Alban Hertroys
alban@magproductions.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 //