Re: [PERFORM] Speeding up Aggregates

Say, what do people think about a comment board thing like php.net has attached to the documentation. People can add comments that show up directly on the bottom of the documentation for each function. I find it's mostly full of junk but skimming the comments often turns up one or two relevant war

Re: [PERFORM] Speeding up Aggregates

Dror Matalon <[EMAIL PROTECTED]> writes: > Ouch. I just double checked and you're right. Is this considered a bug, > or just an implementation issue? Call it a wishlist bug. The problem is it would be a hard feature to implement properly. And none of the people paid to work on postgres by vario

Re: [PERFORM] Sun performance - Major discovery!

Isn't it great how you have the same directory on every host so you can download once and run the same tests easily. Neil Conway wrote: > $ uname -a > Linux spe170 2.4.17-64 #1 Sat Mar 16 17:31:44 MST 2002 parisc64 unknown > $ gcc --version > 3.0.4 > > 'make check' passes I didn't know there w

Re: [PERFORM] Speeding up Aggregates

On Thu, Oct 09, 2003 at 08:35:22PM -0500, Bruno Wolff III wrote: > On Thu, Oct 09, 2003 at 17:44:46 -0700, > Dror Matalon <[EMAIL PROTECTED]> wrote: > > > > How is doing order by limit 1 faster than doing max()? Seems like the > > optimizer will need to sort or scan the data set either way. That

Re: [PERFORM] Sun performance - Major discovery!

On Wed, 2003-10-08 at 21:44, Bruce Momjian wrote: > Agreed. Do we set them all to -O2, then remove it from the ones we > don't get successful reports on? I took the time to compile CVS tip with a few different machines from HP's TestDrive program, to see if there were any regressions using the ne

