Re: Queries joining views

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.

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
stats for these tables are out of date. If not I'd like to see the
pg_stats entries for the two "number" columns.

regards, tom lane