Re: improve 'where not exists' query..

Noel Whelan wrote:
> I executed the following:
>
> EXPLAIN ANALYZE SELECT cwit.cempid
> FROM "cwItems" cwit
> WHERE (NOT (EXISTS (SELECT con.cempid
> FROM contacts con
> WHERE (con.cempid = cwit.cempid))));
>
> It comes back with:
>
> Seq Scan on "cwItems" cwit (cost=0.00..8929702.11 rows=5132 width=8) (actual
> time=132218.29..148623.27 rows=31 loops=1)
> Filter: (NOT (subplan))
> SubPlan
> -> Seq Scan on contacts con (cost=0.00..870.77 rows=1 width=8) (actual time=
> 11.82..11.82 rows=1 loops=12528)
> Filter: (cempid = $0)
> Total runtime: 148623.54 msec
>
> I'm not certain it's an issue with the query itself going wrong. I consider
> I'm basically telling it: for each cempid in 'cwItems', check whether or not
> it exists as a cempid in 'contacts', which could be inherently inefficient
> in itself.

Well, as you say it's never going to be possible without checking all
items. However, you've got two seq-scans there and I'd expect a seq-scan
and looping over an index (or a hash-based plan perhaps).

Now, looking at the values it's only seeing 31 rows in cwItems and 1 row
(!) in contacts, so that would explain the scans. However, in that case
I'd expect it to be much faster than it is.

Hmm - I'd take the following steps:

1. VACUUM FULL VERBOSE ANALYSE <table> on both those tables and then see
what happens. Make a note of how many removable/non-removable rows it
finds. Rerun the explain analyse and see what happens.

2. Check that you have an index on contact.cempid and that the types of
cempid match in both tables. Then issue "SET enable_seqscan=true" and
run the explain analyse again - are things faster?

Let us know what happens, oh and don't forget to cc: the list, you were
lucky I read this.

> I just wondered if there'd be a way to improve on it or not.
> Installation is postgres-7.3.4.

Upgrade to the latest 7.3.x version as soon as is convenient - lots of
bug fixes to be had.

--
Richard Huxton
Archonet Ltd