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=62904loops=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.651rows=111308 loops=1)
John