Re: Postgres 7.4.9 slow! - Mailing list pgsql-sql

On Fri, 2005-10-21 at 14:01 +0200, Havasvölgyi Ottó wrote:
> Mike,
> 
> Please send the EXPLAIN ANALYZE of the two versions of the query.

There they are, they are both the same:

join_test=# select version();                                                      version

----------------------------------------------------------------------------------------------------------------------PostgreSQL
7.4.9on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.2
 
20050808 (prerelease) (Ubuntu 4.0.1-4ubuntu9)
(1 row)

join_test=# \timing
Timing is on.
join_test=# set enable_seqscan to off;
SET
Time: 0.715 ms
join_test=# explain select p.phone, count(*) from phones p left join
table_data d on p.phone = d.phone group by p.phone having count(*) > 1
order by count(*) desc;                                                    QUERY PLAN

---------------------------------------------------------------------------------------------------------------------Sort
(cost=4993545.35..4993754.61 rows=83704 width=16)  Sort Key: count(*)  ->  GroupAggregate  (cost=0.00..4985814.87
rows=83704width=16)        Filter: (count(*) > 1)        ->  Merge Left Join  (cost=0.00..4974843.57 rows=1379136
 
width=16)              Merge Cond: (("outer".phone)::text =
("inner".phone)::text)              ->  Index Scan using pk1 on phones p  (cost=0.00..2876.37
rows=83704 width=16)              ->  Index Scan using "fki_fkTableData" on table_data d
(cost=0.00..4954515.15 rows=1379135 width=16)
(8 rows)

Time: 169.781 ms
join_test=#


And now the 7.4.8:

join_test=# select version();                                            version
--------------------------------------------------------------------------------------------------PostgreSQL 7.4.8 on
i686-pc-linux-gnu,compiled by GCC gcc (GCC) 3.3.5
 
(Debian 1:3.3.5-8ubuntu2)
(1 row)

join_test=# \timing
Timing is on.
join_test=# set enable_seqscan to off;
SET
Time: 0.500 ms
join_test=# explain select p.phone, count(*) from phones p left join
table_data d on p.phone = d.phone group by p.phone having count(*) > 1
order by count(*) desc;                                                    QUERY PLAN

---------------------------------------------------------------------------------------------------------------------Sort
(cost=4993545.35..4993754.61 rows=83704 width=16)  Sort Key: count(*)  ->  GroupAggregate  (cost=0.00..4985814.87
rows=83704width=16)        Filter: (count(*) > 1)        ->  Merge Left Join  (cost=0.00..4974843.57 rows=1379136
 
width=16)              Merge Cond: (("outer".phone)::text =
("inner".phone)::text)              ->  Index Scan using pk1 on phones p  (cost=0.00..2876.37
rows=83704 width=16)              ->  Index Scan using "fki_fkTableData" on table_data d
(cost=0.00..4954515.15 rows=1379135 width=16)
(8 rows)

Time: 31.510 ms
join_test=#

The plans are same. It's just that when I run the query with pg7.4.8 it
takes 100% of the processor time while running. pg7.4.9 takes 2-10%
while running. Disk activity is much more intense with pg7.4.9
Mike

-- 
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."