From: Tom Lane <tgl@sss.pgh.pa.us>
To: Дмитрий Васильев <dmitry.vasil@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad query plan
Date: Sun, 24 Jul 2011 12:02:24 -0400
Message-ID: <12708.1311523344@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKNQRbz8-XYcSmoK8u3S0BvVVk3OdXve7NG20eNwB5p5xms8MQ@mail.gmail.com>
References: <CAKNQRbz8-XYcSmoK8u3S0BvVVk3OdXve7NG20eNwB5p5xms8MQ@mail.gmail.com>
=?KOI8-R?B?5M3J1NLJyiD3wdPJzNjF1w==?= <dmitry.vasil@gmail.com> writes:
> explain analyze insert into large(id) select id from small where id
> not in(select id from large);
> [ crummy plan ]
> explain analyze insert into large(id) select id from small where not
> exists (select id from large l where small.id=l.id);
> [ better plan ]
> Both queries are semantically the same.
No, they are not. NOT IN is hard to optimize because it has strange
behaviors with nulls in the data. Use the NOT EXISTS formulation.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, dmitry.vasil@gmail.com
Subject: Re: Bad query plan
In-Reply-To: <12708.1311523344@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox