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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
