[PERFORM] declared cursor uses slow plan

2004-09-16 Thread Kevin Neufeld
Why would postgres use a different query plan for declared cursors than without? I have a relatively simple query that takes about 150ms using explain analyze. However, when I wrap the same query in a declared cursor statement, the subsequent fetch statement takes almost 30seconds. For some

[PERFORM] Comparing user attributes with bitwise operators

2004-09-16 Thread Patrick Clery
I'm working on a dating/personals/match-making site, that has used many different methods of match-making, that all seem to be very slow. One I am attempting now that seems to be an efficient method of storage, but not the best for indexing, is using bitwise operators to compare one person's

Re: [PERFORM] Comparing user attributes with bitwise operators

2004-09-16 Thread Greg Stark
Patrick Clery [EMAIL PROTECTED] writes: Method 3 is the only one that used the index, but the only really acceptable method here is Method 1. My questions are... - Is there any hope in getting this to use an efficient index? - Any mathmaticians know if there is a way to reorder my

Re: [PERFORM] Comparing user attributes with bitwise operators

2004-09-16 Thread Greg Stark
Patrick Clery [EMAIL PROTECTED] writes: Here's the structure of the marital status table: Also I find it very odd that you have a marital status table. marital status is just one attribute of member. Do you expect to have more than one marital status bitfield per member? How would you

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-16 Thread Iain
Joe, Christopher, Joe's example wasn't excluding partions, as he didn't use a predicated UNION ALL view to select from. His queries use an indexed column that allow the various partitions to be probed at low cost, and he was satisfied wth that. My point in my previous post was that you could

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-16 Thread Markus Schaber
Hi, Mischa, On Tue, 14 Sep 2004 22:58:20 GMT Mischa Sandberg [EMAIL PROTECTED] wrote: Googling 'upsert' (an Oraclism, I believe) will get you hits on Oracle and DB2's implementation of MERGE, which does what AMOUNTS to what is described below (one mass UPDATE...FROM, one mass INSERT...WHERE

Re: [PERFORM] Partitioning

2004-09-16 Thread Mike Rylander
On 15 Sep 2004 23:55:24 -0400, Greg Stark [EMAIL PROTECTED] wrote: J. Andrew Rogers [EMAIL PROTECTED] writes: We do something very similar, also using table inheritance I have a suspicion postgres's table inheritance will end up serving as a good base for a partitioned table feature. Is

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-16 Thread Markus Schaber
Hi, Steve, On Wed, 15 Sep 2004 21:17:03 -0700 Steve Atkins [EMAIL PROTECTED] wrote: On Wed, Sep 15, 2004 at 11:16:44AM +0200, Markus Schaber wrote: But you have to add table constraints restricting the time after adding the partition? Uhm... unless I'm confused that's not a meaningful

Re: [PERFORM] Data Warehouse Reevaluation - MySQL vs Postgres --

2004-09-16 Thread Joe Conway
Iain wrote: Joe's example wasn't excluding partions, as he didn't use a predicated UNION ALL view to select from. His queries use an indexed column that allow the various partitions to be probed at low cost, and he was satisfied wth that. Right. My point in my previous post was that you could

Re: [PERFORM] Comparing user attributes with bitwise operators

2004-09-16 Thread Daniel Ceregatti
Christopher Kings-Lynne wrote: Sounds like you want a many-to-many table that maps user_ids to match_ids Then you can put an index over (user_id, match_id) and the search will be very fast. Chris If I understand you correctly, I believe I've tried this approach. While matching on a single

[PERFORM] Article about PostgreSQL and RAID in Brazil

2004-09-16 Thread Josh Berkus
Ricardo, Hello. I've moved your query to a more appropriate mailing list; on PERFORMANCE we discuss RAID all the time. If you don't mind wading through a host of opinions, you'll get plenty here. I've also cc'd our Brazillian PostgreSQL community. Everyone, please note that Ricardo is NOT

Re: [PERFORM] declared cursor uses slow plan

2004-09-16 Thread Tom Lane
Kevin Neufeld [EMAIL PROTECTED] writes: I have a relatively simple query that takes about 150ms using explain analyze. However, when I wrap the same query in a declared cursor statement, the subsequent fetch statement takes almost 30seconds. For some reason, the planner decided to do a

Re: [PERFORM] Article about PostgreSQL and RAID in Brazil

2004-09-16 Thread Daniel Ceregatti
The first part of the article can be found in http://www.sqlmagazine.com.br/colunistas.asp?artigo=Colunistas/RicardoRezende/06_Raid_P1.asp The site seems to be down. I was looking forward to reading it. :( The first and most important step for RAID performance with PostgreSQL is to get a

Re: [PERFORM] Article about PostgreSQL and RAID in Brazil

2004-09-16 Thread Steinar H. Gunderson
On Thu, Sep 16, 2004 at 11:10:13AM -0700, Daniel Ceregatti wrote: Here is a small example of the performance difference with write cache: http://sh.nu/bonnie.txt Am I missing something here? I can't find any tests with the same machine showing the difference between writeback and

