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) );
You might also try:
SELECT dokumnr FROM rid WHERE NOT EXISTS ( SELECT 'd'
FROM dok
WHERE dok.dokumnr = rid.dokumnr );
Dan Feiveson
DataJoe LLC
- Original Message -
From: Krasimir Dimitrov [EMAIL PROTECTED]
To: Andrus Moor [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Sent:
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
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
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
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 instead