Re: BUG #3085: Performance BUG

On 1 Mar 2007, at 11:28, Tom Lane wrote:

> "" <postgres(at)bilteks(dot)com> writes:
> > Description: Performance BUG
>
> You haven't actually shown us any bug. These are not the same query
> and there's no reason to expect them to take the same amount of time.
>
> regards, tom lane
>
Hi, Tom!
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:
drop table t1;
create table t1 (i4 int4);
insert into t1 SELECT generate_series(1,999);
vacuum analyze;
EXPLAIN ANALYZE
SELECT i4,x1,huge.x1+huge.x1+huge.x1+huge.x1+huge.x1 FROM
(SELECT i4,c1+i4+random()*0 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) AS HUGE
ORDER BY i4-huge.x1+huge.x1+huge.x1+huge.x1+huge.x1;

and compare timing/planner results in cases with and without
'+random()*0' part.

On my test hardware I receive results:
with '+random()*0' - 2818ms
w/o '+random()*0' - 30527ms.

I believe that plan for case with '+random()*0' more correct, see it
below:
Sort (cost=18428.88..18431.38 rows=999 width=12) (actual
time=2816.722..2818.681 rows=999 loops=1)
Sort Key: ((((((i4)::double precision - x1) + x1) + x1) + x1) + x1)
-> Subquery Scan huge (cost=0.00..18379.11 rows=999 width=12)
(actual time=1.350..2810.169 rows=999 loops=1)
-> Seq Scan on t1 main_table (cost=0.00..18344.14 rows=999
width=4) (actual time=1.312..2791.659 rows=999 loops=1)
SubPlan
-> Aggregate (cost=18.32..18.33 rows=1 width=0)
(actual time=2.758..2.760 rows=1 loops=999)
-> Seq Scan on t1 subselect (cost=0.00..17.49
rows=333 width=0) (actual time=0.055..1.736 rows=499 loops=999)
Filter: (i4 < $0)
Total runtime: 2821.200 ms

Best regards,
Alexander Kirpa