Re: BUG #3085: Performance BUG

"Alexander Kirpa" <postgres@bilteks.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=999loops=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.573rows=499 loops=999) 
                                   Filter: (i4 < $0)
 Total runtime: 6906.130 ms
(10 rows)


            regards, tom lane