Re: BUG #3085: Performance BUG

"Alexander Kirpa" <postgres(at)bilteks(dot)com> writes:
> Sorry for long reply delay.
> Yes. Both previous samples is different,
> but I speak about incorrect planner work - see multiple 'aggregate'.
> Try as alternative next sample:

Well, I'm not sure I want to prevent the thing from flattening
subqueries just because they contain sub-subqueries; nor does
trying both ways sound attractive for typical problems.

What you can do if you need to prevent flattening in a particular case
is insert an "OFFSET 0" as an optimization fence:

regression=# explain analyze
SELECT i4,x1,huge.x1+huge.x1+huge.x1+huge.x1+huge.x1 FROM
(SELECT i4,c1+i4 as x1 FROM (
SELECT i4,(SELECT COUNT(1) FROM t1 AS subselect WHERE
i4<main_table.i4)+i4
AS c1
FROM t1 main_table
) AS external offset 0) AS HUGE
ORDER BY i4-huge.x1+huge.x1+huge.x1+huge.x1+huge.x1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=18416.39..18418.89 rows=999 width=12) (actual time=6896.629..6900.553 rows=999 loops=1)
Sort Key: ((((((huge.i4 - huge.x1) + huge.x1) + huge.x1) + huge.x1) + huge.x1))
-> Subquery Scan huge (cost=0.00..18366.62 rows=999 width=12) (actual time=4.955..6886.427 rows=999 loops=1)
-> Limit (cost=0.00..18334.15 rows=999 width=4) (actual time=4.889..6862.476 rows=999 loops=1)
-> Seq Scan on t1 main_table (cost=0.00..18334.15 rows=999 width=4) (actual time=4.874..6855.316 rows=999 loops=1)
SubPlan
-> Aggregate (cost=18.32..18.33 rows=1 width=0) (actual time=6.831..6.835 rows=1 loops=999)
-> Seq Scan on t1 subselect (cost=0.00..17.49 rows=333 width=0) (actual time=0.075..4.573 rows=499 loops=999)
Filter: (i4 < $0)
Total runtime: 6906.130 ms
(10 rows)

regards, tom lane