From: | John Lumby <johnlumby(at)hotmail(dot)com> |
---|---|
To: | <tivv00(at)gmail(dot)com> |
Cc: | pgsql-performance maillist <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: slow query plans caused by under-estimation of CTE cardinality |
Date: | 2013-02-18 23:26:23 |
Message-ID: | COL116-W25F8931477407ED7689D69A3F40@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2013-02-19 00:19:19 | Re: High CPU usage / load average after upgrading to Ubuntu 12.04 |
Previous Message | Vitalii Tymchyshyn | 2013-02-18 20:40:37 | Re: slow query plans caused by under-estimation of CTE cardinality |