Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1
Mark Kirkwood [EMAIL PROTECTED] writes: I spent today looking at getting this patch into a self contained state. Working against HEAD I'm getting bogged down in the PathKeyItem to PathKey/EquivalenceClass/EquivalenceMember(s) change. So I figured I'd divide and conquer to some extent, and initially provide a patch: - against 8.2.(5) - self contained (i.e no mystery functions) That would be helpful for me. It would include the bits I'm looking for. The next step would be to update to to HEAD. That would hopefully provide some useful material for others working on this. If that's not too much work then that would be great but if it's a lot of work then it may not be worth it if I'm planning to only take certain bits. On the other hand if it's good then we might just want to take it wholesale and then add to it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[PERFORM] hp ciss on freebsd
Hi. We are using a HP DL 380 G5 with 4 sas-disks at 10K rpm. The controller is a built in ciss-controller with 256 MB battery-backed cache. It is partitioned as raid 1+0. Our queries are mainly selects. I will get four 72 GB sas-disks at 15K rpm. Reading the archives suggest raid 1+0 for optimal read/write performance, but with a solid raid-controller raid 5 will also perform very well when reading. Is the ciss-controller found in HP-servers a better raid-controller compared to the areca-raid-controller mentioned on this list? -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Database connections and stored procs (functions)
Hi, I am running postgres 8.2 on RH linux. My daemon downloads files and then inserts the data into preliminary tables, and finally calls a stored procedure which reads data from a view and inserts into the final table. I have a bit of a peculiar problem. (I understand this may not be the right venue). My deamon calls a stored procedure SP_LoadFiles(). The daemon stops syncing at about 6:30 pm and restarts syncing at about 7:30 am. Every day, I have to manually re-start the daemon for the function sp_LoadFiles() to actually load the files. I can see that the procedure is being called, but it does not load the data. If I run the procedure manually via psql : select * from sp_loadfiles(); it works and the data is loaded. my stored proc sp_loadfiles is accessing a View which is accessing a couple of tables. There is no dynamic sql being generated, just inserts from the view. Is this a connection issue? Do I have to end the daemons db connection. Is this set in the postgresql.conf? Thank you. Radhika -- It is all a matter of perspective. You choose your view by choosing where to stand. --Larry Wall ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] hp ciss on freebsd
On Nov 5, 2007, at 8:19 AM, Claus Guttesen wrote: Is the ciss-controller found in HP-servers a better raid-controller compared to the areca-raid-controller mentioned on this list? I've had great success with the P600 controller (upgraded to 512MB bbwc) plugged into an MSA70 with a pile of SAS disks. I'm using R6 (ADG) and getting some crazy good numbers with it. My newest box has a built-in p400, that did ok, but not as good as the p600. HP also has the P800 available as well. Your best bet is to load up some data, and do some testing. Check out the pgiosim project on pgfoundry, it sort of simulates a pg index scan, which is probably what you'll want to focus on more than seq read speed. -- Jeff Trout [EMAIL PROTECTED] http://www.dellsmartexitin.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] hp ciss on freebsd
On Mon, 5 Nov 2007, Claus Guttesen wrote: Is the ciss-controller found in HP-servers a better raid-controller compared to the areca-raid-controller mentioned on this list? If you search the archives for cciss you'll see a few complaints about this controller not working all that well under Linux. The smart thing to do regardless of what other people say is to test yourself and see if you're meeting expectations. I've got a sample of how a single disk performs with an Areca controller you can use as a baseline at http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] index stat
PostgreSQL:8.2.4 I am collecting statistics info now on my database. I have used the following two queries: select * from pg_stat_all_indexes; select * from pg_statio_all_indexes; How can I use the information from these two queries to better optimize my indexes? Or maybe even get rid of some unnecessary indexes. Example output: relid | indexrelid | schemaname |relname| indexrelname| idx_blks_read | idx_blks_hit -++---+---+- --+---+-- 16801 | 57855 | a | screen| screen_index1 | 1088 | 213618 16801 | 57857 | a | screen| screen_index3 | 905 | 201219 16803 | 16805 | pg_toast | pg_toast_16801| pg_toast_16801_index | 3879 | 1387471 16978 | 16980 | pg_toast | pg_toast_16976| pg_toast_16976_index | 0 |0 942806 | 942822 | b| question_result_entry | question_result_entry_index1 |18 |0 942806 | 942824 | b| question_result_entry | question_result_entry_index2 |18 |0 942806 | 942828 | b| question_result_entry | question_result_entry_index3 |18 |0 relid | indexrelid | schemaname |relname| indexrelname| idx_scan | idx_tup_read | idx_tup_fetch -++---+---+- --+---+--+--- 16801 | 57855 | a| screen | screen_index1 | 48693 | 1961745 | 1899027 16801 | 57857 | a| screen | screen_index3 | 13192 | 132214 | 87665 16803 | 16805 | pg_toast | pg_toast_16801| pg_toast_16801_index |674183 | 887962 | 887962 16978 | 16980 | pg_toast | pg_toast_16976| pg_toast_16976_index | 0 |0 | 0 942806 | 942822 | b| question_result_entry | question_result_entry_index1| 0 |0 | 0 942806 | 942824 | b| question_result_entry | question_result_entry_index2| 0 |0 | 0 942806 | 942828 | b| question_result_entry | question_result_entry_index3| 0 |0 | 0 Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu
[PERFORM] Training Recommendations
PostgreSQL: 8.2.4 Does anyone have any companies they would recommend using for performance tuning training of PostgreSQL for Linux? Or general DBA training? Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu
Re: [PERFORM] Migrating to 8.3 - checkpoints and background writer
On Nov 4, 2007, at 6:33 PM, Greg Smith wrote: For those of you considering a move to the upcoming 8.3 release, now in beta, I've written some documentation on the changes made in checkpoint and background writer configuration in the new version: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm Since the first half of that covers the current behavior in 8.1 and 8.2, those sections may be helpful if you'd like to know more about checkpoint slowdowns and ways to resolve them even if you have no plans to evaluate 8.3 yet. I'd certainly encourage anyone who can run the 8.3 beta to consider adding some tests in this area while there's still time to correct any issues encountered before the official release. Greg, thanks a lot of this. I'd say this should definitely be linked to from the main site's techdocs section. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PERFORM] Which index methodology is better?-
I have a question. Consider this scenario. Table customer ( customer_id int8, customer_l_name varchar(35), customer_f_name varchar(30), customer_addr_1 varchar(100),\ customer_addr_2 varchar(100), customer_city varchar(50), customer_state char(2), customer_zip varchar(9) ); On this table, a customer can search by customer_id, customer_l_name, and customer_f_name. Is it better to create 3 indexes, or one index on the three columns? I did some initial testing with index customer_test_idx(customer_id, customer_l_name, customer_f_name) and postgres would use the index for select * from customer where customer_f_name = 'zxy' - so the single index will cover the three. My question is, is this better? Does it end up using less memory and/or disk or more? I am trying to find ways to keep more of my customers databases in memory, and I am thinking that loading one index is probably a little better than loading three. Thanks for any advice, Chris PG 8.1 RH 4.0 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Which index methodology is better?-
Chris Hoover [EMAIL PROTECTED] writes: Is it better to create 3 indexes, or one index on the three columns? This is covered in considerable detail in the fine manual: http://www.postgresql.org/docs/8.2/static/indexes.html See particularly sections 11.3 and 11.4 regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Which index methodology is better?-
Chris Hoover wrote: On this table, a customer can search by customer_id, customer_l_name, and customer_f_name. Is it better to create 3 indexes, or one index on the three columns? I did some initial testing with index customer_test_idx(customer_id, customer_l_name, customer_f_name) and postgres would use the index for select * from customer where customer_f_name = 'zxy' - so the single index will cover the three. Postgres can use the index in that case, but it's going to have to scan the whole index, which is a lot slower than looking up just the needed rows. If you do an EXPLAIN ANALYZE on that query, and compare it against select * from customer where customer_id = 123, you'll see that it's a lot more expensive. I'd recommend having separate indexes. Having just one index probably does take less space, but the fact that you don't have to always scan all of it probably outweighs that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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
Re: [PERFORM] Database connections and stored procs (functions)
On 11/5/07, Radhika S [EMAIL PROTECTED] wrote: Hi, I am running postgres 8.2 on RH linux. My daemon downloads files and then inserts the data into preliminary tables, and finally calls a stored procedure which reads data from a view and inserts into the final table. I have a bit of a peculiar problem. (I understand this may not be the right venue). My deamon calls a stored procedure SP_LoadFiles(). The daemon stops syncing at about 6:30 pm and restarts syncing at about 7:30 am. Every day, I have to manually re-start the daemon for the function sp_LoadFiles() to actually load the files. I can see that the procedure is being called, but it does not load the data. If I run the procedure manually via psql : select * from sp_loadfiles(); it works and the data is loaded. my stored proc sp_loadfiles is accessing a View which is accessing a couple of tables. There is no dynamic sql being generated, just inserts from the view. Is this a connection issue? Do I have to end the daemons db connection. Is this set in the postgresql.conf? the answer to your question probably lies within the log. make sure your daemon is logging the connection attempt and any errors. Check the database log for any problems. My gut is telling me the problem might be on your end (can't be sure with this info). merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Is ANALYZE transactional?
If I do: begin; update some_table set foo = newvalue where a_bunch_of_rows_are_changed; analyze some_table; rollback; does it roll back the statistics? (I think the answer is yes, but I need to be sure.) Thanks, Craig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Is ANALYZE transactional?
Craig James [EMAIL PROTECTED] writes: If I do: begin; update some_table set foo = newvalue where a_bunch_of_rows_are_changed; analyze some_table; rollback; does it roll back the statistics? (I think the answer is yes, but I need to be sure.) Yes --- ANALYZE doesn't do anything magic, just a plain UPDATE of those rows. (You could have easily tested this for yourself...) 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
