Andriy Tkachuk <ant@imt.com.ua> writes:
> What's wrong with planner that executes my query in function?:
> tele=# EXPLAIN analyze select sum(cost) from bills where (parent(user_id) = 6916799 or user_id = 6916799) and dat >=
1062363600and dat < 10649555
> 99;
In the function case, the planner will not have access to the specific
values that "dat" is being compared to --- it'll see something like
... and dat >= $1 and dat < $2
In this case it has to fall back on a default estimate of how many rows
will be selected, and I suspect it's guessing that a seqscan will be
faster. The trouble is that for a sufficiently large range of d1/d2,
a seqscan *will* be faster.
You might find that the best solution is to use FOR ... EXECUTE and plug
the parameters into the query string so that the planner can see their
values. This will mean re-planning on every function call, but the
advantage is the plan will adapt to the actual range of d1/d2.
regards, tom lane