Re: [NEWBIE] need help optimizing this query

Bill Moran <wmoran@potentialtech.com> writes:
> 3) Do you have indexes on a.xmax and b.transaction?

He can't index either (xmax is simply not indexable, and pg_locks is a view).

In a quick experiment I got reasonable-seeming join plans; the output of
pg_locks got hashed and then the system did a seqscan over the outer
table.  It's not possible to do any better than that with the problem
as given.  I assume the OP's problem is that the outer table is big and
he doesn't want to seqscan it.  The only way I can see is to add an
additional filter condition that can be indexed, so that not all the
rows in the outer table have to be checked for xmax.

BTW, in 7.4 you get equivalently good plans with the more transparent

explain select * from foo where xmax not in
(select transaction from pg_locks where transaction is not null);

The EXPLAIN output looks different, but it's still effectively a hash
join.

            regards, tom lane