pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery runs very slowly in Postgres, but very fast in other DBMS
6+ messages / 6 participants
[nested] [flat]
* Query runs very slowly in Postgres, but very fast in other DBMS
@ 2005-04-11 21:43 Andrus Moor <eetasoft@online.ee>
0 siblings, 3 replies; 6+ messages in thread
From: Andrus Moor @ 2005-04-11 21:43 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query runs very slowly in Postgres, but very fast in other DBMS
@ 2005-04-12 05:36 Ezequiel Tolnay <mail@etolnay.com.ar>
parent: Andrus Moor <eetasoft@online.ee>
2 siblings, 1 reply; 6+ messages in thread
From: Ezequiel Tolnay @ 2005-04-12 05:36 UTC (permalink / raw)
To: Andrus Moor <eetasoft@online.ee>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query runs very slowly in Postgres, but very fast in other DBMS
@ 2005-04-12 05:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andrus Moor <eetasoft@online.ee>
2 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2005-04-12 05:43 UTC (permalink / raw)
To: Andrus Moor <eetasoft@online.ee>; +Cc: pgsql-sql
"Andrus Moor" <eetasoft@online.ee> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query runs very slowly in Postgres, but very fast in other DBMS
@ 2005-04-12 06:33 Krasimir Dimitrov <kr@aiidatapro.com>
parent: Andrus Moor <eetasoft@online.ee>
2 siblings, 1 reply; 6+ messages in thread
From: Krasimir Dimitrov @ 2005-04-12 06:33 UTC (permalink / raw)
To: Andrus Moor <eetasoft@online.ee>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query runs very slowly in Postgres, but very fast in other DBMS
@ 2005-04-12 10:09 Andrus <noeetasoftspam@online.ee>
parent: Ezequiel Tolnay <mail@etolnay.com.ar>
0 siblings, 0 replies; 6+ messages in thread
From: Andrus @ 2005-04-12 10:09 UTC (permalink / raw)
To: pgsql-sql
>> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query runs very slowly in Postgres, but very fast in other DBMS
@ 2005-04-12 15:18 Dan Feiveson <danf@datajoe.com>
parent: Krasimir Dimitrov <kr@aiidatapro.com>
0 siblings, 0 replies; 6+ messages in thread
From: Dan Feiveson @ 2005-04-12 15:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-04-12 15:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-11 21:43 Query runs very slowly in Postgres, but very fast in other DBMS Andrus Moor <eetasoft@online.ee>
2005-04-12 05:36 ` Ezequiel Tolnay <mail@etolnay.com.ar>
2005-04-12 10:09 ` Andrus <noeetasoftspam@online.ee>
2005-04-12 05:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-12 06:33 ` Krasimir Dimitrov <kr@aiidatapro.com>
2005-04-12 15:18 ` Dan Feiveson <danf@datajoe.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox