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

Поиск
Список
Период
Сортировка
От Andrus Moor
Тема Query runs very slowly in Postgres, but very fast in other DBMS
Дата
Msg-id d3er3k$2m2d$1@news.hub.org
обсуждение исходный текст
Ответы Re: Query runs very slowly in Postgres, but very fast in other DBMS  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Query runs very slowly in Postgres, but very fast in other DBMS  (Krasimir Dimitrov <kr@aiidatapro.com>)
Re: Query runs very slowly in Postgres, but very fast in other DBMS  (Ezequiel Tolnay <mail@etolnay.com.ar>)
Список 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.29rows=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 




В списке pgsql-sql по дате отправления:

Предыдущее
От: Joe Conway
Дата:
Сообщение: Re: PGCrypto: Realworld scenario and advice needed
Следующее
От: "Dan Feiveson"
Дата:
Сообщение: OpenFTS