Re: Queries joining views

Tom Lane wrote:
> Alban Hertroys <alban(at)magproductions(dot)nl> writes:
>> tablename | mm_product_table
>> attname | number
>> histogram_bounds | {2930,3244,3558,3872,4186,4500,4814,5128,5442,5756,6070}
>
>> tablename | mm_insrel_table
>> attname | snumber
>> histogram_bounds |
>> {135,3768,4780,14822,57048,92958,125442,158954,433002,502836,610034}
>
> Hmm ... if I'm not still confused, these are the two columns being
> mergejoined in your slow query (would you double-check that?).

That's correct.

I read up some on the meaning of the pg_stats values, and I noticed that
mm_insrel_tables' snumber and dnumber columns seem to have a rather
bad correlation. I think this could be improved by clustering on an
index over (number, snumber, dnumber); is that correct?

> But the numbers don't seem to add up. Given those stats the estimate
> should be that something over 20% of the mm_insrel_table has to be
> scanned to complete the join (since 6070 falls into the third decile
> of the other histogram). But we saw from Alban's original post that
> the planner must be estimating well under 10% of the table needs to
> be scanned. Either we're still confused about which columns are being
> joined, or there's some weird bug in the computation.

Since the start of this thread the insrel table has grown to 339195
records (it was closer to 330,000), maybe that changed the statistics a
bit. To be sure, attached is the query plan of the problematic query
again at this moment.

The other table involved, mm_medical_care_container_table, has the
following stats on number:

zorgweb_solaris=> select * from pg_stats where attname = 'number' and
tablename = 'mm_medical_care_container_table';
-[ RECORD 1
]-----+-------------------------------------------------------------------------------
schemaname | public
tablename | mm_medical_care_container_table
attname | number
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds |
{418768,436686,455444,473600,490610,508680,527182,545038,562786,578528,595132}
correlation | 0.339138

I sure hope we get this mystery unveiled...

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 //