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

От:

"Dan Feiveson" <danf@datajoe.com>

Дата:

13 апреля 2005 г. в 01:25:17

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" 
To: "Andrus Moor" 
Cc: 
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

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

От:

Krasimir Dimitrov <kr@aiidatapro.com>

Дата:

12 апреля 2005 г. в 03:33:24


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

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

От:

Ezequiel Tolnay <mail@etolnay.com.ar>

Дата:

13 апреля 2005 г. в 01:30:49

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 IN) for larger sets. e.g.:

SELECT rid.dokumnr
FROM rid
LEFT JOIN dok ON (dok.dokumnr = rid.dokumnr)
WHERE dok.dokumnr iS NULL;

Cheers,

Ezequiel Tolnay

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

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

12 апреля 2005 г. в 02:43:22

"Andrus Moor"  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 of NUMERIC(12)?  I think that
we don't currently consider NUMERIC hashable, and you really need a
hash subplan here.  In general NUMERIC is a bad choice if you're hot
about performance, anyway.  Integers or floats would be probably two
orders of magnitude faster.
		regards, tom lane

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

От:

"Andrus Moor" <eetasoft@online.ee>

Дата:

12 апреля 2005 г. в 01:30:12

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

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

От:

"Andrus" <noeetasoftspam@online.ee>

Дата:

13 апреля 2005 г. в 01:25:03

>> 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 larger sets. e.g.:
>
> SELECT rid.dokumnr
> FROM rid
> LEFT JOIN dok ON (dok.dokumnr = rid.dokumnr)
> WHERE dok.dokumnr iS NULL;

Thank you.
How to use this technique to speed up the update statement

UPDATE rid SET dokumnr=NULL WHERE  dokumnr NOT IN (SELECT dokumnr FROM dok);

and DELETE statement

DELETE FROM rid WHERE  dokumnr NOT IN (SELECT dokumnr FROM dok);

Andrus