BUG #3085: Performance BUG
От | |
---|---|
Тема | BUG #3085: Performance BUG |
Дата | |
Msg-id | 200702272255.l1RMtJ4N051886@wwwmaster.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #3085: Performance BUG
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
The following bug has been logged online: Bug reference: 3085 Logged by: Email address: postgres@bilteks.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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: "Ilya Storozhilov"Дата:
Сообщение: BUG #3093: Error with converting error messages between server and client encodings