Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
Hello. Just my 2 cents, and not looking to the technical aspects: setting up PSQL is the weakest point of PSQL as we have experienced ourself, once it is running it is great. I can imagine that a lot of people of stops after their first trials after they have experienced the troubles and performance of a standard set up. This is ofcourse a lost user forever. So anything that could be done to get an easier and BETTER setup would strongly enhance PSQL. My 2 cents. Henk Sanders -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Tom Lane Verzonden: vrijdag 27 april 2007 16:37 Aan: Carlos Moreno CC: PostgreSQL Performance Onderwerp: Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning Carlos Moreno [EMAIL PROTECTED] writes: Tom Lane wrote: But the fundamental problem remains that we don't know that much about how the installation will be used. Notice that the second part of my suggestion covers this --- have additional switches to initdb That's been proposed and rejected before, too; the main problem being that initdb is frequently a layer or two down from the user (eg, executed by initscripts that can't pass extra arguments through, even assuming they're being invoked by hand in the first place). regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 6: explain analyze is your friend
FW: [PERFORM] Simple join optimized badly?
Hello. Simply jumping on the bandwagon, just my 2 cents: why not just like in some other (commercial) databases: a statement to say: use index I know this is against all though but if even the big ones can not resist the pressure of their users, why not? Henk Sanders -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Bucky Jordan Verzonden: woensdag 11 oktober 2006 16:27 Aan: Tom Lane; Brian Herlihy CC: Postgresql Performance Onderwerp: Re: [PERFORM] Simple join optimized badly? Brian Herlihy [EMAIL PROTECTED] writes: What would it take for hints to be added to postgres? A *whole lot* more thought and effort than has been expended on the subject to date. Personally I have no use for the idea of force the planner to do exactly X given a query of exactly Y. You don't have exactly Y today, tomorrow, and the day after (if you do, you don't need a hint mechanism at all, you need a mysql-style query cache). IMHO most of the planner mistakes we see that could be fixed via hinting are really statistical estimation errors, and so the right level to be fixing them at is hints about how to estimate the number of rows produced for given conditions. Mind you that's still a plenty hard problem, but you could at least hope that a hint of that form would be useful for more than one query. Do I understand correctly that you're suggesting it might not be a bad idea to allow users to provide statistics? Is this along the lines of I'm loading a big table and touching every row of data, so I may as well collect some stats along the way and I know my data contains these statistical properties, but the analyzer wasn't able to figure that out (or maybe can't figure it out efficiently enough)? While it seems like this would require more knowledge from the user (e.g. more about their data, how the planner works, and how it uses statistics) this would actually be helpful/required for those who really care about performance. I guess it's the difference between a tool advanced users can get long term benefit from, or a quick fix that will probably come back to bite you. I've been pleased with Postgres' thoughtful design; recently I've been doing some work with MySQL, and can't say I feel the same way. Also, I'm guessing this has already come up at some point, but what about allowing PG to do some stat collection during queries? If you're touching a lot of data (such as an import process) wouldn't it be more efficient (and perhaps more accurate) to collect stats then, rather than having to re-scan? It would be nice to be able to turn this on/off on a per query basis, seeing as it could have pretty negative impacts on OLTP performance... - Bucky ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: 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
[PERFORM] x206-x225
Hello list. We have compared 2 IBM x servers: IBM X206 IBM X226 -- --- processor Pentium 4 3.2 Ghz Xeon 3.0 Ghz main memory 1.25 GB 4 GB discs 2 x SCSI RAID11RPM 1 x ATA 7200 RPM LINUX 2.6 (SUSE 9) same PGSQL 7.4same postgresql.conf attached same We have bij means of an informix-4GL program done the following test: create table : name char(18) adres char(20) key integer create index on (key) Time at X206 Time at X226 -- insert record (key goes from 1 to 1) 6 sec. 41 sec. select record (key goes from 1 to 1) 4 4 delete record (key goes from 1 to 1) 6 41 This is ofcourse a totally unexpected results (you should think off the opposite). Funny is that the select time is the same for both machines. Does anybody has any any idea what can cause this strange results or where we can start our investigations? Regards Henk Sanders postgresql.conf-74 Description: Binary data ---(end of broadcast)--- TIP 1: 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
[PERFORM] date - range
Anybody a solution for the next problem: people can subscribe to a service for 1 or more days (upto a max. of 365). So in the database is stored: first_date and last_date To select which people are subscribed for a certain date (e.g. today) we use a select like select ... where first_date = today and last_date = today Whatever index we create system always does a sequential scan (which I can understand). Has someone a smarter solution? All suggestions will be welcomed. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
