Re: ANTI-JOIN needs table, index scan not possible?

On Fri, Mar 11, 2011 at 10:32 AM, hans wulf <lotu1(at)gmx(dot)net> wrote:
> I need an ANTI-JOIN (not exists SELECT something from table.../ left join table WHERE table.id IS NULL) on the same table. Acutally I have an index to serve the not exists question, but the query planner chooses to to a bitmap heap scan.
>
> The table has 100 Mio rows, so doing a heap scan is messed up...
>
> It would be really fast if Postgres could compare the to indicies. Does Postgres have to visit the table for this ANTI-JOIN?

A bitmap heap scan implies that a bitmap index scan is also being
done, so it IS using the indexes. Now that leaves open the question
of why it's not fast... but it's hard to guess the answer to that
question without seeing at least the EXPLAIN output, preferably
EXPLAIN ANALYZE.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company