From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Дмитрий <fozzy(at)ac-sw(dot)com> |
Cc: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Postgres 8.4 planner question - bad plan, good plan for almost same queries. |
Date: | 2012-03-20 15:43:40 |
Message-ID: | 26101.1332258220@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
=?UTF-8?B?0JTQvNC40YLRgNC40Lk=?= <fozzy(at)ac-sw(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-03-20 15:50:53 | Re: vacuumlo issue |
Previous Message | Jeff Janes | 2012-03-20 15:29:45 | Re: Memory usage during sorting |