Re: [PERFORM] atrocious update performance
I've been following this thread closely as I have the same problem with an UPDATE. Everything is identical here right down to the strace output. Has anyone found a workaround or resolved the problem? If not, I have test systems here which I can use to help up test and explore. Greg -- Greg Spiegelberg Sr. Product Development Engineer Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: [EMAIL PROTECTED] Cranel. Technology. Integrity. Focus. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] two seperate queries run faster than queries ORed together
explain SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260 AND (u.status = 3 ) AND NOT u.boolfield ; QUERY PLAN -- Aggregate (cost=45707.84..45707.84 rows=1 width=4) - Nested Loop (cost=0.00..45707.16 rows=273 width=4) - Seq Scan on usertable u (cost=0.00..44774.97 rows=272 width=4) Filter: ((pkey = 260) AND (status = 3) AND (NOT boolfield)) - Index Scan using d_pkey on d (cost=0.00..3.41 rows=1 width=4) Index Cond: (d.ukey = outer.ukey) explain SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260 AND (d.status = 3 ) AND NOT u.boolfield ; QUERY PLAN -- Aggregate (cost=28271.38..28271.38 rows=1 width=4) - Nested Loop (cost=0.00..28271.38 rows=1 width=4) - Seq Scan on d (cost=0.00..28265.47 rows=1 width=4) Filter: (status = 3) - Index Scan using u_pkey on u (cost=0.00..5.89 rows=1 width=4) Index Cond: ((outer.ukey = u.ukey) AND (u.pkey = 260)) Filter: (NOT boolfield) explain SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260 AND (u.status = 3 OR d.status = 3 ) AND NOT u.boolfield ; QUERY PLAN --- Aggregate (cost=128867.45..128867.45 rows=1 width=4) - Hash Join (cost=32301.47..128866.77 rows=272 width=4) Hash Cond: (outer.ukey = inner.ukey) Join Filter: ((inner.status = 3) OR (outer.status = 3)) - Seq Scan on u (cost=0.00..41215.97 rows=407824 width=6) Filter: ((pkey = 260) AND (NOT boolfield)) - Hash (cost=25682.98..25682.98 rows=1032998 width=6) - Seq Scan on d (cost=0.00..25682.98 rows=1032998 width=6) ... so what do I do? It would be a real pain to rewrite this query to run twice and add the results up, especially since I don't always know beforehand when it will be faster based on different values to the query. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] two seperate queries run faster than queries ORed together
On Thursday 18 March 2004 21:21, Joseph Shraibman wrote: explain SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260 AND (u.status = 3 OR d.status = 3 ) AND NOT u.boolfield ; QUERY PLAN --- Aggregate (cost=128867.45..128867.45 rows=1 width=4) - Hash Join (cost=32301.47..128866.77 rows=272 width=4) Hash Cond: (outer.ukey = inner.ukey) Join Filter: ((inner.status = 3) OR (outer.status = 3)) - Seq Scan on u (cost=0.00..41215.97 rows=407824 width=6) Filter: ((pkey = 260) AND (NOT boolfield)) There's your problem. For some reason it thinks it's getting 407,824 rows back from that filtered seq-scan. I take it that pkey is a primary-key and is defined as being UNIQUE? If you actually did have several hundred thousand matches then a seq-scan might be sensible. I'd start by analyze-ing the table in question, and if that doesn't have any effect look at the column stats and see what spread of values it thinks you have. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] two seperate queries run faster than queries ORed together
Richard Huxton wrote: On Thursday 18 March 2004 21:21, Joseph Shraibman wrote: explain SELECT COUNT(u.ukey) FROM u, d WHERE d.ukey = u.ukey AND u.pkey = 260 AND (u.status = 3 OR d.status = 3 ) AND NOT u.boolfield ; QUERY PLAN --- Aggregate (cost=128867.45..128867.45 rows=1 width=4) - Hash Join (cost=32301.47..128866.77 rows=272 width=4) Hash Cond: (outer.ukey = inner.ukey) Join Filter: ((inner.status = 3) OR (outer.status = 3)) - Seq Scan on u (cost=0.00..41215.97 rows=407824 width=6) Filter: ((pkey = 260) AND (NOT boolfield)) There's your problem. For some reason it thinks it's getting 407,824 rows back from that filtered seq-scan. I take it that pkey is a primary-key and is defined as being UNIQUE? If you actually did have several hundred thousand matches then a seq-scan might be sensible. No, pkey is not the primary key in this case. The number of entries in u that have pkey 260 and not boolfield is 344706. The number of those that have status == 3 is 7. To total number of entries in d that have status == 3 is 4. I'd start by analyze-ing the table in question, Is done every night. The problem is that it seems the planner doesn't think to do the different parts of the OR seperately and then combine the answers. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] two seperate queries run faster than queries ORed together
Joseph Shraibman [EMAIL PROTECTED] writes: No, pkey is not the primary key in this case. The number of entries in u that have pkey 260 and not boolfield is 344706. ... and every one of those rows *must* be included in the join input, regardless of its status value, because it might join to some d row that has status=3. Conversely, every single row of d must be considered in the join because it might join to some u row with status=3. So any way you slice it, this query requires a large and expensive join operation, no matter that there are only a few rows with the right status values in the other table. I'd rewrite the query if I were you. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] two seperate queries run faster than queries ORed together
Tom Lane wrote: Joseph Shraibman [EMAIL PROTECTED] writes: No, pkey is not the primary key in this case. The number of entries in u that have pkey 260 and not boolfield is 344706. ... and every one of those rows *must* be included in the join input, *If* you use one big join in the first place. If postgres ran the query to first get the values with status == 3 from u, then ran the query to get the entries from d, then combined them, the result would be the same but the output faster. Instead it is doing seq scans on both tables and doing an expensive join that returns only a few rows. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] two seperate queries run faster than queries ORed
On Mon, 22 Mar 2004, Joseph Shraibman wrote: Tom Lane wrote: Joseph Shraibman [EMAIL PROTECTED] writes: No, pkey is not the primary key in this case. The number of entries in u that have pkey 260 and not boolfield is 344706. ... and every one of those rows *must* be included in the join input, *If* you use one big join in the first place. If postgres ran the query to first get the values with status == 3 from u, then ran the query to get the entries from d, then combined them, the result would be the same but the output faster. Instead it is doing seq scans on both tables and Well, you have to be careful on the combination to not give the wrong answers if there's a row with u.status=3 that matches a row d.status=3. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] two seperate queries run faster than queries ORed together
Stephan Szabo wrote: On Mon, 22 Mar 2004, Joseph Shraibman wrote: Tom Lane wrote: Joseph Shraibman [EMAIL PROTECTED] writes: No, pkey is not the primary key in this case. The number of entries in u that have pkey 260 and not boolfield is 344706. ... and every one of those rows *must* be included in the join input, *If* you use one big join in the first place. If postgres ran the query to first get the values with status == 3 from u, then ran the query to get the entries from d, then combined them, the result would be the same but the output faster. Instead it is doing seq scans on both tables and Well, you have to be careful on the combination to not give the wrong answers if there's a row with u.status=3 that matches a row d.status=3. Right you would have to avoid duplicates. The existing DISTINCT code should be able to handle that. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] two seperate queries run faster than queries ORed
Stephan Szabo [EMAIL PROTECTED] writes: Well, you have to be careful on the combination to not give the wrong answers if there's a row with u.status=3 that matches a row d.status=3. We could in theory handle that using something similar to the method currently used for OR indexscans (that is, rather than doing either UNION- or UNION-ALL-like processing, we drop tuples from later scans that meet the qual tests of the earlier scans). However I don't see any clean way in the current planner to cost out both approaches and pick the cheaper one. It looks to me like we'd have to do over the *entire* join planning process each way, which is ugly as well as unreasonably expensive. The problem is that the OR approach only wins when the component clauses of the OR can drop down to lower levels of the plan tree if they are considered separately. But a plan tree with a restriction at a low level and one without it are two different things, and the dynamic-programming approach we use to build up join plans doesn't yield the same solutions. (As indeed it shouldn't, since the whole point of Joseph's example is to get fundamentally different plans for the two parts of the OR.) We could possibly approach it heuristically, that is examine the clauses and try to guess whether it's better to split them apart or not. But even assuming that we punt on that part of the problem, it seems like a mess. For instance suppose that there are additional relations in the query that aren't mentioned in the OR clause. The planner may want to join some of those relations in advance of forming the join that the OR itself describes. Pushing down different parts of the OR might cause the best join path to change. How could you merge multiple scans if some include extra relations and some don't? In short, I see how such a plan could be executed, but I don't see any effective approach for generating the plan ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] [ADMIN] Databases Vs. Schemas
Subbiah, Stalin [EMAIL PROTECTED] writes: Is it better to have 1000 databases vs 1000 schemas in a database cluster. You almost certainly want to go for schemas, at least from a performance point of view. The overhead of a schema is small (basically one more row in pg_namespace) whereas the overhead of a database is not trivial. The main reason you might not want to use schemas is if you want fairly airtight separation between different services. Separate databases would prevent services from looking at each others' catalog entries. regards, tom lane ---(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] Benchmarking postgres on Solaris/Linux
As anyone done benchmarking tests with postgres running on solaris and linux (redhat) assuming both environment has similar hardware, memory, processing speed etc. By reading few posts here, i can see linux would outperform solaris cause linux being very good at kernel caching than solaris which is being the key performance booster for postgres. what is the preferred OS for postgres deployment if given an option between linux and solaris. As well as filesystem to be used (xfs, ufs, ext3...). Any pointer to source of information is appreciated. Thanks, Stalin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [HACKERS] fsync method checking
On Thu, Mar 18, 2004 at 01:50:32PM -0500, Bruce Momjian wrote: I'm not sure I believe these numbers at all... my experience is that getting trustworthy disk I/O numbers is *not* easy. These numbers were reproducable on all the platforms I tested. It's not because they are reproducable that they mean anything in the real world. Kurt ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] [HACKERS] fsync method checking
Here are my results on Linux 2.6.1 using cvs version 1.7. Those times with 20 seconds, you really hear the disk go crazy. And I have the feeling something must be wrong. Those results are reproducible. Kurt Simple write timing: write0.139558 Compare fsync times on write() and non-write() descriptor: (If the times are similar, fsync() can sync data written on a different descriptor.) write, fsync, close 8.249364 write, close, fsync 8.356813 Compare one o_sync write to two: one 16k o_sync write28.487650 two 8k o_sync writes 2.310304 Compare file sync methods with one 8k write: (o_dsync unavailable) open o_sync, write 1.010688 write, fdatasync25.109604 write, fsync, 26.051218 Compare file sync methods with 2 8k writes: (The fastest should be used for wal_sync_method) (o_dsync unavailable) open o_sync, write 2.212223 write, fdatasync27.439907 write, fsync, 27.772294 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [HACKERS] fsync method checking
On Thu, Mar 18, 2004 at 03:34:21PM -0500, Bruce Momjian wrote: Kurt Roeckx wrote: Here are my results on Linux 2.6.1 using cvs version 1.7. Those times with 20 seconds, you really hear the disk go crazy. And I have the feeling something must be wrong. Those results are reproducible. Wow, your O_SYNC times are great. Where can I buy some? :-) Anyway, we do need to find a way to test this because obviously there is huge platform variability. New results with version 1.8: Simple write timing: write0.150613 Compare fsync times on write() and non-write() descriptor: (If the times are similar, fsync() can sync data written on a different descriptor.) write, fsync, close 9.170472 write, close, fsync 8.851715 Compare one o_sync write to two: one 16k o_sync write 2.617860 two 8k o_sync writes 2.563437 Compare file sync methods with one 8k write: (o_dsync unavailable) open o_sync, write 1.031721 write, fdatasync25.599010 write, fsync, 26.192824 Compare file sync methods with 2 8k writes: (The fastest should be used for wal_sync_method) (o_dsync unavailable) open o_sync, write 2.268718 write, fdatasync27.029396 write, fsync, 27.399243 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] [HACKERS] fsync method checking
On Thu, Mar 18, 2004 at 02:22:10PM -0500, Bruce Momjian wrote: OK, what better test do you suggest? Right now, there has been no testing of these. I suggest you start by doing atleast preallocating a 16 MB file and do the tests on that, to atleast be somewhat simular to what WAL does. I have no idea what the access pattern is for normal WAL operations or how many times it gets synched. Does it only do f(data)sync() at commit time, or for every block it writes? I think if you write more data you'll see more differences between O_(D)SYNC and f(data)sync(). I guess it can depend on if you have lots of small transactions, or more big ones. Atleast try to make something that covers different access patterns. Kurt ---(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
Re: [PERFORM] [HACKERS] fsync method checking
[EMAIL PROTECTED] writes: I could certainly do some testing if you want to see how DBT-2 does. Just tell me what to do. ;) Just do some runs that are identical except for the wal_sync_method setting. Note that this should not have any impact on SELECT performance, only insert/update/delete performance. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] [HACKERS] fsync method checking
[EMAIL PROTECTED] wrote: On 18 Mar, Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] writes: 1) This is an OSS project. Why not just recruit a bunch of people on PERFORMANCE and GENERAL to test the 4 different synch methods using real databases? No test like reality, I say I agree --- that is likely to yield *far* more useful results than any standalone test program, for the purpose of finding out what wal_sync_method to use in real databases. However, there's a second issue here: we would like to move sync/checkpoint responsibility into the bgwriter, and that requires knowing whether it's valid to let one process fsync on behalf of writes that were done by other processes. That's got nothing to do with WAL sync performance. I think that it would be sensible to make a test program that focuses on this one specific question. (There has been some handwaving to the effect that everybody knows this is safe on Unixen, but I question whether the handwavers have seen the internals of HPUX or AIX for instance; and besides we need to worry about Windows now.) I could certainly do some testing if you want to see how DBT-2 does. Just tell me what to do. ;) To test, you would run from CVS version src/tools/fsync, find the fastest fsync method from the last group of outputs, then try the wal_fsync_method setting to see if the one that tools/fsync says is fastest is actually fastest. However, it might be better to run your tests and get some indication of how frequently writes and fsync's are going to WAL and modify tools/fsync to match what your DBT-2 test does. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
Re: [PERFORM] Databases Vs. Schemas
Stalin, We are evaluating the options for having multiple databases vs. schemas on a single database cluster for a custom grown app that we developed. Each app installs same set of tables for each service. And the service could easily be in thousands. so Is it better to have 1000 databases vs 1000 schemas in a database cluster. What are the performance overhead of having multiple databases vs. schemas (if any). I'm leaning towards having schemas rather than databases but i would like to get others opinion on this. Appreciate your reply. No performance difference AFAIK. The real question is whether you have to have queries joining several databases. If yes, use Schema; if no, use databases. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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
Re: [PERFORM] Benchmarking postgres on Solaris/Linux
Stalin, As anyone done benchmarking tests with postgres running on solaris and linux (redhat) assuming both environment has similar hardware, memory, processing speed etc. By reading few posts here, i can see linux would outperform solaris cause linux being very good at kernel caching than solaris which is being the key performance booster for postgres. what is the preferred OS for postgres deployment if given an option between linux and solaris. As well as filesystem to be used (xfs, ufs, ext3...). Any pointer to source of information is appreciated. Most of that is a matter of opinion. Read the cumulative archives of this list. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Benchmarking postgres on Solaris/Linux
The hardware platform to deploy onto may well influence your choice : Intel is usually the most cost effective , which means using Linux makes sense in that case (anybody measured Pg performance on Solaris/Intel?). If however, you are going to run a very big in some sense database, then 64 bit hardware is desirable and you can look at the Sun offerings. In this case you can run either Linux or Solaris (some informal benchmarks suggest that for small numbers of cpus, Linux is probably faster). It might be worth considering Apple if you want a 64-bit chip that has a clock speed comparable to Intel's - the Xserv is similarly priced to Sun V210 (both dual cpu 1U's). Are you free to choose any hardware? best wishes Mark Subbiah, Stalin wrote: (snipped) what is the preferred OS for postgres deployment if given an option between linux and solaris. ---(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
