>> 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 I
Andrus Moor wrote:
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
ROTECTED]>
Cc:
Sent: Tuesday, April 12, 2005 12:33 AM
Subject: Re: [SQL] Query runs very slowly in Postgres, but very fast in
other DBMS
>
> try this query :
>
> SELECT rid.dokumnr as d1 ,dok.dokumnr as d2 FROM rid left join dok on
> rid.dokumnr = dok.dokumnr where dok.dokumnr is
try this query :
SELECT rid.dokumnr as d1 ,dok.dokumnr as d2 FROM rid left join dok on
rid.dokumnr = dok.dokumnr where dok.dokumnr is null;
> Tables:
>
> CREATE TABLE dok ( dokumnr NUMERIC(12),
> CONSTRAINT dok_pkey PRIMARY KEY (dokumnr) );
> CREATE TABLE rid ( dokumnr NUMERIC(12)
"Andrus Moor" <[EMAIL PROTECTED]> writes:
> Seq Scan on rid (cost=0.00..28698461.07 rows=32201 width=14)
> Filter: (NOT (subplan))
> SubPlan
> -> Seq Scan on dok (cost=0.00..864.29 rows=10729 width=14)
> Is it possible to speed up this query is Postgres ?
Can you switch to int4 or int8
Tables:
CREATE TABLE dok ( dokumnr NUMERIC(12),
CONSTRAINT dok_pkey PRIMARY KEY (dokumnr) );
CREATE TABLE rid ( dokumnr NUMERIC(12) );
CREATE INDEX rid_dokumnr_idx ON rid (dokumnr);
Query:
SELECT dokumnr FROM rid WHERE dokumnr NOT IN
(select dokumnr FROM dok);
runs VERY slowly in Po