Re: [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

On Thu, 9 Oct 2003, David Griffiths wrote: > > > "PostgreSQL supports constraints. MySQL doesn't; programmers need to > > > take care of that from the client side" > > > Again, InnoDB supports constraints. > > > > Really? This is news. We did some tests on constraints on InnoDB, and > > found t

Re: [PERFORM] Speeding up Aggregates

On Thu, Oct 09, 2003 at 17:44:46 -0700, Dror Matalon <[EMAIL PROTECTED]> wrote: > > How is doing order by limit 1 faster than doing max()? Seems like the > optimizer will need to sort or scan the data set either way. That part > didn't actually make a difference in my specific case. max() will

Re: [PERFORM] Speeding up Aggregates

On Thu, Oct 09, 2003 at 07:07:00PM -0400, Greg Stark wrote: > Dror Matalon <[EMAIL PROTECTED]> writes: > > > Actually what finally sovled the problem is repeating the > > dtstamp > last_viewed > > in the sub select > > That will at least convince the optimizer to use an index range lookup. But i

Re: [PERFORM] further testing on IDE drives

How did this drive come by default? Write-cache disabled? --- scott.marlowe wrote: > On Thu, 2 Oct 2003, scott.marlowe wrote: > > > I was testing to get some idea of how to speed up the speed of pgbench > > with IDE drive

Re: [PERFORM] further testing on IDE drives

scott.marlowe wrote: > I was testing to get some idea of how to speed up the speed of pgbench > with IDE drives and the write caching turned off in Linux (i.e. hdparm -W0 > /dev/hdx). > > The only parameter that seems to make a noticeable difference was setting > wal_sync_method = open_sync. W

Re: [PERFORM] backup/restore - another area.

Jeff <[EMAIL PROTECTED]> writes: > Idea #1: > Use an LVM and take a snapshop - archive that. > From the way I see it. the downside is the LVM will use a lot of space > until the snapshot is removed. Also PG may be in a slightly inconsistant > state - but this should "appear" to PG the same as i

Re: [PERFORM] Speeding up Aggregates

Dror Matalon <[EMAIL PROTECTED]> writes: > Actually what finally sovled the problem is repeating the > dtstamp > last_viewed > in the sub select That will at least convince the optimizer to use an index range lookup. But it still will have to scan every record that matches channel==$1, link==$2,

Re: [PERFORM] Compare rows

Greg Spiegelberg wrote: Josh Berkus wrote: As you can see, the NULLs are not stored, making this system much more efficient on storage space. Tommorrow I'll (hopefully) write up how to query this for comparisons. It would help if you gave a little more details about what specific comparison

Re: [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

> Thanks for being considerate, thourough, and honest about your opinions. > Particulary that you didn't simple depart in a huff. Why would I depart in a huff? I was just trying to make a few objective observations. I really have no biases; I like what I've seen in MySQL, and I like alot of the m

Re: [PERFORM] Compare rows, SEMI-SUMMARY

Per Josh's recommendation to implement a Vertical Child Table I came up with 3 possible tables to handle the 3 possible value types: varchar, numeric and bigint. Each table has 7 columns: 1 to denote the time the data was collected, 4 which identify where the data came from, 1 to tell me the value

Re: [pgsql-advocacy] [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

Scott, > any chance of getting the perf.html file from varlena folded into the main > documentation tree somewhere? it's a great document, and it would > definitely help if the tuning section of the main docs said "For a more > thorough examination of postgresql tuning see this:" and pointed t

Re: [PERFORM] PostgreSQL vs MySQL

On Thu, 2003-10-09 at 13:30, David Griffiths wrote: > I also have to admit a bit of irritation reading this thread; there is a > fair number of incorrect statements on this thread that, while not > wrong, definately aren't right: > > "Speed depends on the nature of use and the complexity of queri

Re: [PERFORM] PostgreSQL vs MySQL

I concur 100%. PostgreSQL was big and scary and MySQL seemed cute and cuddly, warm and fuzzy. Then I took my undergrad CS RDBMS course (a course that focused on designing the backend software), and only then was I ready to appreciate and wield the battle axe that is PostgreSQL. He also let me use

Re: [PERFORM] PostgreSQL vs MySQL

On Thu, 9 Oct 2003, Jeff wrote: > On Thu, 9 Oct 2003, David Griffiths wrote: > > > 1) the MySQL docs are better (sorry - I found them easier to read, and > > more comprehensive; I had an easier time finding the answers I needed) > > Huh. I had the opposite experience. Each to his own. > I think

[PERFORM] backup/restore - another area.

Boy, I must be getting annoying by now huh? Anyway, after the joys of Solaris being fast I'm moving onto another area - backup & restore. I've been checking the archives and haven't seen any "good" tips for backing up big databases (and more importantly, restoring). I've noticed while doing a ba

Re: [pgsql-advocacy] [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

On Thu, 9 Oct 2003, Josh Berkus wrote: > David Griffiths wrote: > > With regards to #1, I'd like to specifically mention tuning - the docs > > at http://www.postgresql.org/docs/7.3/static/runtime-config.html > > give a > > Have you

Re: [PERFORM] Sun performance - Major discovery!

Christopher Browne wrote: > [EMAIL PROTECTED] (Bruce Momjian) writes: > > 5) How can I compile for optimum performance? > > > > Try using the "-fast" compile flag. The binaries might not be portable to > > other Solaris systems, and you might need to compile everything that links > > to PostgreSQL

Re: [PERFORM] Sun performance - Major discovery!

[EMAIL PROTECTED] (Bruce Momjian) writes: > 5) How can I compile for optimum performance? > > Try using the "-fast" compile flag. The binaries might not be portable to > other Solaris systems, and you might need to compile everything that links > to PostgreSQL with "-fast", but PostgreSQL will run

Re: [pgsql-advocacy] [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

Nick, > Josh- It would be great to have a link to those last two excellent resources > from the techdocs area- perhaps from the "optimizing" section in > http://techdocs.postgresql.org/oresources.php. Who should we suggest this > to? (I submitted these using the form in that area, but you may have

Re: [PERFORM] IMMUTABLE function's flag do not work: 7.3.4, plpgsql

Andriy Tkachuk wrote: On Wed, 8 Oct 2003, Tom Lane wrote: Andriy Tkachuk <[EMAIL PROTECTED]> writes: At second. calc_total() is immutable function: but it seems that it's not cached in one session: It's not supposed to be. but it's written id doc: IMMUTABLE indicates that the fu

Re: [PERFORM] Any 7.4 w32 numbers in yet?

Jason Hihn wrote: > I am very interested in the non-Cygwin windows port. Looking over the 7.4 > beta release, it looks like the code made it in. I read through the win32 > related docs, to find out that they are out-of date instructions (11/2002). > I do hope these get updated with the native windo

[PERFORM] Any 7.4 w32 numbers in yet?

I am very interested in the non-Cygwin windows port. Looking over the 7.4 beta release, it looks like the code made it in. I read through the win32 related docs, to find out that they are out-of date instructions (11/2002). I do hope these get updated with the native windows stuff. But I came here

Re: [pgsql-advocacy] [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

> Have you checked these pages? They've been posted on this list numerous > times: > http://techdocs.postgresql.org > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html > Josh- It would be great to have a link to

Re: [PERFORM] Sun performance - Major discovery!

Jeff wrote: > We're keeping the -O2 for gcc in the template and moving the mention of > -fast to the FAQ, correct? gcc gets -O2, non-gcc gets -O, and -fast is in the FAQ, yea. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

> Yeah, I had similar thought to Oliver's and suspected that this > would be the answer. Also, while it's not too hard to do this for a > single platform, it gets complecated once you start looking at > different ones. > > Josh, let me know when you're ready to do this. I'll try to help, > althou

Re: [PERFORM] Sun performance - Major discovery!

We're keeping the -O2 for gcc in the template and moving the mention of -fast to the FAQ, correct? -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 6: Have you searched our list ar

Re: [PERFORM] OFFTOPIC: PostgreSQL vs MySQL

David, Thanks for being considerate, thourough, and honest about your opinions. Particulary that you didn't simple depart in a huff. > 1) the MySQL docs are better (sorry - I found them easier to read, and > more comprehensive; I had an easier time finding the answers I needed) I can believe

Re: [PERFORM] PostgreSQL vs MySQL

On Thu, 9 Oct 2003, David Griffiths wrote: > 1) the MySQL docs are better (sorry - I found them easier to read, and > more comprehensive; I had an easier time finding the answers I needed) Huh. I had the opposite experience. Each to his own. I think everybody agrees PG needs a better tuning doc (

Re: [PERFORM] Sun performance - Major discovery!

Andrew Sullivan wrote: > On Thu, Oct 09, 2003 at 01:04:23PM -0400, Jeff wrote: > > > > So you think we should leave PG alone and let it run horrifically slowly? > > Do you have a better idea of how to do this? > > Given the point in the release cycle, mightn't the FAQ_Solaris or > some other plac

Re: [PERFORM] Compare rows

Josh Berkus kirjutas N, 09.10.2003 kell 08:36: > Chris, > > The need to do a lot of joins would likely hurt performance somewhat, > > as well as the way that it greatly increases the number of rows. > > Although you could always split it into several tables, one for each > > "value_type", and UNIO

Re: [PERFORM] PostgreSQL vs MySQL

This is a timely thread for myself, as I'm in the middle of testing both databases as an Oracle replacement.   As of this moment, I know more about MySQL (tuning, setup, features) than I do about Postgres. Not because I like MySQL more, but because   1) the MySQL docs are better (sorry - I fo

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

Yeah, I had similar thought to Oliver's and suspected that this would be the answer. Also, while it's not too hard to do this for a single platform, it gets complecated once you start looking at different ones. Josh, let me know when you're ready to do this. I'll try to help, although my perl's

Re: [PERFORM] Sun performance - Major discovery!

On Thu, Oct 09, 2003 at 01:04:23PM -0400, Jeff wrote: > > So you think we should leave PG alone and let it run horrifically slowly? > Do you have a better idea of how to do this? Given the point in the release cycle, mightn't the FAQ_Solaris or some other place be better for this for now? I agre

Re: [PERFORM] Sun performance - Major discovery!

Jeff wrote: > On Thu, 9 Oct 2003, Kenneth Marshall wrote: > > > Jeff, > > > > My first concern with the -fast option is that it makes an executable > > that is specific for the platform on which the compilation is run > > unless other flags are given. My second concern is the effect it has > > on

Re: [PERFORM] Sun performance - Major discovery!

On Thu, 9 Oct 2003, Kenneth Marshall wrote: > Jeff, > > My first concern with the -fast option is that it makes an executable > that is specific for the platform on which the compilation is run > unless other flags are given. My second concern is the effect it has > on IEEE floating point behavior

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

Oliver, > I think instead of thinking about where to put the > information about tuning, someone should provide a > "pgsql-autotune". Maybe even a shell script would do the > trick. Well, you see, there's the issue. "I think someone." Lots of people have spoken in favor of an "auto-conf" scrip

Re: [PERFORM] Sun performance - Major discovery!

On Thu, 9 Oct 2003, Bruce Momjian wrote: > > 52 seconds to 19-20 seconds > > Wow, that's dramatic. Do you want to propose some flags for non-gcc > Solaris? Is -fast the only one? Is there one that suppresses those > warnings or are they OK? > Well. As I said, I didn't see an obvious way to hid

Re: [PERFORM] Sun performance - Major discovery!

Jeff wrote: > On Thu, 9 Oct 2003, Bruce Momjian wrote: > > > > > What is the performance win for the -fast flag again? > > > > --- > > > 52 seconds to 19-20 seconds Wow, that's dramatic. Do you want to propose some flags for

Re: [PERFORM] Sun performance - Major discovery!

On Thu, 9 Oct 2003, Bruce Momjian wrote: > > What is the performance win for the -fast flag again? > > --- > 52 seconds to 19-20 seconds -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/

Re: [PERFORM] Linux filesystem shootout

On Thu, 9 Oct 2003, Shridhar Daithankar wrote: > Kaarel wrote: > >>>http://www.ussg.iu.edu/hypermail/linux/kernel/0310.1/0208.html > >>> > >>>Shridhar > >>> > >>> > > I feel incompetent when it comes to file systems. Yet everybody would like to > > have the best file system if given the choic

Re: [PERFORM] Sun performance - Major discovery!

What is the performance win for the -fast flag again? --- Jeff wrote: > On Thu, 9 Oct 2003, Bruce Momjian wrote: > > > > > So you want -fast added as default for non-gcc Solaris? You mentioned > > there is a warning genera

Re: [PERFORM] Compare rows

Josh Berkus wrote: Greg, You lost me on that one. What's a "vertical child table"? Currently, you store data like this: id address uptime speed memory tty 3 67.92 0.3 11.237 6 7 69.51.1 NULL15

Re: [PERFORM] Linux filesystem shootout

Kaarel wrote: http://www.ussg.iu.edu/hypermail/linux/kernel/0310.1/0208.html Shridhar I feel incompetent when it comes to file systems. Yet everybody would like to have the best file system if given the choice...so do I :) Here I am looking at those tables seeing JFS having more green cells

Re: [PERFORM] Linux filesystem shootout

Kaarel wrote: http://www.ussg.iu.edu/hypermail/linux/kernel/0310.1/0208.html Shridhar I feel incompetent when it comes to file systems. Yet everybody would like to have the best file system if given the choice...so do I :) Here I am looking at those tables seeing JFS having more green cells

Re: [PERFORM] Linux filesystem shootout

http://www.ussg.iu.edu/hypermail/linux/kernel/0310.1/0208.html Shridhar I feel incompetent when it comes to file systems. Yet everybody would like to have the best file system if given the choice...so do I :) Here I am looking at those tables seeing JFS having more green c

Re: [PERFORM] Sun performance - Major discovery!

On Thu, 9 Oct 2003, Bruce Momjian wrote: > > So you want -fast added as default for non-gcc Solaris? You mentioned > there is a warning generated that we have to deal with? > Yeah, suncc generates a warning for _every_ file that says: Warning: -xarch=native has been explicitly specified, or imp

Re: [HACKERS] [PERFORM] Sun performance - Major discovery!

On Wed, Oct 08, 2003 at 02:31:29PM -0400, Bruce Momjian wrote: > Well, this is really embarassing. I can't imagine why we would not set > at least -O on all platforms. Looking at the template files, I see > these have no optimization set: I think gcc _used_ to generate bad code on SPARC if you s

Re: [PERFORM] Sun performance - Major discovery!

So you want -fast added as default for non-gcc Solaris? You mentioned there is a warning generated that we have to deal with? --- Jeff wrote: > On Wed, 8 Oct 2003, Neil Conway wrote: > > > Hey Jeff, > > > > On Wed, 2003-10

Re: [PERFORM] Compare rows

Christopher Browne wrote: Wow, that takes me back to a paper I have been looking for for _years_. Some time in the late '80s, probably '88 or '89, there was a paper presented in Communications of the ACM that proposed using this sort of "hypernormalized" schema as a way of having _really_ narrow sc

[PERFORM] Serious Problem with the windows and postgres configuration

DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have

Re: [PERFORM] Sun performance - Major discovery!

On Wed, 8 Oct 2003, Neil Conway wrote: > Hey Jeff, > > On Wed, 2003-10-08 at 11:46, Jeff wrote: > > Yeah - like I expected it was able to generate much better code for > > _bt_checkkeys which was the #1 function in gcc on both sun & linux. > > If you get a minute, would it be possible to compare t

Re: [PERFORM] Linux filesystem shootout

I should at least read the URLs before re-posting info. My bad, I'm utterly sorry about this... :-( Cheers, -- Grega Bremec Sistemska administracija in podpora grega.bremec-at-noviforum.si http://najdi.si/ http://www.noviforum.si/ pgp0.pgp Description: PGP signature

Re: [PERFORM] Linux filesystem shootout

...and on Thu, Oct 09, 2003 at 04:42:53PM +0530, Shridhar Daithankar used the keyboard: > > http://www.ussg.iu.edu/hypermail/linux/kernel/0310.1/0208.html > > Shridhar My $0.1: I just stumbled across an interesting filesystem comparison table today, comparing ext2/ext3/reiser/reiser4/jfs/xfs on

Re: [PERFORM] Compare rows

The world rejoiced as [EMAIL PROTECTED] (Josh Berkus) wrote: > Chris, >> Some time in the late '80s, probably '88 or '89, there was a paper >> presented in Communications of the ACM that proposed using this sort >> of "hypernormalized" schema as a way of having _really_ narrow schemas >> that would

[PERFORM] Linux filesystem shootout

http://www.ussg.iu.edu/hypermail/linux/kernel/0310.1/0208.html Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

On 09/10/2003 09:29 Oliver Scheit wrote: Hi guys, I followed the discussion and here are my 0.2$: I think instead of thinking about where to put the information about tuning, someone should provide a "pgsql-autotune". Maybe even a shell script would do the trick. It's not so hard to find out, how

Re: [PERFORM] PostgreSQL vs. MySQL

On Wed, Oct 08, 2003 at 01:28:53PM -0400, Bruce Momjian wrote: > > Agreed. Text added to install docs: [&c.] I think this is just right. It tells a user where to find the info needed, doesn't reproduce it all over the place, and still points out that this is something you'd better do. Combine

[PERFORM] go for a script! / ex: PostgreSQL vs. MySQL

Hi guys, I followed the discussion and here are my 0.2$: I think instead of thinking about where to put the information about tuning, someone should provide a "pgsql-autotune". Maybe even a shell script would do the trick. It's not so hard to find out, how much memory is installed, and IMHO SHAR

Re: [PERFORM] IMMUTABLE function's flag do not work: 7.3.4, plpgsql

On Wed, 8 Oct 2003, Tom Lane wrote: > Andriy Tkachuk <[EMAIL PROTECTED]> writes: > > At second. calc_total() is immutable function: > > but it seems that it's not cached in one session: > > It's not supposed to be. but it's written id doc: IMMUTABLE indicates that the function alwa