>> 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 FR
On Mon, Aug 02, 2010 at 01:35:13PM -0700, Maciek Sakrejda wrote:
> >> All fields involved are declared NOT NULL, but thanks for the heads up.
> >Afair the planner doesnt use that atm.
>
> I was referring to not having to care about the strange NULL semantics
> (as per your original comment), since
Kevin Grittner wrote:
> Maybe before I get excited I should try it with a query which is
> actually logically equivalent.
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 <>
"Kevin Grittner" wrote:
> DELETE FROM foo
> where foo.b in (
> select b from foo WHERE type = 'o'
> except SELECT b FROM bar
> except SELECT b FROM foo where type <> 'o');
Oops. Maybe before I get excited I should try it with a query which
is actually logically equivalent. :-(
Dave Crooke wrote:
> With Oracle, I've found an anti-union (MINUS in Oracle, EXCEPT in
> PGSQL) to be often a bit better than an anti-join, which is in
> turn faster than NOT IN. Depends of course on row distribution and
> index layouts, and a bunch of other details.
I found that assertion int
>> All fields involved are declared NOT NULL, but thanks for the heads up.
>Afair the planner doesnt use that atm.
I was referring to not having to care about the strange NULL semantics
(as per your original comment), since I have no NULLs. Given that, I
think the NOT EXISTS could be a good soluti
On Mon, Aug 02, 2010 at 01:06:00PM -0700, Maciek Sakrejda wrote:
> All fields involved are declared NOT NULL, but thanks for the heads up.
Afair the planner doesnt use that atm.
Andres
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscript
With Oracle, I've found an anti-union (MINUS in Oracle, EXCEPT in PGSQL) to
be often a bit better than an anti-join, which is in turn faster than NOT
IN. Depends of course on row distribution and index layouts, and a bunch of
other details.
Depending on what you're returning, it can pay to make su
All fields involved are declared NOT NULL, but thanks for the heads up.
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
(650) 242-3500 Main
www.truviso.com
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To m
Hi,
On Mon, Aug 02, 2010 at 12:12:51PM -0700, Maciek Sakrejda wrote:
> I'm running PostgreSQL 8.3 and I have a query with a couple of NOT IN
> subqueries:
With 8.3 you will have to use manual antijoins (i.e LEFT JOIN
... WHERE NULL). If you use 8.4 NOT EXISTS() will do that
automatically in many c
Maciek Sakrejda wrote:
> No NULLs. It looks like it's a good deal slower than the LOJ
> version, but a good deal faster than the original.
On 8.4 and later the NOT EXISTS I suggested is a bit faster than
your fast version, since Tom did some very nice work in this area,
implementing semi join
> Can "b" be null in any of these tables? If not, then you can
> rewrite your query to us NOT EXISTS and have the same semantics.
> That will often be much faster.
Thanks, Kevin.
No NULLs. It looks like it's a good deal slower than the LOJ version,
but a good deal faster than the original. Since
Maciek Sakrejda wrote:
> DELETE FROM foo WHERE type = 'o' AND b NOT IN (SELECT cqc.b FROM
> bar cqc) AND b NOT IN (SELECT car.b FROM foo car WHERE car.type !=
> 'o');
Can "b" be null in any of these tables? If not, then you can
rewrite your query to us NOT EXISTS and have the same semantics.
Hi,
I'm running PostgreSQL 8.3 and I have a query with a couple of NOT IN
subqueries:
DELETE FROM foo WHERE type = 'o' AND b NOT IN (SELECT cqc.b FROM bar
cqc) AND b NOT IN (SELECT car.b FROM foo car WHERE car.type != 'o');
The plan produced for this is:
14 matches
Mail list logo
