Alberto Dalmaso <dalmaso@clesius.it> wrote:
> P.S.: to understand what the query has to make (and 80% of the view
> hve these to make): a lot of time is spend to pivoting a table with
> a structure like
> identifier, description_of_value, numeric value
> that has to be transformed in
> identifier, description_1, description_2, ..., description_n
> where n is not a fixed number (it changes in function of the type of
> calculation that was used to generate the rows in the table).
>
> perhaps this information could help.
What would help more is the actual query, if that can be shared. It
leaves a lot less to the imagination than descriptions of it.
There are a couple things which have been requested which would help
pin down the reason the optimizer is not getting to a good plan, so
that it can be allowed to do a good job. As Tom said, this would be a
much more productive focus than casting about for ways to force it to
do what you think is the best thing. (Maybe, given the chance, it can
come up with a plan which runs in seconds, rather than over the 24
minutes you've gotten.)
With all the optimizer options on, and the from_collapse_limit and
join_collapse_limit values both set to 100, run an EXPLAIN (no
ANALYZE) on your big problem query. Let us know how long the EXPLAIN
runs. If it gets any errors, copy and paste all available
information. (General descriptions aren't likely to get us very far.)
Since EXPLAIN without ANALYZE only *plans* the query, but doesn't run
it, it should not take long to do this.
If there are any views or custom functions involved, showing those
along with the query source would be good.
If we get this information, we have a much better chance to find the
real problem and get it fixed.
-Kevin
