Using string concatenation in where clause causes huge perfomance loss:

explain analyze select
rid.toode
FROM dok JOIN rid USING (dokumnr)
JOIN toode USING (toode)
LEFT JOIN artliik using(grupp,liik)
WHERE rid.toode='NAH S'
AND dok.kuupaev BETWEEN '2007-11-01' AND '2007-12-04'
and dok.kuupaev||dok.kellaaeg BETWEEN '2007-11-01' AND '2007-12-0423 59'

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

takes 26 seconds !

If I remove last line it takes only 0 seconds:

SET SEARCH_PATH TO FIRMA1,public;
explain analyze select
rid.toode
FROM dok JOIN rid USING (dokumnr)
JOIN toode USING (toode)
LEFT JOIN artliik using(grupp,liik)
WHERE rid.toode='NAH S'
AND dok.kuupaev BETWEEN '2007-11-01' AND '2007-12-04'

"Hash Left Join (cost=4313.85..7702.10 rows=24 width=24) (actual
time=10.138..48.884 rows=567 loops=1)"
" Hash Cond: ((toode.grupp = artliik.grupp) AND (toode.liik =
artliik.liik))"
" -> Nested Loop (cost=4311.17..7699.14 rows=24 width=43) (actual
time=10.049..47.877 rows=567 loops=1)"
" -> Index Scan using toode_pkey on toode (cost=0.00..8.27 rows=1
width=43) (actual time=0.043..0.046 rows=1 loops=1)"
" Index Cond: ('NAH S'::bpchar = toode)"
" -> Hash Join (cost=4311.17..7690.63 rows=24 width=24) (actual
time=9.998..47.341 rows=567 loops=1)"
" Hash Cond: (rid.dokumnr = dok.dokumnr)"
" -> Index Scan using rid_toode_idx on rid
(cost=0.00..3372.45 rows=1354 width=28) (actual time=0.089..24.265
rows=21144 loops=1)"
" Index Cond: (toode = 'NAH S'::bpchar)"
" -> Hash (cost=4286.20..4286.20 rows=1998 width=4) (actual
time=9.871..9.871 rows=3543 loops=1)"
" -> Index Scan using dok_kuupaev_idx on dok
(cost=0.00..4286.20 rows=1998 width=4) (actual time=0.057..6.779 rows=3543
loops=1)"
" Index Cond: ((kuupaev >= '2007-11-01'::date) AND
(kuupaev <= '2007-12-04'::date))"
" -> Hash (cost=2.27..2.27 rows=27 width=19) (actual time=0.060..0.060
rows=27 loops=1)"
" -> Seq Scan on artliik (cost=0.00..2.27 rows=27 width=19) (actual
time=0.009..0.027 rows=27 loops=1)"
"Total runtime: 49.409 ms"


How to rewrite the query

select
rid.toode,
artliik.*
FROM dok JOIN rid USING (dokumnr)
JOIN toode USING (toode)
LEFT JOIN artliik using(grupp,liik)
WHERE rid.toode='NAH S'
AND dok.kuupaev BETWEEN '2007-11-01' AND '2007-12-04'
and dok.kuupaev||dok.kellaaeg BETWEEN '2007-11-01' AND '2007-12-0423 59'

so it runs fast ?

Andrus.


"PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)"

"Andrus" <kobruleht2@hot.ee> wrote:
>
> Using string concatenation in where clause causes huge perfomance loss:
>
> explain analyze select
> rid.toode
> FROM dok JOIN rid USING (dokumnr)
> JOIN toode USING (toode)
> LEFT JOIN artliik using(grupp,liik)
> WHERE rid.toode='NAH S'
> AND dok.kuupaev BETWEEN '2007-11-01' AND '2007-12-04'
> and dok.kuupaev||dok.kellaaeg BETWEEN '2007-11-01' AND '2007-12-0423 59'

You provide zero information on the table layout, and the explain output
has been horribly mangled by your MUA.

I would suspect the problem is that there's no index that can be used
for that final comparison. Do you have an index along the lines of
CREATE INDEX dokindex ON dok (kuupaeve||kellaaeg) ?

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.

If neither of those help, I expect you'll need to provide more information.

