Re: [GENERAL] Performance aggregates
On Tuesday 15 May 2001 14:40, you wrote: Hello, I have 2 tables : CREATE TABLE e_kalkn ( idINT4 NOT NULL, roba VARCHAR(6) NOT NULL, dat DATENOT NULL, datv DATENOT NULL, val VARCHAR(3) NOT NULL, kod VARCHAR(3) NOT NULL, tbVARCHAR(2) NOT NULL, tsVARCHAR(2) NOT NULL, dob VARCHAR(6), statusVARCHAR(1) ); CREATE TABLE e_kalkns ( idINT4 NOT NULL, redbr INTNOT NULL, osn NUMERIC(30,6), proc NUMERIC(30,6), izn NUMERIC(30,6) ); and indexes : create unique index e_kalkn_id on e_kalkn(id); create index e_kalkns_id on e_kalkns(id); Table e_kalkn have 4668 rows and e_kalkns 101170 rows. Query : select roba,sum(izn) from e_kalkn k,e_kalkns ks where k.id=ks.id group by roba order by roba is 2.5 times faster on one commercial database (there are tests on Internet that say 'Postgresql is faster than that database). I can't say which database it is. MySQL is 2.5 times faster, too. regards, ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Performance aggregates
On Tue, 15 May 2001, snpe wrote: Table e_kalkn have 4668 rows and e_kalkns 101170 rows. Query : select roba,sum(izn) from e_kalkn k,e_kalkns ks where k.id=ks.id group by roba order by roba is 2.5 times faster on one commercial database (there are tests on Internet that say 'Postgresql is faster than that database). I can't say which database it is. Have you run vacuum analyze (since loading the data) and what does explain show for the query. Also, what version are you using? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Performance aggregates
On Tuesday 15 May 2001 17:28, Stephan Szabo wrote: On Tue, 15 May 2001, snpe wrote: Table e_kalkn have 4668 rows and e_kalkns 101170 rows. Query : select roba,sum(izn) from e_kalkn k,e_kalkns ks where k.id=ks.id group by roba order by roba is 2.5 times faster on one commercial database (there are tests on Internet that say 'Postgresql is faster than that database). I can't say which database it is. Have you run vacuum analyze (since loading the data) and what does explain show for the query. Also, what version are you using? version postgresql 7.1.1 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance aggregates
On Tuesday 15 May 2001 17:28, Stephan Szabo wrote: On Tue, 15 May 2001, snpe wrote: Table e_kalkn have 4668 rows and e_kalkns 101170 rows. Query : select roba,sum(izn) from e_kalkn k,e_kalkns ks where k.id=ks.id group by roba order by roba is 2.5 times faster on one commercial database (there are tests on Internet that say 'Postgresql is faster than that database). I can't say which database it is. Have you run vacuum analyze (since loading the data) and what does explain show for the query. Also, what version are you using? I have run : vacuumdb --analyze -v -d mytest I try index on column roba in table e_kalkn, but all is same. This is explain : psql:up1:4: NOTICE: QUERY PLAN: Aggregate (cost=11132.18..11286.42 rows=3085 width=32) - Group (cost=11132.18..11209.30 rows=30849 width=32) - Sort (cost=11132.18..11132.18 rows=30849 width=32) - Hash Join (cost=121.35..8831.95 rows=30849 width=32) - Seq Scan on e_kalkns ks (cost=0.00..2041.10 rows=101710 width=16) - Hash (cost=109.68..109.68 rows=4668 width=16) - Seq Scan on e_kalkn k (cost=0.00..109.68 rows=4668 width=16) EXPLAIN ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Performance aggregates
snpe [EMAIL PROTECTED] writes: select roba,sum(izn) from e_kalkn k,e_kalkns ks where k.id=ks.id group by roba order by roba is 2.5 times faster on one commercial database That other DBMS is probably implementing the GROUP BY + aggregate using a hash table to compute all the aggregates in parallel, rather than sorting first as Postgres does. You still need to sort in the end to meet the ORDER BY spec, but you are only sorting the aggregate results not the inputs. Disadvantage: if there are a lot of distinct values of ROBA then your hash table may overrun memory. We have a TODO item to implement hashed aggregation. Teaching the planner to make an intelligent choice between sorted and hashed aggregation will probably be harder than the actual execution code. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Performance aggregates
If your query is not using the correct index files, you're query will run slowly. Please post the output from EXPLAIN. -r At 09:00 PM 5/15/01 +0200, snpe wrote: On Tuesday 15 May 2001 17:28, Stephan Szabo wrote: On Tue, 15 May 2001, snpe wrote: Table e_kalkn have 4668 rows and e_kalkns 101170 rows. Query : select roba,sum(izn) from e_kalkn k,e_kalkns ks where k.id=ks.id group by roba order by roba is 2.5 times faster on one commercial database (there are tests on Internet that say 'Postgresql is faster than that database). I can't say which database it is. Have you run vacuum analyze (since loading the data) and what does explain show for the query. Also, what version are you using? version postgresql 7.1.1 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
RE: [GENERAL] Performance aggregates
I found this same problem. It's the group by that is the killer. PostgreSQL's implementation of group by requires a sort of the entire table on the group by criteria, even if there is an index on the group by column. It's to bad this caused us not to be able to use Postgresql for a project. -Original Message- From: snpe [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 15, 2001 11:59 AM To: Stephan Szabo Cc: PostgreSQL-General Subject: Re: [GENERAL] Performance aggregates On Tuesday 15 May 2001 17:28, Stephan Szabo wrote: On Tue, 15 May 2001, snpe wrote: Table e_kalkn have 4668 rows and e_kalkns 101170 rows. Query : select roba,sum(izn) from e_kalkn k,e_kalkns ks where k.id=ks.id group by roba order by roba is 2.5 times faster on one commercial database (there are tests on Internet that say 'Postgresql is faster than that database). I can't say which database it is. Have you run vacuum analyze (since loading the data) and what does explain show for the query. Also, what version are you using? I have run : vacuumdb --analyze -v -d mytest I try index on column roba in table e_kalkn, but all is same. This is explain : psql:up1:4: NOTICE: QUERY PLAN: Aggregate (cost=11132.18..11286.42 rows=3085 width=32) - Group (cost=11132.18..11209.30 rows=30849 width=32) - Sort (cost=11132.18..11132.18 rows=30849 width=32) - Hash Join (cost=121.35..8831.95 rows=30849 width=32) - Seq Scan on e_kalkns ks (cost=0.00..2041.10 rows=101710 width=16) - Hash (cost=109.68..109.68 rows=4668 width=16) - Seq Scan on e_kalkn k (cost=0.00..109.68 rows=4668 width=16) EXPLAIN ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly This email message is for the sole use of the intended recipient(s) and may contain proprietary and confidential information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. Thank you ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
