Обсуждение: Query runs forever after upgrading to 9.3

Query

SELECT * FROM toode
WHERE toode in (SELECT toode FROM tempkaive)
     OR toode in (SELECT toode FROM tempalgsemu)

stops working after upgrading to 9.3 RTM in Windows from earlier version.

Task Manager shows that postgres.exe process has constantly 13% CPU usage
(this is 8 core computer) and private working set memory is 16 MB

PgAdmin shows that this query is running .

toode field type is char(20) and it is toode table primary key.

tempkaive and tempalgsemu are temporary tables created eralier this
transaction. They do not have indexes.
toode is real table which has 509873 records .
Probably tempkaive temp table size is bigger that toode table and
templalgemu temp table size is smaller than in toode.

How to fix this or find the reason ?
How to rewrite the query so that it works ?

analyze command was executed but problem persists.
I tested it running Postgres 9.3 RTM in 32 bin Windows 7 and  64 bit Windows
2008 R2 servers.
In both cases same problem occurs.
Only single user is using database and only this query is running.


Locks window shows:

7840    toy    53749    admin        7/13375    AccessShareLock    Yes
2013-09-23 15:57:08+03    SELECT * FROM toode WHERE toode in (SELECT toode
FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840    toy    53652    admin        7/13375    AccessShareLock    Yes
2013-09-23 15:57:08+03    SELECT * FROM toode WHERE toode in (SELECT toode
FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840    toy    54605    admin        7/13375    AccessShareLock    Yes
2013-09-23 15:57:08+03    SELECT * FROM toode WHERE toode in (SELECT toode
FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840    toy    54608    admin        7/13375    AccessShareLock    Yes
2013-09-23 15:57:08+03    SELECT * FROM toode WHERE toode in (SELECT toode
FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840    toy    49799    admin        7/13375    AccessShareLock    Yes
2013-09-23 15:57:08+03    SELECT * FROM toode WHERE toode in (SELECT toode
FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840            admin    7/13375    7/13375    ExclusiveLock    Yes
2013-09-23 15:57:08+03    SELECT * FROM toode WHERE toode in (SELECT toode
FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)
7840    toy    53750    admin        7/13375    AccessShareLock    Yes
2013-09-23 15:57:08+03    SELECT * FROM toode WHERE toode in (SELECT toode
FROM tempkaive)OR toode in (SELECT toode FROM tempalgsemu)

Andrus.

Re: Andrus 2013-09-23 <E04C65FDEE80430DB6499621E2EC36BC@dell2>
> SELECT * FROM toode
> WHERE toode in (SELECT toode FROM tempkaive)
>     OR toode in (SELECT toode FROM tempalgsemu)

Generally, WHERE IN (SELECT) should be rewritten as WHERE EXISTS
(SELECT):

SELECT * FROM toode o
WHERE EXISTS (SELECT toode FROM tempkaive i WHERE o.toode = i.toode)
    OR EXISTS (SELECT toode FROM tempalgsemu i WHERE o.toode = i.toode)

Also, ANALYZEing the tables after the upgrade might help if this has
not yet been done.

Christoph
--
cb@df7cb.de | http://www.df7cb.de/

Hi,

Thank you.

>Generally, WHERE IN (SELECT) should be rewritten as WHERE EXISTS
>(SELECT):
>SELECT * FROM toode o
>WHERE EXISTS (SELECT toode FROM tempkaive i WHERE o.toode = i.toode)
>    OR EXISTS (SELECT toode FROM tempalgsemu i WHERE o.toode = i.toode)

I re-wrote it. It now hangs in this line

SELECT * FROM toode o WHERE  exists (SELECT toode FROM tempkaive i where
o.toode = i.toode ) OR EXISTS (SELECT toode FROM tempalgsemu i WHERE o.toode
= i.toode)

I wait 18 minutes but query is still running.

Maybe it will take extremely long time.
How to make it work  ?

toode table structure is below. It contains 509873 records .
tempkaive and tempalgsemu are big temporary tables created earlier this
transaction. They do not have indexes and have lot of records.

Andrus.

CREATE TABLE firma1.toode
(
  grupp character(1),
  toode character(20) NOT NULL,
  ribakood character(20),
  ribakood2 character(20),
  ribakood3 character(20),
  nimetus character(50),
  yhik character(6),
  myygikood character(4),
  tykke numeric(9,2),
  liik character(10),
  kontonr character(10),
  engnimetus character(50),
  rusnimetus character(50),
  finnimetus character(50),
  lvlnimetus character(50),
  markused text,
  myygihind numeric(15,5),
  jaehind numeric(15,2),
  katteprots numeric(6,2),
  paritoluri character(2),
  ostuhind numeric(15,5),
  valmyygih numeric(15,5),
  valraha character(3),
  ovalraha character(3),
  aktsiis numeric(10,5),
  kogpak numeric(9,4) NOT NULL DEFAULT 0,
  soodkogus numeric(8,1),
  vaikkogus numeric(12,4),
  hinne numeric(8,2),
  yhikuteise numeric(9,4),
  norm numeric(8,4),
  soetaeg date,
  soetarve character(25),
  algmaksumu numeric(12,2),
  kasutaja character(12),
  kulum character(10),
  kulukonto character(10),
  oper character(3),
  objekt1 character(10),
  objekt2 character(10),
  objekt3 character(10),
  objekt4 character(10),
  objekt5 character(10),
  objekt6 character(10),
  objekt7 character(10),
  objekt8 character(10),
  objekt9 character(10),
  parimenne date,
  asukoht character(25),
  minkogus numeric(12,4),
  masin character(5),
  ryhm character(10),
  klass character(5),
  kaubasumma text,
  tasusumma text,
  pangateen ebool,
  analoog character(20),
  taara character(20),
  taara2 character(20),
  taarakaal numeric(9,5),
  taara2kaal numeric(9,5),
  hankija character(12),
  hinnak character(5),
  eelminekuu ebool,
  distribute ebool,
  plaanhind numeric(15,5),
  "timestamp" character(14) NOT NULL DEFAULT to_char(now(),
'YYYYMMDDHH24MISS'::text),
  atimestamp character(14) NOT NULL DEFAULT to_char(now(),
'YYYYMMDDHH24MISS'::text),
  username character(10),
  changedby character(10),
  kgasuvi numeric(2,0),
  ktasuvi numeric(2,0),
  kgatalv numeric(2,0),
  ktatalv numeric(2,0),
  kylmik numeric(2,0),
  tkmkoef numeric(3,1),
  paak numeric(4,0),
  kassakeeld ebool,
  kaalukaup ebool,
  saadakaalu ebool,
  sailivusae numeric(2,0),
  kaubakood character(10),
  netomass numeric(12,4),
  seisund character(1),
  tootjakood character(40),
  klassif3 numeric(7,0),
  prots1 numeric(6,2),
  prots2 numeric(6,2),
  prots3 numeric(6,2),
  ale1 numeric(8,2),
  ale2 numeric(8,2),
  ale3 numeric(8,2),
  tootja character(10),
  soomes numeric(12,4),
  originaal character(20),
  eekjaehind numeric(15,2),
  amordipiir numeric(12,2),
  pant character(20),
  hulgihind numeric(12,2),
  transportw ebool,
  tykke2 numeric(9,2),
  tootjaviit character(40),
  CONSTRAINT toode_pkey PRIMARY KEY (toode),
  CONSTRAINT toode_changedby_fkey FOREIGN KEY (changedby)
      REFERENCES kasutaja (kasutaja) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_kasutaja_fkey FOREIGN KEY (kasutaja)
      REFERENCES firma1.klient (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_kaubakood_fkey FOREIGN KEY (kaubakood)
      REFERENCES nomenkla (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_kontonr_fkey FOREIGN KEY (kontonr)
      REFERENCES firma1.konto (kontonr) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_kulukonto_fkey FOREIGN KEY (kulukonto)
      REFERENCES firma1.konto (kontonr) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_kulum_fkey FOREIGN KEY (kulum)
      REFERENCES firma1.konto (kontonr) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_liik_fkey FOREIGN KEY (liik)
      REFERENCES firma1.artliik (liik) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT toode_myygikood_fkey FOREIGN KEY (myygikood)
      REFERENCES firma1.myygikoo (myygikood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_objekt1_fkey FOREIGN KEY (objekt1)
      REFERENCES firma1.yksus1 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_objekt2_fkey FOREIGN KEY (objekt2)
      REFERENCES firma1.yksus2 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_objekt3_fkey FOREIGN KEY (objekt3)
      REFERENCES firma1.yksus3 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_objekt4_fkey FOREIGN KEY (objekt4)
      REFERENCES firma1.yksus4 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_objekt5_fkey FOREIGN KEY (objekt5)
      REFERENCES firma1.yksus5 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_objekt7_fkey FOREIGN KEY (objekt7)
      REFERENCES firma1.yksus7 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_objekt8_fkey FOREIGN KEY (objekt8)
      REFERENCES firma1.yksus8 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_objekt9_fkey FOREIGN KEY (objekt9)
      REFERENCES firma1.yksus9 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_oper_fkey FOREIGN KEY (oper)
      REFERENCES alamdok (oper) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_ovalraha_fkey FOREIGN KEY (ovalraha)
      REFERENCES raha (raha) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_paritoluri_fkey FOREIGN KEY (paritoluri)
      REFERENCES riik (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_ryhm_fkey FOREIGN KEY (ryhm)
      REFERENCES firma1.artryhm (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_seisund_fkey FOREIGN KEY (seisund)
      REFERENCES artstaat (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_taara2_fkey FOREIGN KEY (taara2)
      REFERENCES firma1.toode (toode) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_taara_fkey FOREIGN KEY (taara)
      REFERENCES firma1.toode (toode) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_username_fkey FOREIGN KEY (username)
      REFERENCES kasutaja (kasutaja) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_valraha_fkey FOREIGN KEY (valraha)
      REFERENCES raha (raha) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_yhik_fkey FOREIGN KEY (yhik)
      REFERENCES firma1.mootyhik (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT toode_grupp_check CHECK (grupp = ANY (ARRAY['L'::bpchar,
'P'::bpchar, 'V'::bpchar, 'S'::bpchar]))
)
WITH (
  OIDS=FALSE
);

CREATE INDEX toode_toode_pattern_idx
  ON firma1.toode
  USING btree
  (toode COLLATE pg_catalog."default" bpchar_pattern_ops);

CREATE UNIQUE INDEX toode_toode_unique_pattern_idx
  ON firma1.toode
  USING btree
  (upper(toode::text) COLLATE pg_catalog."default" text_pattern_ops);


>Also, ANALYZEing the tables after the upgrade might help if this has
>not yet been done.

analyze was ran in this database.

Andrus.

"Andrus" <kobruleht2@hot.ee> writes:
> Query
> SELECT * FROM toode
> WHERE toode in (SELECT toode FROM tempkaive)
>      OR toode in (SELECT toode FROM tempalgsemu)

> stops working after upgrading to 9.3 RTM in Windows from earlier version.

Just out of curiosity, what "earlier version" was that that was able to
run this query quickly?  Based on what you've said in this thread, I don't
see a reason for 9.3 to be slower than earlier releases for this.  The
default plan certainly sucks, but that would've been true in earlier
releases as well.

Personally I'd try to get rid of the OR, perhaps with

SELECT * FROM toode
WHERE toode in (SELECT toode FROM tempkaive UNION ALL
                SELECT toode FROM tempalgsemu)

You want to end up with a plan that has no "SubPlans" in it, and in
a quick check this looked promising.

            regards, tom lane

Hi,

>Just out of curiosity, what "earlier version" was that that was able to
>run this query quickly?

It was installed in customer site at May 2012 in Windows 2003 server and
latest RTM version of Postgres
x32 in this time was used.

In this year server was upgraded to Windows 2008 x64 server and Postgres 9.3
x64 was used, database was restored from backup copy.
After that this query started to run forever so I assumed that this was
Postgres version issue.

It is probably possible to try to reproduce the issue by restoring it to
earlier version.

>Personally I'd try to get rid of the OR, perhaps with
>SELECT * FROM toode
>WHERE toode in (SELECT toode FROM tempkaive UNION ALL
>                SELECT toode FROM tempalgsemu)
>You want to end up with a plan that has no "SubPlans" in it, and in
>a quick check this looked promising.

I fixed the issue by using

create index tempkaivetoode on tempkaive(toode);
create index tempalgemutoode on tempalgsemu(toode);

and using exists.

Will your suggestion run faster ? Is it reasonable to switch to use your
suggestion ?

Andrus.

"Andrus" <kobruleht2@hot.ee> writes:
>> Just out of curiosity, what "earlier version" was that that was able to
>> run this query quickly?

> It was installed in customer site at May 2012 in Windows 2003 server and
> latest RTM version of Postgres
> x32 in this time was used.

That would probably have been 9.1.something, which makes it even less
likely that there was a significant planner difference affecting this.
I wonder if maybe the 9.1 installation had a higher work_mem, or there
was some other configuration setting you forgot to bring forward.
(A higher work_mem might have allowed it to use hashed rather than
simple subplans, which could possibly explain the speed difference.)

> Will your suggestion run faster ? Is it reasonable to switch to use your
> suggestion ?

If you're happy with performance now, there's probably no reason to
mess with it.  Changing the query might allow you to skip building
those indexes though, so if that's a pain point then it might be
worth spending more time on.

            regards, tom lane