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(at)aiidatapro(dot)com>
To: "Andrus Moor" <eetasoft(at)online(dot)ee>
Cc: <pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org)
>
>
>
> --
> ________________________________________________________
> Krasimir Dimitrov
> IT Department
> AII Data Processing Ltd.,
> 16 Ivan Vazov Str,
> Sofia 1000,
> Bulgaria
> Phone: +359 2 9376 352
> E-mail: kr(at)aiidatapro(dot)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