: Because timestamp() is marked non-cachable, Postgres doesn't know it is
: constant over the duration of the query and so scans every row in the
: table re-calculating the timestamp each time.
:
: When you use it in the fast psql query you call timestamp('constant')
: which Postgres probably is clever enough to figure out as a constant.
:
: In fact, if you used WHERE ... dt_inicial = timestamp('2001-04-17
: 12:12:00') in the function Postgres would probably figure it out.
:
: There are two ways you could help Postgres to do the right thing:
:
: 1. Use another variable for the conversions:
:
: ...
: DECLARE
: ...
: ts_pdtinicial timestamp;
: BEGIN
: ts_pdtinicial := timestamp(pdtinicial);
: ...
: WHERE
: ...
: dt_inicial = ts_pdtinicial;
: ...
:
: Hopefully this will simplify things for Postgres and it will decide it
: can use the index.
:
Interesting, but I did it here and there was no performance increase.
: 2. Mark the timestamp() conversions as cachable
: You need to find the entry in pg_proc which takes text and returns a
: timestamp and update the proiscachable flag.
: You'll need to be user postgres, I don't know if it will work and it
: might break other things, so be careful.
No thanks, my boss will kill me if something goes wrong in the database :)
Thanks for all info Richard, I'm really greatfull about it. Now I can figure
out whats the problem and it's finally fixed.
Best regards
José Vilson de Mello de Farias
Dígitro Tecnologia Ltda.