Re: Optimizing NOT IN plans / verify rewrite

>> Maybe before I get excited I should try it with a query which is
>> actually logically equivalent.

Yes, the joys of manual rewrites...

> Fixed version:
>
> DELETE FROM foo
> where type = 'o' and foo.b in (
> select b from foo WHERE type = 'o'
> except SELECT b FROM bar
> except SELECT b FROM foo where type <> 'o');
>
> The change didn't affect run time significantly; it still beats the
> others.

On my 8.3, it still performs a little worse than your original
correlated EXCEPT (which is actually on par with the antijoin in 8.3,
but significantly better in 8.4). In 8.4, this EXCEPT version does
seem somewhat better.

It looks like according to Andres, though, I should not be depending
on these plans with 8.3, so I may want to stick with the manual
antijoin.

---
Maciek Sakrejda | System Architect | Truviso

1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com