Re: Avoid huge perfomance loss on string concatenation - Mailing list pgsql-general

Thank you very much for quick reply.

> can you please give us the types of  dok.kuupaev and dok.kellaaeg?  I
> think a simple fix is possible here.

dok.kuupaev type is DATE

dok.kellaaeg type is     character(5) NOT NULL DEFAULT ''
and is used to represent dokument time in format   hh mm

Database encoding is UTF-8 , cluster locale is estonian, OS is Windows 2003
server.

>> You provide zero information on the table layout

dok table full definition is below.
What other information do you need ?

>>, and the explain output
>> has been horribly mangled by your MUA.

I used copy and paste from pgAdmin.
I checked my message and it seems that explain output is OK, havent found
any truncation.
So I do'nt understand this.

>> I would suspect the problem is that there's no index that can be used
>> for that final comparison.

Postgres must use index on kuupaev in both queries.
This index filters out most rows.

>> Do you have an index along the lines of
>> CREATE INDEX dokindex ON dok (kuupaeve||kellaaeg) ?

I do'nt have this index.
dok.kuupaev||dok.kellaaeg conditon should applied after index search is
performed.
It filters out only a small number of rows additionally to the plain kuupaev
filter.
So adding index on dok.kuupaev||dok.kellaaeg  is not reasonable IMHO.

Please confirm that most reasonable way to fix this to add this index, I
will add this.

>> Overall, the fact that you're concatenating two text fields to generate a
>> date field tends to suggest that your database schema has some fairly
>> major design problems, but I can only speculate at this point.

This schema is migrated from dbms where there was no datetime support.
char(5) field is used to express time  in form   hh mm
This schema is deployed in a large number of servers.
Its change would be very expensive. change requires huge amout of  work time
to re-write applications, create database conversion scripts, re-write
pl/sql triggers, test and fix new bugs causes by change.

Andrus.


