Re: Forcing seq_scan off for large table joined with tiny table yeilds improved performance

On Mon, Mar 30, 2009 at 9:34 AM, Mario Splivalo
<mario(dot)splivalo(at)megafon(dot)hr> wrote:

>         ->  Bitmap Heap Scan on photo_info_data u (cost=2193.50..26798.74
> rows=109024 width=9) (actual time=0.025..0.030 rows=3 loops=2)
>               Recheck Cond: ((u.field_name)::text = (t.key)::text)
>               ->  Bitmap Index Scan on photo_info_data_ix__field_name
>  (cost=0.00..2166.24 rows=109024 width=0) (actual time=0.019..0.019 rows=3
> loops=2)

> So, I guess I solved my problem! :) The explain analyze still shows that row
> estimate is 'quite off' (109024 estimated vs only 3 actuall), but the query
> is light-speeded :)

It's not really solved, it's just a happy coincidence that the current
plan runs well. In order to keep the query planner making good
choices you need to increase stats target for the field in the index
above. The easiest way to do so is to do this:

alter database mydb set default_statistics_target=100;

and run analyze again:

analyze;

> I tought that having primary key (and auto-index because of primary key) on
> (photo_id, field_name) should be enough. Now I have two indexes on
> field_name, but that seems to do good.

Nope, it's about the stats collected that let the planner make the right choice.