pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed7.3 vs 7.4 performance
16+ messages / 8 participants
[nested] [flat]
* 7.3 vs 7.4 performance
@ 2004-02-05 05:16 Orion Henry <orion@trustcommerce.com>
0 siblings, 3 replies; 16+ messages in thread
From: Orion Henry @ 2004-02-05 05:16 UTC (permalink / raw)
To: pgsql-performance
I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
slower than 7.3.4. Is this common knowledge or am I just unlucky with
my query/data selection?
Things of note that might matter: the machine is a dual Opteron 1.4GHz
running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from the Fedora
distro and the 7.4.1 was the PGDG package. The database is 3.5 Gigs
with 10 millions rows and the machine had 1 Gig or ram.
Oh... as a side note I'm happy to announce that the 2.6 Linux kernel has
more than DOUBLED the speed of all my Postgres queries over the 2.4. =)
--
Orion Henry <orion@trustcommerce.com>
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-05 05:27 Josh Berkus <josh@agliodbs.com>
parent: Orion Henry <orion@trustcommerce.com>
2 siblings, 2 replies; 16+ messages in thread
From: Josh Berkus @ 2004-02-05 05:27 UTC (permalink / raw)
To: Orion Henry <orion@trustcommerce.com>; pgsql-performance
Orion,
> I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> slower than 7.3.4. Is this common knowledge or am I just unlucky with
> my query/data selection?
No, it's not common knowledge. It should be the other way around. Perhaps
it's the queries you picked? Even so ..... feel free to post individual
EXPLAIN ANALYZEs to the list.
> Things of note that might matter: the machine is a dual Opteron 1.4GHz
> running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from the Fedora
> distro and the 7.4.1 was the PGDG package. The database is 3.5 Gigs
> with 10 millions rows and the machine had 1 Gig or ram.
I'm wondering if we need specific compile-time switches for Opteron. I know
we got Opteron code tweaks in the last version, but am not sure if a --with
is required to activate them.
> Oh... as a side note I'm happy to announce that the 2.6 Linux kernel has
> more than DOUBLED the speed of all my Postgres queries over the 2.4. =)
Keen. Waiting for upgrades ....
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-05 05:32 Christopher Browne <cbbrowne@acm.org>
parent: Orion Henry <orion@trustcommerce.com>
2 siblings, 3 replies; 16+ messages in thread
From: Christopher Browne @ 2004-02-05 05:32 UTC (permalink / raw)
To: pgsql-performance
Oops! orion@trustcommerce.com (Orion Henry) was seen spray-painting on a wall:
> I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> slower than 7.3.4. Is this common knowledge or am I just unlucky with
> my query/data selection?
That seems unusual; the opposite seems more typical in view of there
being some substantial improvements to the query optimizer.
Have you tried doing EXPLAIN ANALYZE on the queries on both sides?
There would doubtless be interest in figuring out what is breaking
down...
> Things of note that might matter: the machine is a dual Opteron
> 1.4GHz running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from
> the Fedora distro and the 7.4.1 was the PGDG package. The database
> is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
>
> Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
> has more than DOUBLED the speed of all my Postgres queries over the
> 2.4. =)
I did some heavy-transaction-oriented tests recently on somewhat
heftier quad-Xeon hardware, and found little difference between 2.4
and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
Now, I'm quite sure my load was rather different from yours, but I
find the claim of doubling of speed rather surprising.
--
(format nil "~S@~S" "aa454" "freenet.carleton.ca")
http://www.ntlug.org/~cbbrowne/spiritual.html
Failure is not an option. It comes bundled with your Microsoft product.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-05 06:50 Chris Trawick <ctrawick@cultured.net>
parent: Christopher Browne <cbbrowne@acm.org>
2 siblings, 1 reply; 16+ messages in thread
From: Chris Trawick @ 2004-02-05 06:50 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
On Thu, 2004-02-05 at 00:32, Christopher Browne wrote:
> > Things of note that might matter: the machine is a dual Opteron
> > 1.4GHz running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from
> > the Fedora distro and the 7.4.1 was the PGDG package. The database
> > is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
> >
> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
> > has more than DOUBLED the speed of all my Postgres queries over the
> > 2.4. =)
>
> I did some heavy-transaction-oriented tests recently on somewhat
> heftier quad-Xeon hardware, and found little difference between 2.4
> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
> Now, I'm quite sure my load was rather different from yours, but I
> find the claim of doubling of speed rather surprising.
I don't. I got a similar boost out of 2.6 when dealing with extreme
concurrency. Then again, I also got a similar boost out of 7.4. The
two together tickled my bank account. ;)
One question though... It sounds like your 7.3 binaries are 64-bit and
your 7.4 binaries are 32-bit. Have you tried grabbing the SRPM for 7.4
and recompiling it for X86_64?
chris
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-05 11:14 Carlos Eduardo Smanioto <csmanioto@uol.com.br>
parent: Christopher Browne <cbbrowne@acm.org>
2 siblings, 2 replies; 16+ messages in thread
From: Carlos Eduardo Smanioto @ 2004-02-05 11:14 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; pgsql-performance
> I did some heavy-transaction-oriented tests recently on somewhat
> heftier quad-Xeon hardware, and found little difference between 2.4
> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
> Now, I'm quite sure my load was rather different from yours, but I
> find the claim of doubling of speed rather surprising.
> --
What's the type of File System you used in the Linux? I am wanting to know
which is the operational system better for PostgreSQL: FreeBSD versus Linux
2.6.
Thanks.
[]'s
Carlos Eduardo Smanioto (Brazil)
----- Original Message -----
From: "Christopher Browne" <cbbrowne@acm.org>
To: <pgsql-performance@postgresql.org>
Sent: Thursday, February 05, 2004 3:32 AM
Subject: Re: [PERFORM] 7.3 vs 7.4 performance
> Oops! orion@trustcommerce.com (Orion Henry) was seen spray-painting on a
wall:
> > I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> > slower than 7.3.4. Is this common knowledge or am I just unlucky with
> > my query/data selection?
>
> That seems unusual; the opposite seems more typical in view of there
> being some substantial improvements to the query optimizer.
>
> Have you tried doing EXPLAIN ANALYZE on the queries on both sides?
> There would doubtless be interest in figuring out what is breaking
> down...
>
> > Things of note that might matter: the machine is a dual Opteron
> > 1.4GHz running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from
> > the Fedora distro and the 7.4.1 was the PGDG package. The database
> > is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
> >
> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
> > has more than DOUBLED the speed of all my Postgres queries over the
> > 2.4. =)
>
> I did some heavy-transaction-oriented tests recently on somewhat
> heftier quad-Xeon hardware, and found little difference between 2.4
> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
> Now, I'm quite sure my load was rather different from yours, but I
> find the claim of doubling of speed rather surprising.
> --
> (format nil "~S@~S" "aa454" "freenet.carleton.ca")
> http://www.ntlug.org/~cbbrowne/spiritual.html
> Failure is not an option. It comes bundled with your Microsoft product.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-05 13:30 Christopher Browne <cbbrowne@acm.org>
parent: Carlos Eduardo Smanioto <csmanioto@uol.com.br>
1 sibling, 0 replies; 16+ messages in thread
From: Christopher Browne @ 2004-02-05 13:30 UTC (permalink / raw)
To: pgsql-performance
In an attempt to throw the authorities off his trail, csmanioto@uol.com.br ("Carlos Eduardo Smanioto") transmitted:
>> I did some heavy-transaction-oriented tests recently on somewhat
>> heftier quad-Xeon hardware, and found little difference between 2.4
>> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD
>> 4.9. Now, I'm quite sure my load was rather different from yours,
>> but I find the claim of doubling of speed rather surprising.
>
> What's the type of File System you used in the Linux? I am wanting
> to know which is the operational system better for PostgreSQL:
> FreeBSD versus Linux 2.6.
On the Linux box in question, I was using JFS, which has had the mixed
reviews, lately, that on the one hand, it _appears_ to be a tad faster
than all the others, but that has been, on the other hand, associated
with systems hanging up and crashing, under load.
The latter bit is a _really_ big caveat. On that particular machine,
I have a nicely repeatable "test case" where I can do a particular set
of "system load" that consistently takes the system down, to the point
of having to hit the "big red button." If I could point to a clear
reason why it happens, I'd be a much happier camper. As it stands, it
is a bit nebulous whether the problem is:
a) Hardware drivers,
b) Flakey hardware (which Linux 2.6.1 copes with a lot better than
2.4!),
c) Flakey 2.4 kernel,
d) Problem with JFS,
e) Something else not yet identified as a plausible cause.
If I could say, "Oh, it's an identified bug in the Frobozz RAID
controller drivers, and was fixed in 2.6.0-pre-17", that would help
allay the suspicion that the problem could be any of the above.
--
let name="aa454" and tld="freenet.carleton.ca" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/
"Another result of the tyranny of Pascal is that beginners don't use
function pointers." --Rob Pike
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-05 15:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 16+ messages in thread
From: Tom Lane @ 2004-02-05 15:07 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Orion Henry <orion@trustcommerce.com>; pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
> I'm wondering if we need specific compile-time switches for Opteron. I know
> we got Opteron code tweaks in the last version,
Not in 7.4. There is some marginal hacking in the spinlock code in CVS
tip for multi-CPU i386 and x86_64 (viz, add a PAUSE instruction inside
the wait loop) but I'm not sure that will have any significance in real
life.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-05 18:04 Orion Henry <orion@trustcommerce.com>
parent: Chris Trawick <ctrawick@cultured.net>
0 siblings, 0 replies; 16+ messages in thread
From: Orion Henry @ 2004-02-05 18:04 UTC (permalink / raw)
To: Chris Trawick <ctrawick@cultured.net>; +Cc: pgsql-performance
>
> One question though... It sounds like your 7.3 binaries are 64-bit and
> your 7.4 binaries are 32-bit. Have you tried grabbing the SRPM for 7.4
> and recompiling it for X86_64?
No, they were all 64 bit.
I'm going to run explains on all my queries and see if I can find
anything of interest...
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-06 10:43 Hannu Krosing <hannu@tm.ee>
parent: Orion Henry <orion@trustcommerce.com>
2 siblings, 1 reply; 16+ messages in thread
From: Hannu Krosing @ 2004-02-06 10:43 UTC (permalink / raw)
To: Orion Henry <orion@trustcommerce.com>; +Cc: pgsql-performance
Orion Henry kirjutas N, 05.02.2004 kell 07:16:
> I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> slower than 7.3.4. Is this common knowledge or am I just unlucky with
> my query/data selection?
>
> Things of note that might matter: the machine is a dual Opteron 1.4GHz
> running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from the Fedora
> distro and the 7.4.1 was the PGDG package.
Are you sure that it is not the case that it is not tha case that 7.3.4
is 64 bit and the PGDG package is 32 ?
> The database is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
>
> Oh... as a side note I'm happy to announce that the 2.6 Linux kernel has
> more than DOUBLED the speed of all my Postgres queries over the 2.4. =)
Is this on this same hardware ?
-------------
Hannu
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-06 10:44 Hannu Krosing <hannu@tm.ee>
parent: Christopher Browne <cbbrowne@acm.org>
2 siblings, 1 reply; 16+ messages in thread
From: Hannu Krosing @ 2004-02-06 10:44 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Christopher Browne kirjutas N, 05.02.2004 kell 07:32:
> Oops! orion@trustcommerce.com (Orion Henry) was seen spray-painting on a wall:
> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
> > has more than DOUBLED the speed of all my Postgres queries over the
> > 2.4. =)
>
> I did some heavy-transaction-oriented tests recently on somewhat
> heftier quad-Xeon hardware, and found little difference between 2.4
> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
> Now, I'm quite sure my load was rather different from yours, but I
> find the claim of doubling of speed rather surprising.
perhaps you were just IO-bound while he was not ?
or starving on some locks ?
-------------
Hannu
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-06 17:05 markw@osdl.org
parent: Carlos Eduardo Smanioto <csmanioto@uol.com.br>
1 sibling, 0 replies; 16+ messages in thread
From: markw@osdl.org @ 2004-02-06 17:05 UTC (permalink / raw)
To: csmanioto@uol.com.br; +Cc: cbbrowne@acm.org; pgsql-performance
I have some results with our DBT-2 (OLTP) workload on various linux-2.6
filesystems, if you'll find that interesting:
http://developer.osdl.org/markw/fs/dbt2_project_results.html
I've found JFS to perform similarly to ext2. Reiserfs isn't far behind.
XFS and ext3 fall off a bit. These results are also on a 4-way Xeon,
with about 70 drives and a ~ 30GB database.
Mark
On 5 Feb, Carlos Eduardo Smanioto wrote:
>> I did some heavy-transaction-oriented tests recently on somewhat
>> heftier quad-Xeon hardware, and found little difference between 2.4
>> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
>> Now, I'm quite sure my load was rather different from yours, but I
>> find the claim of doubling of speed rather surprising.
>> --
>
> What's the type of File System you used in the Linux? I am wanting to know
> which is the operational system better for PostgreSQL: FreeBSD versus Linux
> 2.6.
>
> Thanks.
>
> []'s
> Carlos Eduardo Smanioto (Brazil)
>
> ----- Original Message -----
> From: "Christopher Browne" <cbbrowne@acm.org>
> To: <pgsql-performance@postgresql.org>
> Sent: Thursday, February 05, 2004 3:32 AM
> Subject: Re: [PERFORM] 7.3 vs 7.4 performance
>
>
>> Oops! orion@trustcommerce.com (Orion Henry) was seen spray-painting on a
> wall:
>> > I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
>> > slower than 7.3.4. Is this common knowledge or am I just unlucky with
>> > my query/data selection?
>>
>> That seems unusual; the opposite seems more typical in view of there
>> being some substantial improvements to the query optimizer.
>>
>> Have you tried doing EXPLAIN ANALYZE on the queries on both sides?
>> There would doubtless be interest in figuring out what is breaking
>> down...
>>
>> > Things of note that might matter: the machine is a dual Opteron
>> > 1.4GHz running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from
>> > the Fedora distro and the 7.4.1 was the PGDG package. The database
>> > is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
>> >
>> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
>> > has more than DOUBLED the speed of all my Postgres queries over the
>> > 2.4. =)
>>
>> I did some heavy-transaction-oriented tests recently on somewhat
>> heftier quad-Xeon hardware, and found little difference between 2.4
>> and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
>> Now, I'm quite sure my load was rather different from yours, but I
>> find the claim of doubling of speed rather surprising.
>> --
>> (format nil "~S@~S" "aa454" "freenet.carleton.ca")
>> http://www.ntlug.org/~cbbrowne/spiritual.html
>> Failure is not an option. It comes bundled with your Microsoft product.
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-07 01:02 Orion Henry <orion@trustcommerce.com>
parent: Hannu Krosing <hannu@tm.ee>
0 siblings, 0 replies; 16+ messages in thread
From: Orion Henry @ 2004-02-07 01:02 UTC (permalink / raw)
To: Hannu Krosing <hannu@tm.ee>; +Cc: pgsql-performance
On Fri, 2004-02-06 at 02:43, Hannu Krosing wrote:
> Orion Henry kirjutas N, 05.02.2004 kell 07:16:
> > I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> > slower than 7.3.4. Is this common knowledge or am I just unlucky with
> > my query/data selection?
> >
> > Things of note that might matter: the machine is a dual Opteron 1.4GHz
> > running Fedora Core 1 Test 1 for X86_64. The 7.3.4 was from the Fedora
> > distro and the 7.4.1 was the PGDG package.
>
> Are you sure that it is not the case that it is not tha case that 7.3.4
> is 64 bit and the PGDG package is 32 ?
Yes sure... I don't know if they were compiled with differing
optimizations or compilers though...
> > The database is 3.5 Gigs with 10 millions rows and the machine had 1 Gig or ram.
> >
> > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel has
> > more than DOUBLED the speed of all my Postgres queries over the 2.4. =)
>
> Is this on this same hardware ?
No. I havent gotten the 2.6 kernel working on the Opteron yet. The 2x speedup
was on a dual Athlon 2GHz.
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-07 01:03 Orion Henry <orion@trustcommerce.com>
parent: Hannu Krosing <hannu@tm.ee>
0 siblings, 0 replies; 16+ messages in thread
From: Orion Henry @ 2004-02-07 01:03 UTC (permalink / raw)
To: Hannu Krosing <hannu@tm.ee>; +Cc: Christopher Browne <cbbrowne@acm.org>; pgsql-performance
On Fri, 2004-02-06 at 02:44, Hannu Krosing wrote:
> Christopher Browne kirjutas N, 05.02.2004 kell 07:32:
> > Oops! orion@trustcommerce.com (Orion Henry) was seen spray-painting on a wall:
> > > Oh... as a side note I'm happy to announce that the 2.6 Linux kernel
> > > has more than DOUBLED the speed of all my Postgres queries over the
> > > 2.4. =)
> >
> > I did some heavy-transaction-oriented tests recently on somewhat
> > heftier quad-Xeon hardware, and found little difference between 2.4
> > and 2.6, and a small-but-quite-repeatable advantage with FreeBSD 4.9.
> > Now, I'm quite sure my load was rather different from yours, but I
> > find the claim of doubling of speed rather surprising.
>
> perhaps you were just IO-bound while he was not ?
>
> or starving on some locks ?
The queries were across almost 4 gigs of data on a machine with 512 MB of ram.
I personally was assuming it was the anticipatory disk scheduler... but alas I
don't know why it affected me so much.
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-07 01:49 Orion Henry <orion@trustcommerce.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 16+ messages in thread
From: Orion Henry @ 2004-02-07 01:49 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-performance
On Wed, 2004-02-04 at 21:27, Josh Berkus wrote:
> Orion,
>
> > I've done some testing of 7.3.4 vs 7.4.1 and found 7.4.1 to be 20%-30%
> > slower than 7.3.4. Is this common knowledge or am I just unlucky with
> > my query/data selection?
>
> No, it's not common knowledge. It should be the other way around. Perhaps
> it's the queries you picked? Even so ..... feel free to post individual
> EXPLAIN ANALYZEs to the list.
Thank you...
Here's one good example of 7.3 beating 7.4 soundly:
Again this could me some compile option since I built the 7.4 RPM
from source and I got the 7.3 from Fedora or something to
do with the Opteron architecture. (Yes the compiled postgres
is 64 bit)
SELECT cid,media_name,media_type,count(*) as count,sum(a_amount)
as a,sum(case when b_amount > 0 then b_amount else 0 end) as b,
sum(case when b_amount < 0 then b_amount else 0 end) as c
FROM transdata JOIN media_info ON (media = media_type)
WHERE cid = 140100 AND demo is not null
AND trans_date between date '2004-01-01'
AND date_trunc('month',date '2004-01-01' + interval '32 days')
GROUP BY cid,media_name,media_type;
Here's 7.3's time and explain
real 0m34.260s
user 0m0.010s
sys 0m0.000s
---------------------------------------------------------------
Aggregate (cost=7411.88..7415.32 rows=17 width=25)
-> Group (cost=7411.88..7413.60 rows=172 width=25)
-> Sort (cost=7411.88..7412.31 rows=172 width=25)
Sort Key: transdata.cid, media_info.media_name, transdata.media_type
-> Hash Join (cost=1.22..7405.50 rows=172 width=25)
Hash Cond: ("outer".media_type = "inner".media)
-> Index Scan using transdata_date_index on transdata (cost=0.00..7401.27 rows=172 width=14)
Index Cond: ((trans_date >= ('2004-01-01'::date)::timestamp with time zone) AND (trans_date <= ('2004-02-01 00:00:00'::timestamp without time zone)::timestamp with time zone))
Filter: ((cid = 140100) AND (demo IS NOT NULL))
-> Hash (cost=1.18..1.18 rows=18 width=11)
-> Seq Scan on media_info (cost=0.00..1.18 rows=18 width=11)
Here's 7.4's time and explain
real 0m43.052s
user 0m0.000s
sys 0m0.020s
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=8098.26..8098.29 rows=2 width=23)
-> Hash Join (cost=1.22..8095.48 rows=159 width=23)
Hash Cond: ("outer".media_type = "inner".media)
-> Index Scan using transdata_date_index on transdata (cost=0.00..8091.87 rows=159 width=14)
Index Cond: ((trans_date >= ('2004-01-01'::date)::timestamp with time zone) AND (trans_date <= ('2004-02-01 00:00:00'::timestamp without time zone)::timestamp with time zone))
Filter: ((cid = 140100) AND (demo IS NOT NULL))
-> Hash (cost=1.18..1.18 rows=18 width=11)
-> Seq Scan on media_info (cost=0.00..1.18 rows=18 width=11)
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-07 02:32 Josh Berkus <josh@agliodbs.com>
parent: Orion Henry <orion@trustcommerce.com>
0 siblings, 1 reply; 16+ messages in thread
From: Josh Berkus @ 2004-02-07 02:32 UTC (permalink / raw)
To: Orion Henry <orion@trustcommerce.com>; +Cc: pgsql-performance
Orion,
> Here's one good example of 7.3 beating 7.4 soundly:
> Again this could me some compile option since I built the 7.4 RPM
> from source and I got the 7.3 from Fedora or something to
> do with the Opteron architecture. (Yes the compiled postgres
> is 64 bit)
Need an EXPLAIN ANALYZE, not just an EXPLAIN.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: 7.3 vs 7.4 performance
@ 2004-02-07 07:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 16+ messages in thread
From: Tom Lane @ 2004-02-07 07:28 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Orion Henry <orion@trustcommerce.com>; pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
> Orion,
>> Here's one good example of 7.3 beating 7.4 soundly:
> Need an EXPLAIN ANALYZE, not just an EXPLAIN.
Indeed. Also, please try 7.4 with enable_hashagg turned off to see
what it does with a 7.3-style plan.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2004-02-07 07:28 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-05 05:16 7.3 vs 7.4 performance Orion Henry <orion@trustcommerce.com>
2004-02-05 05:27 ` Josh Berkus <josh@agliodbs.com>
2004-02-05 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-07 01:49 ` Orion Henry <orion@trustcommerce.com>
2004-02-07 02:32 ` Josh Berkus <josh@agliodbs.com>
2004-02-07 07:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-02-05 05:32 ` Christopher Browne <cbbrowne@acm.org>
2004-02-05 06:50 ` Chris Trawick <ctrawick@cultured.net>
2004-02-05 18:04 ` Orion Henry <orion@trustcommerce.com>
2004-02-05 11:14 ` Carlos Eduardo Smanioto <csmanioto@uol.com.br>
2004-02-05 13:30 ` Christopher Browne <cbbrowne@acm.org>
2004-02-06 17:05 ` markw@osdl.org
2004-02-06 10:44 ` Hannu Krosing <hannu@tm.ee>
2004-02-07 01:03 ` Orion Henry <orion@trustcommerce.com>
2004-02-06 10:43 ` Hannu Krosing <hannu@tm.ee>
2004-02-07 01:02 ` Orion Henry <orion@trustcommerce.com>
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