CREATE TABLE firma1.dok
(
  doktyyp character(1) NOT NULL,
  dokumnr integer NOT NULL DEFAULT nextval('dok_dokumnr_seq'::regclass),
  kuupaev date NOT NULL,
  oper character(3),
  klient character(12),
  laonr numeric(2),
  raha character(3),
  tasudok character(25),
  knr character(10),
  tasukuup date,
  yksus character(10),
  sihtyksus character(10),
  pais2obj character(10),
  saaja character(12),
  krdokumnr integer,
  eimuuda ebool,
  kasutaja character(10),
  username character(10),
  kellaaeg character(5) NOT NULL DEFAULT ''::bpchar,
  arvekonto character(10),
  maksetin character(5),
  exchrate numeric(11,6),
  ratefound date,
  kurss numeric(10,5),
  tekst1 text,
  viitenr character(20),
  objrealt ebool,
  arvenumber character(25),
  pais3obj character(10),
  pais4obj character(10),
  pais5obj character(10),
  pais6obj character(10),
  pais7obj character(10),
  pais8obj character(10),
  pais9obj character(10),
  masin character(5),
  tegmasin character(5),
  guid character(36) NOT NULL,
  doksumma numeric(12,2),
  kinnitatud ebool,
  tasumata numeric(12,2),
  sularaha numeric(12,2),
  kaardimaks numeric(12,2),
  kalkliik character(1),
  kalktoode character(20),
  inventuur ebool,
  algus date,
  lopp date,
  taidetud ebool,
  kaal numeric(7,3),
  "timestamp" character(14) NOT NULL DEFAULT to_char(now(),
'YYYYMMDDHH24MISS'::text),
  vmnr integer,
  tellimus character(25),
  volitaisik character(36),
  liikmesrii character(2),
  tehingulii character(2),
  tarneklaus character(10),
  statprots character(2),
  CONSTRAINT dok_pkey PRIMARY KEY (dokumnr),
  CONSTRAINT dok_arvekonto_fkey FOREIGN KEY (arvekonto)
      REFERENCES firma1.konto (kontonr) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_kalktoode_fkey FOREIGN KEY (kalktoode)
      REFERENCES firma1.toode (toode) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_kasutaja_fkey FOREIGN KEY (kasutaja)
      REFERENCES kasutaja (kasutaja) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_klient_fkey FOREIGN KEY (klient)
      REFERENCES firma1.klient (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_knr_fkey FOREIGN KEY (knr)
      REFERENCES firma1.konto (kontonr) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_krdokumnr_fkey FOREIGN KEY (krdokumnr)
      REFERENCES firma1.dok (dokumnr) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_liikmesrii_fkey FOREIGN KEY (liikmesrii)
      REFERENCES riik (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_maksetin_fkey FOREIGN KEY (maksetin)
      REFERENCES maksetin (maksetin) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_oper_fkey FOREIGN KEY (oper)
      REFERENCES alamdok (oper) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_pais2obj_fkey FOREIGN KEY (pais2obj)
      REFERENCES firma1.yksus2 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_pais3obj_fkey FOREIGN KEY (pais3obj)
      REFERENCES firma1.yksus3 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_pais4obj_fkey FOREIGN KEY (pais4obj)
      REFERENCES firma1.yksus4 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_pais5obj_fkey FOREIGN KEY (pais5obj)
      REFERENCES firma1.yksus5 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_pais6obj_fkey FOREIGN KEY (pais6obj)
      REFERENCES firma1.yksus6 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_pais7obj_fkey FOREIGN KEY (pais7obj)
      REFERENCES firma1.yksus7 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_pais8obj_fkey FOREIGN KEY (pais8obj)
      REFERENCES firma1.yksus8 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_pais9obj_fkey FOREIGN KEY (pais9obj)
      REFERENCES firma1.yksus9 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_raha_fkey FOREIGN KEY (raha)
      REFERENCES raha (raha) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_saaja_fkey FOREIGN KEY (saaja)
      REFERENCES firma1.klient (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_sihtyksus_fkey FOREIGN KEY (sihtyksus)
      REFERENCES firma1.yksus1 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_statprots_fkey FOREIGN KEY (statprots)
      REFERENCES transpor (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_tarneklaus_fkey FOREIGN KEY (tarneklaus)
      REFERENCES tarnekla (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_tehingulii_fkey FOREIGN KEY (tehingulii)
      REFERENCES tehingul (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_username_fkey FOREIGN KEY (username)
      REFERENCES kasutaja (kasutaja) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_vmnr_fkey FOREIGN KEY (vmnr)
      REFERENCES firma1.vmaks (vmnr) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_volitaisik_fkey FOREIGN KEY (volitaisik)
      REFERENCES firma1.kaardika (guid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_yksus_fkey FOREIGN KEY (yksus)
      REFERENCES firma1.yksus1 (yksus) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY IMMEDIATE,
  CONSTRAINT dok_check CHECK (krdokumnr IS NULL OR (doktyyp = ANY
(ARRAY['G'::bpchar, 'O'::bpchar]))),
  CONSTRAINT dok_dokumnr_check CHECK (dokumnr > 0),
  CONSTRAINT dok_guid_check CHECK (guid <> ''::bpchar)
)
WITHOUT OIDS;

ALTER TABLE firma1.dok OWNER TO eeva_owner;

CREATE INDEX dok_klient_idx
  ON firma1.dok
  USING btree
  (klient);

CREATE INDEX dok_krdokumnr_idx
  ON firma1.dok
  USING btree
  (krdokumnr);

CREATE INDEX dok_kuupaev_idx
  ON firma1.dok
  USING btree
  (kuupaev);

CREATE INDEX dok_tasudok_idx
  ON firma1.dok
  USING btree
  (tasudok);

CREATE UNIQUE INDEX dok_tasudok_unique_idx
  ON firma1.dok
  USING btree
  (doktyyp, tasudok)
  WHERE doktyyp = ANY (ARRAY['T'::bpchar, 'U'::bpchar]);

CREATE INDEX dok_tasumata_idx
  ON firma1.dok
  USING btree
  (tasumata);

CREATE INDEX dok_tellimus_idx
  ON firma1.dok
  USING btree
  (tellimus);

CREATE TRIGGER dok_btrig
  BEFORE INSERT
  ON firma1.dok
  FOR EACH ROW
  EXECUTE PROCEDURE firma1.dok_seq_trig();