Re: Query runs forever after upgrading to 9.3 - Mailing list pgsql-general

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.