From: Mario Splivalo <mario.splivalo@mobart.hr>
To: Havasvölgyi Ottó <h.otto@freemail.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Postgres 7.4.9 slow!
Date: Fri, 21 Oct 2005 14:34:26 +0200
Message-ID: <1129898066.27326.6.camel@ekim> (raw)
In-Reply-To: <006701c5d637$2d7b5550$6400a8c0@OTTO>
References: <1129893226.25351.3.camel@ekim>
<006701c5d637$2d7b5550$6400a8c0@OTTO>
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."
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mario.splivalo@mobart.hr, h.otto@freemail.hu
Subject: Re: Postgres 7.4.9 slow!
In-Reply-To: <1129898066.27326.6.camel@ekim>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox