Subject: Re: Postgres 7.4.9 slow!
From: Mario Splivalo <mario.splivalo@mobart.hr>
Reply-To: mario.splivalo@mobart.hr
To: =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <006701c5d637$2d7b5550$6400a8c0@OTTO>
References: <1129893226.25351.3.camel@ekim>
	<006701c5d637$2d7b5550$6400a8c0@OTTO>
Content-Type: text/plain; charset=UTF-8
Organization: Mob-Art
Date: Fri, 21 Oct 2005 14:34:26 +0200
Message-Id: <1129898066.27326.6.camel@ekim>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

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.9 on 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=83704 width=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=83704 width=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."