| From: | "Andrus Moor" <eetasoft(at)online(dot)ee> |
|---|---|
| To: | pgsql-sql(at)postgresql(dot)org |
| Subject: | Query runs very slowly in Postgres, but very fast in other DBMS |
| Date: | 2005-04-11 21:43:10 |
| Message-ID: | d3er3k$2m2d$1@news.hub.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Joe Conway | 2005-04-11 22:26:22 | Re: PGCrypto: Realworld scenario and advice needed |
| Previous Message | Moran.Michael | 2005-04-11 21:18:03 | PGCrypto: Realworld scenario and advice needed |