Re: BUG #3085: Performance BUG - Mailing list pgsql-bugs

From Alexander Kirpa
Subject Re: BUG #3085: Performance BUG
Date
Msg-id 45FA0835.3227.6127A59A@localhost
Whole thread Raw
In response to Re: BUG #3085: Performance BUG  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: BUG #3085: Performance BUG
List pgsql-bugs
On 1 Mar 2007, at 11:28, Tom Lane wrote:

> "" <postgres@bilteks.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

pgsql-bugs by date:

Previous
From: Magnus Hagander
Date:
Subject: Re: Installation bug
Next
From: "Bruna Schio"
Date:
Subject: FDO Provider for ODBC