Re: slow query plans caused by under-estimation of CTE cardinality

Vitalii wrote
>
> 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.
>
Thanks Vitalii  -  yes,   you are right,  and I have used that technique on other cases like this.

However,  for this one,   the entire query must be executed as a single query in order
that it is based on a consistent snapshot (in the Multiversion Concurrency Control sense)
of the base table data.    Using the temp table technique would allow a commit to occur
which would be invisible to the part of the query which would build the temp
but visible to the remaining part of the query.     I know I could set Repeatable Read
for the transaction to ensure the consistency but that causes other concurrency problems
as this query is part of a fairly long-running transaction.     I really just want this one
query to avoid "dangerous" plans (meaning relying too much on an estimate of cardinality
of ~ 1 being really correct).

I also forgot to show the fragment of "good" plan (from corrupting the statistics).
It demonstrates how effective the hash join is in comparison  -
20 minutes reduced down to 1 second for this join.

 ->  Hash Join  (cost=0.80..1.51 rows=1 width=588) (actual time=1227.517..1693.792 rows=105984 loops=1)
       Hash Cond: ((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))
       Buffers: shared hit=386485 read=364
       ->  CTE Scan on winning_option_nums winnum  (cost=0.00..0.40 rows=20 width=536) (actual time=1174.558..1222.542 rows=62904 loops=1)
             Buffers: shared hit=386485 read=364
       ->  Hash  (cost=0.40..0.40 rows=20 width=584) (actual time=52.933..52.933 rows=111308 loops=1)
             Buckets: 1024  Batches: 1  Memory Usage: 8644kB
             ->  CTE Scan on subnet_inhrt_options_asc binoptasc  (cost=0.00..0.40 rows=20 width=584) (actual time=0.001..21.651 rows=111308 loops=1)

John