Re: [PERFORM] why is a constraint not 'pushed down' into a subselect when this subselect is using a 'group by' ??
We don't attempt to make every possible inference (and I don't think you'd like it if we did). I wasn't really asking you to, either ;)) Just trying to achieve a more in-depth understanding of the way things work. This example doesn't persuade me that it would be worth expending the cycles to do so. In the real thing I can easily get good processing times by adding an extra join with article inside in the group by and simply use the constraint on that as well, so I'm ok with any choice you make on this. I thought this might have been some kind of special case though, given its occurence on the use of group by. for example, zero and minus zero in IEEE-standard float arithmetic. Good example, brings back a few memories as well ;) Thanks for your explanation, Tom! -- Best, Frank. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[PERFORM] Visual Explain
I've known about this tool for a while, but it seems many people do not know of its existence and I think it would be useful to a lot of people who have a hard time reading explain analyze output. (And even those who can read them without blinking.. when you get deep in join hell it gets tricky!) Red Hat Visual Explain - part of Red Hat Database. It is what the name implies - a graphical (java) program to draw a picture of your query plan (along with all the juicy information explain analyze provides). I just tried it out today and after upgrading my JDBC to 7.4 it worked fine (If you get a message about SET AUTOCOMMIT then you need to upgrade your jdbc jar) Quite handy for getting a grasp on stupidly large query plans. http://sources.redhat.com/rhdb/visualexplain.html I used the CVS version, I have no idea how well the official releases work. Anyone else using it? -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] Basic Postgresql Performance Question
Hi, I currently have a mysql server running with a database of around 800 gb. The problem is that the server is old (500 MHz Pentium III with 512 MB RAM) and I want to change this to a new server and convert the existing database to Postgresql on Debian (I assume that Postgresql offers better performance for complex read only queries on large databases), though I was wondering if 1. It is possible to have some sort of load-balancing through buying many computers without replication, i.e have one server which has the databases and then other servers which has no database but just exists to balance the memory and processor load? (I have heard this is possible with C-JDBC)It is difficult to have enough space to replicate a 600 gb database across all computers) 2. It is advantageous to buy AMD 64 rather than the Pentium IV? Any thoughts? Thanks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Basic Postgresql Performance Question
On Wed, 2004-06-16 at 13:15, Bill wrote: Hi, I currently have a mysql server running with a database of around 800 gb. The problem is that the server is old (500 MHz Pentium III with 512 MB RAM) and I want to change this to a new server and convert the existing database to Postgresql on Debian (I assume that Postgresql offers better performance for complex read only queries on large databases), Usually, but there are always queries that run faster or slower on a given database due to differences in architecture and design. For instance PostgreSQL tends to be slow when doing max/min aggs, but faster when doing things involving complex joins and unions. though I was wondering if 1. It is possible to have some sort of load-balancing through buying many computers without replication, i.e have one server which has the databases and then other servers which has no database but just exists to balance the memory and processor load? (I have heard this is possible with C-JDBC)It is difficult to have enough space to replicate a 600 gb database across all computers) That depends. Most databases are first I/O bound, then memory bound, then CPU bound, in that order. With an 800Gb database your main cost is gonna be moving data off of the platters and into memory, then having the memory to hold the working sets, then the CPU to mush it together. Now, if you're reading only tiny portions at a time, but doing lots of strange work on them, say weather forcasting, then you might be CPU bound. But without knowing what your usage patterns are like, we don't know whether or not running on multiple boxes would help. There are replication systems, but there's no such thing as a free lunch. 2. It is advantageous to buy AMD 64 rather than the Pentium IV? Yes and no. If having more than 2 gigs of ram is important, 64 bit architecures run faster than 32 bit, where having over 2 gigs usually results in a slow down due to the memory switching they use. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Basic Postgresql Performance Question
* Scott Marlowe ([EMAIL PROTECTED]) wrote: On Wed, 2004-06-16 at 13:15, Bill wrote: 2. It is advantageous to buy AMD 64 rather than the Pentium IV? Yes and no. If having more than 2 gigs of ram is important, 64 bit architecures run faster than 32 bit, where having over 2 gigs usually results in a slow down due to the memory switching they use. This is truer on more traditional 64bit platforms than on amd64 which has more differences than just the ability to handle 64bit size things. amd64 also gives you access to more registers than were on the register-starved i386 platforms which increases the speed for most applications where it usually wouldn't when recompiled for 64bit. Stephen signature.asc Description: Digital signature
