Since cte is already an optimization fence, you can go further and make it temporary table.
Create table;analyze;select should make optimizer's work much easier.
18 лют. 2013 18:45, "John Lumby" <johnlumby@xxxxxxxxxxx> напис.
On 2012-10-09 23:09:21
Tom Lane wrote:
>
> re subject Why am I getting great/terrible estimates with these CTE queries?
> You're assuming the case where the estimate is better is better for a
> reason ... but it's only better as a result of blind dumb luck. The
> outer-level query planner doesn't know anything about the CTE's output
> except the estimated number of rows --- in particular, it doesn't drill
> down to find any statistics about the join column.
>
I am also struggling with a problem involving CTEs although in my case
it is caused by huge *under*-estimation of cardinality rather then *over*-estimation.
The statement is quite complex and the problem arises because there is a chain of
RECURSIVE CTEs each defined as a query involving an earlier CTE and more tables.
Eventually there is no hope for making a good cardinality estimate.
One CTE in particular has a cardinality estimate of 1 (I guess the actual
estimate is nearer zero and rounded up) but actual count is over 100000.
The planner puts this CTE as inner of a nested loop accessed by simple linear CTE scan
and the full query then takes over 20 minutes.
-> Nested Loop (cost=0.00..0.06 rows=1 width=588) (actual time=2340.421..1201593.856 rows=105984 loops=1)
Join Filter: ((winnum.subnet_id = binoptasc.subnet_id) AND (winnum.option_code = binoptasc.option_code) AND ((winnum.option_discriminator)::text = (binoptasc.option_discriminator)::text) AND (winnum.net_rel_level = binoptasc.net_rel_level))
Rows Removed by Join Filter: 7001612448
Buffers: shared hit=2290941
-> CTE Scan on winning_option_nums winnum (cost=0.00..0.02 rows=1 width=536) (actual time=2338.422..2543.684 rows=62904 loops=1)
Buffers: shared hit=2290941
-> CTE Scan on subnet_inhrt_options_asc binoptasc (cost=0.00..0.02 rows=1 width=584) (actual time=0.000..9.728 rows=111308 loops=62904)
Whereas, (by altering various statistics to be very wrong) the entire query runs in 21 seconds.
There have been several debates about how to address situations like this where
no practical non-query-specific statistics-gathering scheme can ever hope to
gather enough statistics to model the later derived tables. E.g. the frowned-on
SELECTIVITY clause and ideas for query-specific statistics.
Meanwhile, I have one other suggestion aimed specifically at problematic CTEs:
Would it be reasonable to provide a new Planner Configuration option :
enable_nestloop_cte_inner (boolean)
Enables or disables the query planner's use of nested-loop join plans in which a CTE is the inner.
It is impossible to suppress such nested-loop joins entirely,
but turning this variable off discourages the planner from using one
if there are other methods available, such as sorting the CTE for merge-join
or hashing it for hash-join.
The default is on.
John
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
