Re: why sequential scan is used on indexed column ???

On Sat, Jun 14, 2008 at 02:35:38PM -0400, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > I created a test case that has close to the same estimated and
> > actual row counts and has the same plan if I disable enable_nestloop:
>
> There's something weird about this --- why does the second plan seqscan
> b_saskaita, instead of using the bitmap scan that it had previously
> estimated to be cheaper?

Dunno.

> What PG version are you testing, and can you provide the full test case?

My test was in 8.2.9, the only version I had handy at the time. I
later tested 8.1.13 (Julius said he was running 8.1.4) and got the
same plan that Julius got without messing with planner settings.

I don't have access to my test case right now but I'll post it when
I get a chance. I simply populated the tables with random data,
adjusting the amount and distribution until I got row count estimates
close to what Julius got. I don't know if my test case is close
enough to Julius's data to be relevant to his problem but if you think
my results are weird then maybe I've stumbled across something else
that's interesting.

> (As for the original question, the hash plan seems to me to be perfectly
> reasonable for the estimated row counts --- fetching one row out of
> fifty using an indexscan is going to be expensive. So I think the OP's
> problem is purely a statistical one, or maybe he's in a situation where
> he should reduce random_page_cost.)

Hmmm...8.1.13 wants to do the hash join that you think would be
reasonable but 8.2.9 prefers the nested loop as in my second example.
I think I did have a reduced random_page_cost (2 as I recall).

--
Michael Fuhr