Обсуждение: Query runs very slowly in Postgres, but very fast in other DBMS
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
"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
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
>> 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
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
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