Tom Lane wrote:
Alban Hertroys <alban@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx
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 //
zorgweb_solaris=> explain analyze SELECT
zorgweb_solaris-> insrel.rnumber,insrel.dnumber,insrel.snumber,insrel.dir,insrel.number,medical_care_container.number,product.number
zorgweb_solaris-> FROM mm_medical_care_container medical_care_container,mm_insrel
zorgweb_solaris-> insrel,mm_product product WHERE medical_care_container.number=558332 AND
zorgweb_solaris-> (medical_care_container.number=insrel.dnumber AND
zorgweb_solaris(> product.number=insrel.snumber AND insrel.dir<>1);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..178.61 rows=1 width=28) (actual time=56.089..137.304 rows=1 loops=1)
-> Nested Loop (cost=0.00..174.67 rows=1 width=28) (actual time=56.041..137.250 rows=1 loops=1)
-> Merge Join (cost=0.00..170.73 rows=1 width=28) (actual time=55.986..137.189 rows=1 loops=1)
Merge Cond: ("outer".number = "inner".number)
-> Nested Loop (cost=0.00..2966.75 rows=30 width=28) (actual time=46.161..125.315 rows=2 loops=1)
-> Nested Loop (cost=0.00..2848.50 rows=30 width=24) (actual time=46.108..125.205 rows=2 loops=1)
-> Index Scan using mm_insrel_full_idx on mm_insrel_table (cost=0.00..2678.20 rows=30 width=20) (actual time=46.042..125.067 rows=2 loops=1)
Index Cond: (dnumber = 558332)
-> Index Scan using mm_medical_care_container_table_pkey on mm_medical_care_container_table (cost=0.00..5.67 rows=1 width=4) (actual time=0.048..0.049 rows=1 loops=2)
Index Cond: (558332 = number)
-> Index Scan using mm_object_pkey on mm_object (cost=0.00..3.93 rows=1 width=4) (actual time=0.041..0.043 rows=1 loops=2)
Index Cond: (mm_object.number = "outer".snumber)
-> Index Scan using mm_product_table_pkey on mm_product_table (cost=0.00..67.90 rows=1571 width=4) (actual time=0.024..9.462 rows=1571 loops=1)
-> Index Scan using mm_object_pkey on mm_object (cost=0.00..3.93 rows=1 width=4) (actual time=0.043..0.045 rows=1 loops=1)
Index Cond: ("outer".number = mm_object.number)
-> Index Scan using mm_object_pkey on mm_object (cost=0.00..3.93 rows=1 width=4) (actual time=0.038..0.041 rows=1 loops=1)
Index Cond: (number = 558332)
Total runtime: 138.132 ms
(18 rows)
