Re: Queries joining views

Tom Lane wrote:
> DelGurth <delgurth(at)gmail(dot)com> writes:
>> On 8/21/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> It might be interesting also to examine the output of just
>>> explain select * from mm_insrel_table where dnumber=558332 and dir<>1
>>> with different subsets of these indexes in place.
>
>> Ok. Did that (with your trick, thanks!). The output is attached to
>> this e-mail.
>
> Well, no smoking gun there, it clearly knows that mm_insrel_full_idx
> is much more expensive for this query than the other two...
>
> Looking back at Alban's original post, I finally see what the planner
> is up to:
>
> -> Merge Join (cost=0.00..165.07 rows=1 width=28) (actual time=53.890..129.310 rows=1 loops=1)
> Merge Cond: ("outer".number = "inner".number)
> -> Nested Loop (cost=0.00..2796.82 rows=30 width=28) (actual time=44.088..117.487 rows=2 loops=1)
> -> Nested Loop (cost=0.00..2682.38 rows=30 width=24) (actual time=44.034..117.375 rows=2 loops=1)
> -> Index Scan using mm_insrel_full_idx on mm_insrel_table (cost=0.00..2512.97 rows=30 width=20) (actual time=43.975..117.246 rows=2 loops=1)
> Index Cond: (dnumber = 558332)
>
> The reason it's choosing this indexscan is that that will give it data
> sorted by mm_insrel_table.number, which it can feed into the mergejoin
> without an extra sort step. Now sorting 30 rows is not going to take
> nearly as much time as the indexscan eats up, so this still doesn't
> make sense --- until you notice that it's estimating the top merge join
> at considerably less than the cost of its inputs (165.07, vss 2796.82
> just for this input). That means it thinks it won't have to run the
> inputs to completion in order to finish the mergejoin, and so it's
> picking a sub-plan that has zero start cost.

I see. Obviously that's not right for our case, so we'll have to figure
out why it thinks that.

> What this means is that the planner thinks the range of "number" values
> in mm_product_table (the other side of the mergejoin) is much less than
> the range in mm_insrel_table. Is that the case? Perhaps your ANALYZE

Very much so. The mm_product_table only contains about 1500 products,
while the mm_insrel_table contains a record for every relation in the
application (This is MMBase; it handles all relations with a relation
table). That's about 330,000 records.

> stats for these tables are out of date. If not I'd like to see the
> pg_stats entries for the two "number" columns.

Especially during optimization sessions like this one we tend to analyse
rather frequently. All these indices were created yesterday (except for
the primary key index) and the corresponding tables were analyzed after
each index creation (aboutish). Data hasn't changed since a while -
we're in the middle of a test migration from mysql[1].

As for the stats, I included the one for mm_object as well, as every
view contains at least a join with that table - thus it contains 1284556
records... I suspect we're in for another few painful surprises there.

zorgweb_solaris=> select * from pg_stats where attname = 'number' and
tablename IN ('mm_insrel_table', 'mm_product_table', 'mm_object');
-[ 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 | number
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds |
{615920,689286,750855,812003,872741,933041,1004672,1068250,1134894,1198559,1261685}
correlation | 0.664637
-[ RECORD 3
]-----+------------------------------------------------------------------------------------
schemaname | public
tablename | mm_object
attname | number
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds |
{287,124412,256534,375896,505810,643940,770327,899229,1028933,1153260,1262338}
correlation | 0.858558

As a side note, I'm in the progress of rewriting MMBase code to use
explicit joins where applicable. AFAIK the planner can handle those better.

> regards, tom lane

[1]: With MySQL(4) there was no way for us to enhance performance any
more. There are a number of tree-like structures in our data model, and
MySQL just lacks the features to cope with that. With PostgreSQL we at
least can make use of the ltree contrib package - quite an improvement
in performance so far. Even though the ltrees are applied on text
columns with text2ltree() conversions in both the queries and the GIST
index.

We also figured we could use some triggers to generate data that could
improve query performance (moving conversions from SELECT-time to
INSERT-time), but unfortunately MMBase's caches are in the way there.

Regards, Alban.
--
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 //