Re: slow select

"Medora Schauer" <mschauer(at)fairfield(dot)com> writes:

> Merge Join (cost=0.00..287726.10 rows=100221 width=58) (actual time=61.60..5975.63 rows=100425 loops=1)
> Merge Cond: (("outer".shot_line_num = "inner".shot_line_num) AND ("outer".shotpoint = "inner".shotpoint))
> -> Index Scan using hsot_record_idx on shot_record r (cost=0.00..123080.11 rows=100425 width=46) (actual time=24.15..2710.31 rows=100425 loops=1)
> -> Index Scan using shotpoint_idx on shotpoint p (cost=0.00..467924.54 rows=290106 width=12) (actual time=37.38..1379.64 rows=100749 loops=1)
> Total runtime: 6086.32 msec
>
> So why did were the indices not used before when they yield a better plan?

There's another reason. Notice it thinks the second table will return 290k
records. In fact it only returns 100k records. So it's optimizing on the
assumption that it will have to read 3x as many records as it actually will...

I'm not clear if there's anything you can do to improve this estimate though.

--
greg