=?UTF-8?B?0JTQvNC40YLRgNC40Lk=?= <fo...@ac-sw.com> writes: > Running PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC > gcc-4.6.real (Ubuntu/Linaro 4.6.0-7ubuntu1) 4.6.1, 64-bit > under Ubuntu 11.10.
> with uuu as ( > select dml.id > from mp_locs12 dml > where (complex conditions, leaving about 100 rows from millions) > ) > select label, country, region, parish, city, district, > st_geometrytype(loc::geometry) as gtype, > '0x' || to_hex(type) as n_type, file_name, line > from mp_locs12 dml1 > where dml1.id in (select uu.id from uuu uu) > and not exists ( > select 1 from mp_locs12 dml2 > where dml2.id in (select uu.id from uuu uu) > and dml2.id <> dml1.id > and not st_contains(dml1.loc::geometry, dml2.loc::geometry) > ); I think the reason the planner isn't too bright about this case is http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=cd1f0d04bf06938c0ee5728fc8424d62bcf2eef3 ie, it won't do IN/EXISTS pullup below a NOT EXISTS. HEAD is better, thanks to commit 0816fad6eebddb8f1f0e21635e46625815d690b9, but of course there is no chance at all of back-patching the planner changes that depends on. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
