Hi folks.
What's wrong with planner that executes my query in function?: (i mean no explanation but runtime)
tele=# EXPLAIN analyze select calc_total(6916799, 1062363600, 1064955599); QUERY PLAN ------------------------------------------------------------------------------------------ Result (cost=0.00..0.01 rows=1 width=0) (actual time=36919.37..36919.37 rows=1 loops=1) Total runtime: 36919.40 msec ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
tele=# \df+ calc_total ... declare usr alias for $1; d1 alias for $2; d2 alias for $3; res integer; begin select sum(cost) into res from bills where (parent(user_id) = usr or user_id = usr) and dat >= d1 and dat < d2; if res is not null then return res; else return 0; end if; end;
You didn't wrote the type of d1 and d2, I had your same problem:
declare
a_user alias for $1;
res INTEGER;
begin
select cost into res
from my_table
where login = a_user;...... end;
the problem was that login was a VARCHAR and a_user was a TEXT so the index was not used, was enough cast a_user::varchar;
I believe that your dat, d1, d2 are not "index" comparable.
Gaetano
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
