From: Andrus <noeetasoftspam@online.ee>
To: pgsql-sql@postgresql.org
Subject: Re: Query runs very slowly in Postgres, but very fast in other DBMS
Date: Tue, 12 Apr 2005 13:09:42 +0300
Message-ID: <d3g6pg$nik$1@news.hub.org> (raw)
References: <d3er3k$2m2d$1@news.hub.org>
<425B5E66.8080905@etolnay.com.ar>
>> SELECT dokumnr FROM rid WHERE dokumnr NOT IN
>> (select dokumnr FROM dok);
> ...
>> Is it possible to speed up this query is Postgres ? How to force Postgres
>> to use indexes for this query ?
>
> Use IN and NOT IN only for small sets. Use JOIN (instead of IN) and LEFT
> JOIN (instead of NOT IN) for larger sets. e.g.:
>
> SELECT rid.dokumnr
> FROM rid
> LEFT JOIN dok ON (dok.dokumnr = rid.dokumnr)
> WHERE dok.dokumnr iS NULL;
Thank you.
How to use this technique to speed up the update statement
UPDATE rid SET dokumnr=NULL WHERE
dokumnr NOT IN (SELECT dokumnr FROM dok);
and DELETE statement
DELETE FROM rid WHERE dokumnr NOT IN (SELECT dokumnr FROM dok);
Andrus
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-sql@postgresql.org
Cc: noeetasoftspam@online.ee
Subject: Re: Query runs very slowly in Postgres, but very fast in other DBMS
In-Reply-To: <d3g6pg$nik$1@news.hub.org>
* 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