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