> "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"
>
> takes 26 seconds !
>
> If I remove last line it takes only 0 seconds:
>
> SET SEARCH_PATH TO FIRMA1,public;
> explain analyze select
> rid.toode
> FROM dok JOIN rid USING (dokumnr)
> JOIN toode USING (toode)
> LEFT JOIN artliik using(grupp,liik)
> WHERE rid.toode='NAH S'
> AND dok.kuupaev BETWEEN '2007-11-01' AND '2007-12-04'
>
> "Hash Left Join (cost=4313.85..7702.10 rows=24 width=24) (actual
> time=10.138..48.884 rows=567 loops=1)"
> " Hash Cond: ((toode.grupp = artliik.grupp) AND (toode.liik =
> artliik.liik))"
> " -> Nested Loop (cost=4311.17..7699.14 rows=24 width=43) (actual
> time=10.049..47.877 rows=567 loops=1)"
> " -> Index Scan using toode_pkey on toode (cost=0.00..8.27 rows=1
> width=43) (actual time=0.043..0.046 rows=1 loops=1)"
> " Index Cond: ('NAH S'::bpchar = toode)"
> " -> Hash Join (cost=4311.17..7690.63 rows=24 width=24) (actual
> time=9.998..47.341 rows=567 loops=1)"
> " Hash Cond: (rid.dokumnr = dok.dokumnr)"
> " -> Index Scan using rid_toode_idx on rid
> (cost=0.00..3372.45 rows=1354 width=28) (actual time=0.089..24.265
> rows=21144 loops=1)"
> " Index Cond: (toode = 'NAH S'::bpchar)"
> " -> Hash (cost=4286.20..4286.20 rows=1998 width=4) (actual
> time=9.871..9.871 rows=3543 loops=1)"
> " -> Index Scan using dok_kuupaev_idx on dok
> (cost=0.00..4286.20 rows=1998 width=4) (actual time=0.057..6.779 rows=3543
> loops=1)"
> " Index Cond: ((kuupaev >= '2007-11-01'::date) AND
> (kuupaev <= '2007-12-04'::date))"
> " -> Hash (cost=2.27..2.27 rows=27 width=19) (actual time=0.060..0.060
> rows=27 loops=1)"
> " -> Seq Scan on artliik (cost=0.00..2.27 rows=27 width=19) (actual
> time=0.009..0.027 rows=27 loops=1)"
> "Total runtime: 49.409 ms"
>
>
> How to rewrite the query
>
> select
> rid.toode,
> artliik.*
> FROM dok JOIN rid USING (dokumnr)
> JOIN toode USING (toode)
> LEFT JOIN artliik using(grupp,liik)
> WHERE rid.toode='NAH S'
> AND dok.kuupaev BETWEEN '2007-11-01' AND '2007-12-04'
> and dok.kuupaev||dok.kellaaeg BETWEEN '2007-11-01' AND '2007-12-0423 59'
>
> so it runs fast ?
>
> Andrus.
>
>
> "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
> (mingw-special)"
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings


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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Dec 4, 2007 8:02 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> "Andrus" <kobruleht2@hot.ee> wrote:
> > Using string concatenation in where clause causes huge perfomance loss:
> >
> > explain analyze select
> > rid.toode
> > FROM dok JOIN rid USING (dokumnr)
> > JOIN toode USING (toode)
> > LEFT JOIN artliik using(grupp,liik)
> > WHERE rid.toode='NAH S'
> > AND dok.kuupaev BETWEEN '2007-11-01' AND '2007-12-04'
> > and dok.kuupaev||dok.kellaaeg BETWEEN '2007-11-01' AND '2007-12-0423 59'

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


> You provide zero information on the table layout, and the explain output
> has been horribly mangled by your MUA.
>
> I would suspect the problem is that there's no index that can be used
> for that final comparison. Do you have an index along the lines of
> CREATE INDEX dokindex ON dok (kuupaeve||kellaaeg) ?
>
> 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.

just small correction here...expressions like that in the create index
need an extra set of parens (but I agree with your sentiment):
CREATE INDEX dokindex ON dok ((kuupaeve||kellaaeg))

merlin

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

> just small correction here...expressions like that in the create index
> need an extra set of parens (but I agree with your sentiment):
> CREATE INDEX dokindex ON dok ((kuupaeve||kellaaeg))

I tried

CREATE INDEX dok_kuupaev_kellaaeg_idx ON dok ((kuupaev||kellaaeg));

but got error

ERROR: functions in index expression must be marked IMMUTABLE
SQL state: 42P17

How to create such index ?

Andrus.

On Wed, Dec 05, 2007 at 11:32:59AM +0200, Andrus wrote:
> 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.

Your problem is that it is doing that, except postgres is assuming that
it does filter more rows. In actual fact the extra condition is making
no difference whatsoever.

So postgres assumes the result will only give 10 rows and so a nested
loop is better.

As for the index problem, I don't know but it will probably work better.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFHWDBcIB7bNG8LQkwRAmTFAKCBOQQ94fuZhkCzHA0PDi yEcR/8owCfU3sc
J/dB7UsoFbihkbzKSLahrVE=
=YAWK
-----END PGP SIGNATURE-----

Thank you.
I ran manually analyze command.
After that query runs fast.
I have enabled autovacuum and statitics collection in config file.
Every day a lot of rows are added to dok table.

However it seems that statitics is not collected (autovacuum is not running)

Any idea autovacuum is not running ?

Andrus.