Re: [PERFORM] anti-join chosen even when slower than old plan
Hello, Just a short though: Is it imaginable to compare the prognoses of the plans with the actual results and somehow log the worst cases ? a) to help the DBA locate bad statistics and queries b) as additional information source for the planner This could possibly affect parameters of your formula on the fly. best regards, Marc Mamin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] do temporary tables have hint bits?
I was doing some testing with temporary tables using this sql: begin; select pg_sleep(30); create temporary TABLE foo (a int, b int, c int, d text); insert into foo SELECT (x%1000) AS a,(x%1001) AS b, (x % 650) as c, '' as d FROM generate_series( 1, 100 ) AS x; -- create temporary TABLE foo AS SELECT (x%1000) AS a,(x%1001) AS b, (x % 650) as c, '' as d FROM generate_series( 1, 100 ) AS x; select count(1) from foo; While it was in pg_sleep, I would attach to the backend process with strace. I observed a few things that I don't yet understand, but one thing I did notice was an I/O pattern (following the count(1)) that seemed to suggest that the table was getting its hint bits set. I thought hint bits were just for the mvcc side of things? If this is a temporary table, is there any need or benefit to setting hint bits? -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] do temporary tables have hint bits?
Yes. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] temporary tables, indexes, and query plans
Jon Nelson jnelson+pg...@jamponi.net writes: OK. This is a highly distilled example that shows the behavior. BEGIN; CREATE TEMPORARY TABLE foo AS SELECT x AS A, chr(x % 75 + 32) AS b, ''::text AS c from generate_series(1,500) AS x; UPDATE foo SET c = 'foo' WHERE b = 'A' ; CREATE INDEX foo_b_idx on foo (b); [ and the rest of the transaction can't use that index ] OK, this is an artifact of the HOT update optimization. Before creating the index, you did updates on the table that would have been executed differently if the index had existed. When the index does get created, its entries for those updates are incomplete, so the index can't be used in transactions that could in principle see the unmodified rows. You could avoid this effect either by creating the index before you do any updates on the table, or by not wrapping the entire process into a single transaction. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] anti-join chosen even when slower than old plan
On Sat, Nov 13, 2010 at 1:32 AM, Marc Mamin m.ma...@intershop.de wrote: Hello, Just a short though: Is it imaginable to compare the prognoses of the plans with the actual results and somehow log the worst cases ? a) to help the DBA locate bad statistics and queries b) as additional information source for the planner This could possibly affect parameters of your formula on the fly. best regards, Marc Mamin The contrib module auto_explain might help out here if you wanted to roll your own solution for plan comparison. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] temporary tables, indexes, and query plans
On Sat, Nov 13, 2010 at 9:41 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jon Nelson jnelson+pg...@jamponi.net writes: OK. This is a highly distilled example that shows the behavior. BEGIN; CREATE TEMPORARY TABLE foo AS SELECT x AS A, chr(x % 75 + 32) AS b, ''::text AS c from generate_series(1,500) AS x; UPDATE foo SET c = 'foo' WHERE b = 'A' ; CREATE INDEX foo_b_idx on foo (b); [ and the rest of the transaction can't use that index ] OK, this is an artifact of the HOT update optimization. Before creating the index, you did updates on the table that would have been executed differently if the index had existed. When the index does get created, its entries for those updates are incomplete, so the index can't be used in transactions that could in principle see the unmodified rows. Aha! When you indicated that HOT updates were part of the problem, I googled HOT updates for more detail and ran across this article: http://pgsql.tapoueh.org/site/html/misc/hot.html which was very useful in helping me to understand things. If I understand things correctly, after a tuple undergoes a HOT-style update, there is a chain from the original tuple to the updated tuple. If an index already exists on the relation (and involves the updated column), a *new entry* in the index is created. However, if an index does not already exist and one is created (which involves a column with tuples that underwent HOT update) then it seems as though the index doesn't see either version. Is that description inaccurate? What would the effect be of patching postgresql to allow indexes to see and follow the HOT chains during index creation? The reason I did the update before the index creation is that the initial update (in the actual version, not this test version) updates 2.8 million of some 7.5 million rows (or a bit under 40% of the entire table), and such a large update seems like it would have a deleterious effect on the index (although in either case the planner properly chooses a sequential scan for this update). You could avoid this effect either by creating the index before you do any updates on the table, or by not wrapping the entire process into a single transaction. I need the whole thing in a single transaction because I make /extensive/ use of temporary tables and many dozens of statements that need to either succeed or fail as one. Is this HOT update optimization interaction with indexes documented anywhere? It doesn't appear to be common knowledge as there are now 20 messages in this topic and this is the first mention of the HOT updates / index interaction. I would like to suggest that an update to the CREATE INDEX documentation might contain some caveats about creating indexes in transactions on relations that might have HOT updates. Again, I'd like to thank everybody for helping me to figure this out. It's not a huge burden to create the index before the updates, but understanding *why* it wasn't working (even if it violates the principle-of-least-surprise) helps quite a bit. -- Jon -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] temporary tables, indexes, and query plans
Jon Nelson jnelson+pg...@jamponi.net writes: What would the effect be of patching postgresql to allow indexes to see and follow the HOT chains during index creation? It would break things. We did a *lot* of thinking about this when HOT was implemented; there are not simple improvements to be made. The particular case you have here might be improvable because you actually don't have any indexes at all during the UPDATE, and so maybe there's no need for it to create HOT-update chains. But that would still fall over if you made an index, did the update, then made more indexes. Is this HOT update optimization interaction with indexes documented anywhere? It doesn't appear to be common knowledge as there are now 20 messages in this topic and this is the first mention of the HOT updates / index interaction. The reason it wasn't mentioned before was that you kept on not showing us what you did, and there was no reason for anyone to guess that you were mixing updates and index creations in a single transaction. We have seen people run into this type of issue once or twice since 8.3 came out, but it's sufficiently uncommon that it doesn't spend time at the front of anybody's mind. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Defaulting wal_sync_method to fdatasync on Linux for 9.1?
On Mon, Nov 8, 2010 at 20:40, Tom Lane t...@sss.pgh.pa.us wrote: The latter choice is the one that requires testing to prove that it is the proper and preferred default from the performance and data reliability POV. And, in fact, the game plan is to do that testing and see which default we want. I think it's premature to argue further about this until we have some test results. Who will be doing that testing? You said you're relying on Greg Smith to manage the testing, but he's obviously uninterested, so it seems unlikely that this will go anywhere. I posted my results with the simple INSERT test, but nobody cared. I could do some pgbench runs, but I have no idea what parameters would give useful results. Meanwhile, PostgreSQL performance is regressing and there's still no evidence that open_datasync is any safer. Regards, Marti -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Defaulting wal_sync_method to fdatasync on Linux for 9.1?
Marti Raudsepp ma...@juffo.org writes: On Mon, Nov 8, 2010 at 20:40, Tom Lane t...@sss.pgh.pa.us wrote: And, in fact, the game plan is to do that testing and see which default we want. Â I think it's premature to argue further about this until we have some test results. Who will be doing that testing? You said you're relying on Greg Smith to manage the testing, but he's obviously uninterested, so it seems unlikely that this will go anywhere. What's your basis for asserting he's uninterested? Please have a little patience. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] MVCC performance issue
Craig Ringer wrote: It sounds like you're describing Oracle-style MVCC, using redo logs. http://blogs.sybase.com/database/2009/04/mvcc-dispelling-some-oracle-fudunderstanding-the-cost/ Craig, this is an interesting blog page, making some valid points about the multiversioning vs. locking. The ATM example, however, is unrealistic and couldn't have happened the way the author describes. Oracle has the same write consistency mechanism as Postgres and it restarts the transaction if the transaction blocks were updated while the transaction was waiting. In other words, the wife's transaction would have been restarted before committing, the transaction would get the balance accurately and there wouldn't be a loss of $250. Such an example is naive, sheer FUD. If that was the case, no bank in the whole wide world would be using Oracle, and many of them do, I dare say many more are using Oracle than Sybase. That means that they're not losing money if 2 spouses decide to withdraw money from the joint account simultaneously. Given the number of people in the world, I imagine that to be a rather common and ordinary situation for the banks. The example is plain silly. Here is what I have in mind as write consistency: http://www.postgresql.org/docs/9.0/static/transaction-iso.html#XACT-READ-COMMITTED: If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. Essentially the same behavior is described here, for Oracle: http://tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html Obviously, we cannot modify an old version of a block—when we go to modify a row, we must modify the current version of that block. Additionally, Oracle cannot just simply skip this row, as that would be an inconsistent read and unpredictable. What we’ll discover is that in such cases, Oracle will restart the write modification from scratch. Postgres re-evaluates the where condition, Oracle restarts the entire transaction, but neither MVCC mechanism would allow for the silly ATM example described in the blog. Both databases would have noticed change in the balance, both databases would have ended with the proper balance in the account. -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] MVCC performance issue
On 11/14/2010 02:38 AM, Mladen Gogala wrote: Craig Ringer wrote: It sounds like you're describing Oracle-style MVCC, using redo logs. http://blogs.sybase.com/database/2009/04/mvcc-dispelling-some-oracle-fudunderstanding-the-cost/ Craig, this is an interesting blog page, making some valid points about the multiversioning vs. locking. The ATM example, however, is unrealistic and couldn't have happened the way the author describes. Yep, you're quite right. I was using it for its explanation of some of the costs of MVCC as Oracle implements it, because it's surprisingly hard to find explanations/analysis of that with some quick Google searching. I hadn't read beyond that part. I'd be really interested in some *good* writeups of the costs/benefits of the various common mvcc and locking based rdbms implementations. Thanks for posting a breakdown of the issues with that article, lest others be mislead. Appreciated. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] anti-join chosen even when slower than old plan
On Sat, Nov 13, 2010 at 4:32 AM, Marc Mamin m.ma...@intershop.de wrote: Hello, Just a short though: Is it imaginable to compare the prognoses of the plans with the actual results and somehow log the worst cases ? a) to help the DBA locate bad statistics and queries b) as additional information source for the planner This could possibly affect parameters of your formula on the fly. Yeah, I've thought about this, but it's not exactly clear what would be most useful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] temporary tables, indexes, and query plans
On Sat, Nov 13, 2010 at 10:41 AM, Tom Lane t...@sss.pgh.pa.us wrote: Jon Nelson jnelson+pg...@jamponi.net writes: OK. This is a highly distilled example that shows the behavior. BEGIN; CREATE TEMPORARY TABLE foo AS SELECT x AS A, chr(x % 75 + 32) AS b, ''::text AS c from generate_series(1,500) AS x; UPDATE foo SET c = 'foo' WHERE b = 'A' ; CREATE INDEX foo_b_idx on foo (b); [ and the rest of the transaction can't use that index ] OK, this is an artifact of the HOT update optimization. Before creating the index, you did updates on the table that would have been executed differently if the index had existed. When the index does get created, its entries for those updates are incomplete, so the index can't be used in transactions that could in principle see the unmodified rows. Is the in principle here because there might be an open snapshot other than the one under which CREATE INDEX is running, like a cursor? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] temporary tables, indexes, and query plans
Robert Haas robertmh...@gmail.com writes: On Sat, Nov 13, 2010 at 10:41 AM, Tom Lane t...@sss.pgh.pa.us wrote: OK, this is an artifact of the HOT update optimization. Before creating the index, you did updates on the table that would have been executed differently if the index had existed. When the index does get created, its entries for those updates are incomplete, so the index can't be used in transactions that could in principle see the unmodified rows. Is the in principle here because there might be an open snapshot other than the one under which CREATE INDEX is running, like a cursor? Well, the test is based on xmin alone, not cmin, so it can't really tell the difference. It's unclear that it'd be worth trying. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] temporary tables, indexes, and query plans
On Sat, Nov 13, 2010 at 7:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Nov 13, 2010 at 10:41 AM, Tom Lane t...@sss.pgh.pa.us wrote: OK, this is an artifact of the HOT update optimization. Before creating the index, you did updates on the table that would have been executed differently if the index had existed. When the index does get created, its entries for those updates are incomplete, so the index can't be used in transactions that could in principle see the unmodified rows. Is the in principle here because there might be an open snapshot other than the one under which CREATE INDEX is running, like a cursor? Well, the test is based on xmin alone, not cmin, so it can't really tell the difference. It's unclear that it'd be worth trying. Yeah, I'm not familiar with the logic in that area of the code, so I can't comment all that intelligently. However, I feel like there's a class of things that could potentially be optimized if we know that the only snapshot they could affect is the one we're currently using. For example, when bulk loading a newly created table with COPY or CTAS, we could set the xmin-committed hint bit if it weren't for the possibility that some snapshot with a command-ID equal to or lower than our own might take a look and get confused. That seems to require a BEFORE trigger or another open snapshot. And, if we HOT-update a tuple created by our own transaction that can't be of interest to anyone else ever again, it would be nice to either mark it for pruning or maybe even overwrite it in place; similarly if we delete such a tuple it would be nice to schedule its execution. There are problems with all of these ideas, and I'm not totally sure how to make any of it work, but to me this sounds suspiciously like another instance of a somewhat more general problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
