Hi all,
I´m experiencing an unexpected behaviour in the planner. I want the
planner to apply a function in the results of a subquery, but its doing
a filter in the hole table.
The results between the mixed filters are the same in the end, but its
taking ages. What I want is the planner to aplly the filters in the
query sequence, eg, filter the subquery, then filter its results again
using a function.
The table:
[ users ]
year | cod | name | age | sex
1997 | 123 | john | 23 | M
1997 | 456 | smith | 68 | M
1998 | 123 | john | 23 | M
1998 | 456 | smith | 68 | M
1999 | 789 | mary | 12 | F
...
The query:
select u2.cod, u2.name
from
(
select u.cod, u.name
from users u
where age between 0 and 44 and sex='F'
group by u.cod, u.name
) u2
group by u2.cod, u2.name
having
getSalaryPeriod(1997,1999,u2.cod) > 1000
The function getSalaryPeriod is in pl/pgsql , and basically returns the
accumulated salary in the given period (1997 to 1999).
I want the planner to filter the user age and sex (wich restricts the
results and groups the user cod), and after that, run getSalaryPeriod
ONLY in the results of the subquery u2. Instead, its filtering the age,
sex and the salary in the same point :
Filter: ((age >= 0) AND (age<= 44) AND (sex = 'F'::bpchar) AND
(getSalaryPeriod(1997 , 1999, (cod)::text, 0) >= 1000))
Right now, I am rewriting the query to filter age/sex in a temp table,
and after that, running getSalary.
This way is EXTREMELY faster, but I think that there must be a better way =)
Any hints ?
-- ACV