Re: [PERFORM] Article about PostgreSQL and RAID in Brazil

2004-09-16 Thread Josh Berkus
Primer, The site seems to be down. I was looking forward to reading it. :( I didn't have a problem. The site *is* in Portuguese, though. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain

Re: [PERFORM] Article about PostgreSQL and RAID in Brazil

2004-09-16 Thread Daniel Ceregatti
Josh Berkus wrote: Primer, The site seems to be down. I was looking forward to reading it. :( I didn't have a problem. The site *is* in Portuguese, though. Yes, it came up finally. Fortunately I'm Brazilian. :) -- Daniel Ceregatti - Programmer Omnis Network,

Re: [PERFORM] Partitioning

2004-09-16 Thread Jim C. Nasby
On Wed, Sep 15, 2004 at 02:09:31PM -0700, J. Andrew Rogers wrote: On Tue, 2004-09-14 at 21:30, Joe Conway wrote: That's exactly what we're doing, but using inherited tables instead of a union view. With inheritance, there is no need to rebuild the view each time a table is added or

Re: [PERFORM] Article about PostgreSQL and RAID in Brazil

2004-09-16 Thread Qing Zhao
Hi, there, I am running PostgreSQL 7.3.4 on MAC OS X G5 with dual processors and 8GB memory. The shared buffer was set as 512MB. The database has been running great until about 10 days ago when our developers decided to add some indexes to some tables to speed up certain uploading ops. Now the CPU

Re: [PERFORM] Article about PostgreSQL and RAID in Brazil

2004-09-16 Thread Jim C. Nasby
On Thu, Sep 16, 2004 at 10:50:33AM -0700, Josh Berkus wrote: The second step is to have lots of disks; 5 drives is a minimum for really good performance. 3-drive RAID5, in particular, is a poor performer for PostgreSQL, often resulting in I/O that is 40% or less as efficient as a single

Re: [PERFORM] Question about PG on OSX

2004-09-16 Thread Josh Berkus
Qing, Please don't start a new question by replying to someone else's e-mail. It confuses people and makes it unlikely for you to get help. My suspicion is that since now a few indexes are added, every ops are run by PostgreSQL with the indexes being used when calculating cost. This leads

Re: [PERFORM] Article about PostgreSQL and RAID in Brazil

2004-09-16 Thread Josh Berkus
Jim, What about benefits from putting WAL and pg_temp on seperate drives? Specifically, we have a box with 8 drives, 2 in a mirror with the OS and WAL and pg_temp; the rest in a raid10 with the database on it. Do you think it would have been better to make one big raid10? What if it was

[PERFORM] indexes make other queries slow!

2004-09-16 Thread Qing Zhao
Josh: Sorry for the reply to the existing subject! The newly added indexes have made all other queries much slower except the uploading ops. As a result, all the CPU's are running crazy but not much is getting finished and our Application Server waits for certain time and then times out.

Re: [PERFORM] indexes make other queries slow!

2004-09-16 Thread Josh Berkus
Qing, My guess is that all the queries that involves the columns that are being indexed need to be rewritten to use the newly created indexes to avoid the performance issues. The reason is that REINDEX does not help either. Does it make sense? What's the rate of updates on the newly

Re: [PERFORM] indexes make other queries slow!

2004-09-16 Thread Jason Coene
My guess is that all the queries that involves the columns that are being indexed need to be rewritten to use the newly created indexes to avoid the performance issues. The reason is that REINDEX does not help either. Does it make sense? Qing, Generally, adding new indexes blindly will

Re: [PERFORM] Partitioning

2004-09-16 Thread J. Andrew Rogers
On Thu, 2004-09-16 at 13:39, Jim C. Nasby wrote: Forgive my ignorance, but I didn't think you could have a table that inherits from a parent not have all the columns. Or is that not what you mean by 'you can drop columns from the table...'? This is one advantage I see to a big UNION ALL

Re: [PERFORM] Article about PostgreSQL and RAID in Brazil

2004-09-16 Thread Jim C. Nasby
On Thu, Sep 16, 2004 at 02:07:37PM -0700, Josh Berkus wrote: Jim, What about benefits from putting WAL and pg_temp on seperate drives? Specifically, we have a box with 8 drives, 2 in a mirror with the OS and WAL and pg_temp; the rest in a raid10 with the database on it. Do you think it

Re: [PERFORM] Large # of rows in query extremely slow, not using

2004-09-16 Thread Stephen Crowley
Here are some results of explain analyze, I've included the LIMIT 10 because otherwise the resultset would exhaust all available memory. explain analyze select * from history where date='2004-09-07' and stock='ORCL' LIMIT 10; Limit (cost=0.00..17.92 rows=10 width=83) (actual

Re: [PERFORM] Large # of rows in query extremely slow, not using

2004-09-16 Thread Joshua D. Drake
When I set enable_seqscan to OFF and force everything to use the index every stock I query returns within 100ms, but turn seqscan back ON and its back up to taking several minutes for non-index using plans. Any ideas? --Stephen Try increasing your statistics target and re-running analyze. Try say