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
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
