Re: Specifying many rows in a table

Steve Atkins <steve@blighty.com> writes:
> An interesting thing, though is that for 10,000,000 rows in big and
> one row in little, everything indexed and analyzed an exists query
> is painfully bad...

>   select * from big where exists (select 1 from little where s=big.id)

> ...expands to nested seqscans over big and little, rather than the
> seqscan over little with a nested index scan over big I was expecting.

The planner can figure this out for straight join and (as of 7.4) for
IN, but not for EXISTS().  I've forgotten the details, but there were
some semantic fine points that discouraged me from trying to tackle the
EXISTS() case at the same time as IN.

            regards, tom lane