Re: Query runs very slowly in Postgres, but very fast in other DBMS

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" <kr@aiidatapro.com>
To: "Andrus Moor" <eetasoft@online.ee>
Cc: <pgsql-sql@postgresql.org>
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 null;
>
>
> > 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 Postgres. It uses the following query plan:
> >
> > 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)
> >
> > In Microsoft Visual FoxPro this query runs fast. FoxPro uses indexes
speed
> > up the query by comparing bitmaps.
> >
> > Is it possible to speed up this query is Postgres ? How to force
Postgres
> > to use indexes for this query ?
> >
> > Andrus
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>
> -- 
> ________________________________________________________
> Krasimir Dimitrov
> IT Department
> AII Data Processing Ltd.,
> 16 Ivan Vazov Str,
> Sofia 1000,
> Bulgaria
> Phone: +359 2 9376 352
> E-mail: kr@aiidatapro.com
> http://www.see-news.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match