Skip site navigation (1) Skip section navigation (2)

BUG #3085: Performance BUG

From: "" <postgres(at)bilteks(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3085: Performance BUG
Date: 2007-02-27 22:55:19
Message-ID: 200702272255.l1RMtJ4N051886@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3085
Logged by:          
Email address:      postgres(at)bilteks(dot)com
PostgreSQL version: 8.2.3
Operating system:   FreeBSD 6.1
Description:        Performance BUG
Details: 

Test sequence:
%psql test
create table t1 (i4 int4);
insert into t1 SELECT generate_series(1,999999);
vacuum analyze;
EXPLAIN ANALYZE SELECT * FROM (
SELECT i4,(SELECT COUNT(1) FROM t1 AS subselect) AS c1
FROM t1 main_table
) AS external
ORDER BY external.c1+external.c1+external.c1;
                                                            QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
 Sort  (cost=17429.03..17664.97 rows=94374 width=4) (actual
time=2582.681..2861.135 rows=100000 loops=1)
   Sort Key: (($1 + $2) + $3)
   InitPlan
     ->  Aggregate  (cost=1620.68..1620.69 rows=1 width=0) (actual
time=410.606..410.608 rows=1 loops=1)
           ->  Seq Scan on t1 subselect  (cost=0.00..1384.74 rows=94374
width=0) (actual time=0.038..220.444 rows=100000 loops=1)
     ->  Aggregate  (cost=1620.68..1620.69 rows=1 width=0) (actual
time=407.217..407.219 rows=1 loops=1)
           ->  Seq Scan on t1 subselect  (cost=0.00..1384.74 rows=94374
width=0) (actual time=0.100..218.832 rows=100000 loops=1)
     ->  Aggregate  (cost=1620.68..1620.69 rows=1 width=0) (actual
time=408.512..408.514 rows=1 loops=1)
           ->  Seq Scan on t1 subselect  (cost=0.00..1384.74 rows=94374
width=0) (actual time=0.099..221.303 rows=100000 loops=1)
     ->  Aggregate  (cost=1620.68..1620.69 rows=1 width=0) (actual
time=413.888..413.890 rows=1 loops=1)
           ->  Seq Scan on t1 subselect  (cost=0.00..1384.74 rows=94374
width=0) (actual time=0.122..224.616 rows=100000 loops=1)
   ->  Seq Scan on t1 main_table  (cost=0.00..1856.61 rows=94374 width=4)
(actual time=1640.477..2060.580 rows=100000 loops=1)
 Total runtime: 3074.265 ms
(13 rows)

Time: 3077.961 ms

and similar:
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 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;

 QUERY PLAN

----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------------------
 Sort  (cost=201598.03..201600.52 rows=999 width=4) (actual
time=31236.239..31238.171 rows=999 loops=1)
   Sort Key: (((((main_table.i4 - (((subplan) + main_table.i4) +
main_table.i4)) + (((subplan) + main_table.i4) + main_table.i4))
+(((subplan) + main_table.i4) + main_table.i4)) + (((subplan) +
main_table.i4) + main_table.i4)) + (((subplan) + main_table.i4) +
main_table.i4))
   ->  Seq Scan on t1 main_table  (cost=0.00..201548.25 rows=999 width=4)
(actual time=11.886..31222.853 rows=999 loops=1)
         SubPlan
           ->  Aggregate  (cost=18.32..18.33 rows=1 width=0) (actual
time=2.857..2.859 rows=1 loops=999)
                 ->  Seq Scan on t1 subselect  (cost=0.00..17.49 rows=333
width=0) (actual time=0.060..1.795 rows=499 loops=999)
                       Filter: (i4 < $0)
           ->  Aggregate  (cost=18.32..18.33 rows=1 width=0) (actual
time=2.787..2.789 rows=1 loops=999)
                 ->  Seq Scan on t1 subselect  (cost=0.00..17.49 rows=333
width=0) (actual time=0.061..1.775 rows=499 loops=999)
                       Filter: (i4 < $0)
           ->  Aggregate  (cost=18.32..18.33 rows=1 width=0) (actual
time=2.829..2.831 rows=1 loops=999)
                 ->  Seq Scan on t1 subselect  (cost=0.00..17.49 rows=333
width=0) (actual time=0.060..1.768 rows=499 loops=999)
                       Filter: (i4 < $0)
           ->  Aggregate  (cost=18.32..18.33 rows=1 width=0) (actual
time=2.780..2.782 rows=1 loops=999)
                 ->  Seq Scan on t1 subselect  (cost=0.00..17.49 rows=333
width=0) (actual time=0.061..1.767 rows=499 loops=999)
                       Filter: (i4 < $0)
           ->  Aggregate  (cost=18.32..18.33 rows=1 width=0) (actual
time=2.831..2.833 rows=1 loops=999)
                 ->  Seq Scan on t1 subselect  (cost=0.00..17.49 rows=333
width=0) (actual time=0.063..1.745 rows=499 loops=999)
                       Filter: (i4 < $0)
           ->  Aggregate  (cost=18.32..18.33 rows=1 width=0) (actual
time=2.781..2.783 rows=1 loops=999)
                 ->  Seq Scan on t1 subselect  (cost=0.00..17.49 rows=333
width=0) (actual time=0.062..1.764 rows=499 loops=999)
                       Filter: (i4 < $0)
           ->  Aggregate  (cost=18.32..18.33 rows=1 width=0) (actual
time=2.801..2.803 rows=1 loops=999)
                 ->  Seq Scan on t1 subselect  (cost=0.00..17.49 rows=333
width=0) (actual time=0.060..1.779 rows=499 loops=999)
                       Filter: (i4 < $0)
           ->  Aggregate  (cost=18.32..18.33 rows=1 width=0) (actual
time=2.808..2.810 rows=1 loops=999)
                 ->  Seq Scan on t1 subselect  (cost=0.00..17.49 rows=333
width=0) (actual time=0.080..1.766 rows=499 loops=999)
                       Filter: (i4 < $0)
           ->  Aggregate  (cost=18.32..18.33 rows=1 width=0) (actual
time=2.750..2.752 rows=1 loops=999)
                 ->  Seq Scan on t1 subselect  (cost=0.00..17.49 rows=333
width=0) (actual time=0.055..1.746 rows=499 loops=999)
                       Filter: (i4 < $0)
           ->  Aggregate  (cost=18.32..18.33 rows=1 width=0) (actual
time=2.852..2.854 rows=1 loops=999)
                 ->  Seq Scan on t1 subselect  (cost=0.00..17.49 rows=333
width=0) (actual time=0.061..1.782 rows=499 loops=999)
                       Filter: (i4 < $0)
           ->  Aggregate  (cost=18.32..18.33 rows=1 width=0) (actual
time=2.803..2.804 rows=1 loops=999)
                 ->  Seq Scan on t1 subselect  (cost=0.00..17.49 rows=333
width=0) (actual time=0.063..1.778 rows=499 loops=999)
                       Filter: (i4 < $0)
 Total runtime: 31242.269 ms
(38 rows)

Best regards,
 Alexander Kirpa

Responses

pgsql-bugs by date

Next:From: Raymond NaseefDate: 2007-02-28 00:25:38
Subject: Re: BUG #3059: psql to 'postgres' shortcut
Previous:From: Bruce MomjianDate: 2007-02-27 19:10:22
Subject: Re: [PATCHES] BUG #2969: Inaccuracies in Solaris FAQ

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group