Re: Avoid huge perfomance loss on string concatenation

In response to "Andrus" <eetasoft(at)online(dot)ee>:

> Thank you very much for quick reply.

Keep the mailing list included in this discussion.

> > 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 ?

That's pretty much it.

> >>, 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 had to cut/paste the data into a text editor and reformat it before I
could work with it. The arbitrary line-wrap is painful:

"Nested Loop Left Join (cost=68.75..5064.86 rows=1 width=24) (actual
time=8.081..26995.552 rows=567 loops=1)"
" Join Filter: ((toode.grupp = artliik.grupp) AND (toode.liik =
artliik.liik))"
" -> Nested Loop (cost=68.75..5062.19 rows=1 width=43) (actual
time=8.045..26965.731 rows=567 loops=1)"
" -> Index Scan using toode_pkey on toode (cost=0.00..8.27 rows=1
width=43) (actual time=0.023..0.026 rows=1 loops=1)"
" Index Cond: ('NAH S'::bpchar = toode)"
" -> Nested Loop (cost=68.75..5053.91 rows=1 width=24) (actual
time=8.016..26964.698 rows=567 loops=1)"
" -> Index Scan using dok_kuupaev_idx on dok
(cost=0.00..4326.16 rows=10 width=4) (actual time=0.059..67.985 rows=3543
loops=1)"
" Index Cond: ((kuupaev >= '2007-11-01'::date) AND
(kuupaev <= '2007-12-04'::date))"
" Filter: ((((kuupaev)::text || (kellaaeg)::text) >=
'2007-11-01'::text) AND (((kuupaev)::text || (kellaaeg)::text) <=
'2007-12-0423 59'::text))"
" -> Bitmap Heap Scan on rid (cost=68.75..72.76 rows=1
width=28) (actual time=7.577..7.577 rows=0 loops=3543)"
" Recheck Cond: ((dok.dokumnr = rid.dokumnr) AND
(rid.toode = 'NAH S'::bpchar))"
" -> BitmapAnd (cost=68.75..68.75 rows=1 width=0)
(actual time=7.574..7.574 rows=0 loops=3543)"
" -> Bitmap Index Scan on rid_dokumnr_idx
(cost=0.00..5.13 rows=83 width=0) (actual time=0.037..0.037 rows=14
loops=3543)"
" Index Cond: (dok.dokumnr = rid.dokumnr)"
" -> Bitmap Index Scan on rid_toode_idx
(cost=0.00..63.03 rows=1354 width=0) (actual time=7.528..7.528 rows=21144
loops=3543)"
" Index Cond: (toode = 'NAH S'::bpchar)"
" -> Seq Scan on artliik (cost=0.00..2.27 rows=27 width=19) (actual
time=0.007..0.020 rows=27 loops=567)"
"Total runtime: 26996.399 ms"

> >> 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.

I'm not sure what that comment is supposed to mean.

PG is using the index for the condition
dok.kuupaev BETWEEN '2007-11-01' AND '2007-12-04'

but there is no index that matches the expression
dok.kuupaev||dok.kellaaeg BETWEEN '2007-11-01' AND '2007-12-0423 59'

If you look at your explain output, you'll see that step is taking a
lot of time, and it's inside a nested loop, which means it's run
repeatedly.

> This index filters out most rows.

Have you run a VACUUM ANALYZE on the tables involved with this query
recently? It's possible that PG has outdated statistics and is
running a poor plan as a result.

> >> 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.

The query planner doesn't seem to think so.

> 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.

Just add the index and rerun to see if it helps. If it doesn't, then
drop the index. I have absolutely no way to investigate this for you.

> >> 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.

I understand. However, that doesn't change the fact that such a change
will improve performance and accuracy of the data.

For example, in your query, you have a text string meant to represent
a date: '2007-12-0423 59'

This is not a valid date/time, but PostgreSQL has no way to know that
because it's just a text string. As a result, you're query is liable
to give you outright incorrect results.

>
> 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();
>
>
>
>

--
Bill Moran
http://www.potentialtech.com