Thread: Postgres 7.4.9 slow!
Hello! I'm not sure if this belongs to this mailing list, if not, please tell me to redirect to where it belongs. I have a query that does COUNT, LEFT JOIN and GROUP BY from two tables. One has 85000 records, and other has 1000000 records. I've been running the tests on 7.4.3, with SET ENABLE_SEQSCAN TO OFF, and I get 9-11 seconds for that query. Query plan shows that postgres is using both indexes on both tables (one index is set on primary key, naturaly, and other is manualy set on foreign key in 'child' table). That is acceptable. But, now I downloaded postgres 7.4.9, and i'm running the very same query on the very same database with all the indices and constraints beeing the same (also SET ENABLE_SEQSCAN TO OFF), and I get around 90-110 seconds. Has anyone noticed extreeme slowdown of postgres 7.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."
Mike, Please send the EXPLAIN ANALYZE of the two versions of the query. Best Regards, Otto ----- Original Message ----- From: "Mario Splivalo" <mario.splivalo@mobart.hr> To: <pgsql-sql@postgresql.org> Sent: Friday, October 21, 2005 1:13 PM Subject: [SQL] Postgres 7.4.9 slow! > Hello! I'm not sure if this belongs to this mailing list, if not, please > tell me to redirect to where it belongs. > > I have a query that does COUNT, LEFT JOIN and GROUP BY from two tables. > One has 85000 records, and other has 1000000 records. I've been running > the tests on 7.4.3, with SET ENABLE_SEQSCAN TO OFF, and I get 9-11 > seconds for that query. Query plan shows that postgres is using both > indexes on both tables (one index is set on primary key, naturaly, and > other is manualy set on foreign key in 'child' table). That is > acceptable. > > But, now I downloaded postgres 7.4.9, and i'm running the very same > query on the very same database with all the indices and constraints > beeing the same (also SET ENABLE_SEQSCAN TO OFF), and I get around > 90-110 seconds. > > Has anyone noticed extreeme slowdown of postgres 7.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." > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >
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."
On Friday 21 October 2005 14:34, Mario Splivalo wrote: | 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; this is not what Otto requested; please send the output of EXPLAIN ANALYZE select p.phone, count(*) from phones p left join table_data d on p.phone = d.phone group by p.phone havingcount(*) > 1 order by count(*) desc; for both versions. Ciao, Thomas -- Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> I have a query that does COUNT, LEFT JOIN and GROUP BY from two tables.
> One has 85000 records, and other has 1000000 records. I've been running
> the tests on 7.4.3,
Your later message shows 7.4.8. Which is it?
> But, now I downloaded postgres 7.4.9, and i'm running the very same
> query on the very same database with all the indices and constraints
> beeing the same (also SET ENABLE_SEQSCAN TO OFF), and I get around
> 90-110 seconds.
You sure it's the very same? The version outputs suggest that these
might be two different machines; certainly two very different compilers
were used. One thing I'd wonder about is whether both databases were
initialized in the same locale.
regards, tom lane
On Fri, 2005-10-21 at 10:20 -0400, Tom Lane wrote: > Mario Splivalo <mario.splivalo@mobart.hr> writes: > > I have a query that does COUNT, LEFT JOIN and GROUP BY from two tables. > > One has 85000 records, and other has 1000000 records. I've been running > > the tests on 7.4.3, > > Your later message shows 7.4.8. Which is it? > > > But, now I downloaded postgres 7.4.9, and i'm running the very same > > query on the very same database with all the indices and constraints > > beeing the same (also SET ENABLE_SEQSCAN TO OFF), and I get around > > 90-110 seconds. > > You sure it's the very same? The version outputs suggest that these > might be two different machines; certainly two very different compilers > were used. One thing I'd wonder about is whether both databases were > initialized in the same locale. Yes, I realized that the new Ubuntu distribution hac gcc4 by default. I'll compile again both 7.4.8 and 7.4.9 and my home PC, and see what happens then. My mention of 7.4.3 is a typo. It's 7.4.8 and 7.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."