Re: [HACKERS] So, is COUNT(*) fast now?
On Friday, October 21, 2011 08:14:12 PM Robert Haas wrote: On Fri, Oct 21, 2011 at 2:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Oct 21, 2011 at 1:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: I don't know why you'd imagine that touching an index is free, or even cheap, CPU-wise. The whole point of the index-only optimization is to avoid I/O. When you try it on a case where there's no I/O to be saved, and no shared-buffers contention to be avoided, there's no way it's going to be a win. Well, call me naive, but I would have thought touching six times less data would make the operation run faster, not slower. It's not touching six times less data. It's touching the exact same number of tuples either way, just index tuples in one case and heap tuples in the other. Yeah, but it works out to fewer pages. But access to those is not sequential. I guess if you measure cache hit ratios the index scan will come out significantly worse. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So, is COUNT(*) fast now?
2011/10/22 Andres Freund and...@anarazel.de On Friday, October 21, 2011 08:14:12 PM Robert Haas wrote: On Fri, Oct 21, 2011 at 2:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Oct 21, 2011 at 1:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: I don't know why you'd imagine that touching an index is free, or even cheap, CPU-wise. The whole point of the index-only optimization is to avoid I/O. When you try it on a case where there's no I/O to be saved, and no shared-buffers contention to be avoided, there's no way it's going to be a win. Well, call me naive, but I would have thought touching six times less data would make the operation run faster, not slower. It's not touching six times less data. It's touching the exact same number of tuples either way, just index tuples in one case and heap tuples in the other. Yeah, but it works out to fewer pages. But access to those is not sequential. I guess if you measure cache hit ratios the index scan will come out significantly worse. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers But access to those is not sequential yes, I am agree. In my opinion the problem is that. If the query needs to scan all the b-tree index without to access the table rows, the better way to read the index is like sequential one, in fact , query like count(*) or other not need the data are in order so I think we could read all blocks (better, only the leaf blocks) without to touching too much the branch blocks. For example query like this : select column_a from table ; is better to read the data from indexes like sequential For query like this : select column_a from table order by column_a ; is better to read the data from indexes in range scan from root block to first branch blocks and their leaf blocks, so we could save the sorting. Mat
Re: [HACKERS] Synchronized snapshots versus multiple databases
On Fri, Oct 21, 2011 at 4:36 PM, Tom Lane t...@sss.pgh.pa.us wrote: I can see a few alternatives, none of them very pleasant: 1. Restrict exported snapshots to be loaded only by transactions running in the same database as the exporter. This would fix the problem, but it cuts out one of the main use-cases for sync snapshots, namely getting cluster-wide-consistent dumps in pg_dumpall. 4. Somehow mark the xmin of a process that has exported a snapshot so that it will be honored in all DBs not just the current one. The difficulty here is that we'd need to know *at the time the snap is taken* that it's going to be exported. (Consider the scenario above, except that A doesn't get around to exporting the snapshot it took in step 3 until between steps 5 and 6. If the xmin wasn't already marked as globally applicable when vacuum looked at it in step 5, we lose.) This is do-able but it will contort the user-visible API of the sync snapshots feature. One way we could do it is to require that transactions that want to export snapshots set a transaction mode before they take their first snapshot. 1 *and* 4 please. So, unless explicitly requested, an exported snapshot is limited to just one database. If explicitly requested to be transportable, we can use the snapshot in other databases. This allows us to do parallel pg_dump in both 1+ databases, as well as allowing pg_dumpall to be fully consistent across all dbs. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So, is COUNT(*) fast now?
Andres Freund and...@anarazel.de writes: On Friday, October 21, 2011 08:14:12 PM Robert Haas wrote: On Fri, Oct 21, 2011 at 2:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: It's not touching six times less data. It's touching the exact same number of tuples either way, just index tuples in one case and heap tuples in the other. Yeah, but it works out to fewer pages. But access to those is not sequential. I guess if you measure cache hit ratios the index scan will come out significantly worse. Huh? In the case he's complaining about, the index is all in RAM. Sequentiality of access is not an issue (at least not at the page level --- within a page I suppose there could be cache-line effects). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronized snapshots versus multiple databases
Simon Riggs si...@2ndquadrant.com writes: 1 *and* 4 please. Given the lack of enthusiasm I'm not going to do anything about #4 now. Somebody else can add it later. So, unless explicitly requested, an exported snapshot is limited to just one database. If explicitly requested to be transportable, we can use the snapshot in other databases. Yeah, we could make it work like that when it gets added. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] database file encryption.
On 10/21/2011 05:42 PM, nrdb wrote: Hi, I am new to this list. I haven't ever contributed code before, and have no idea on how to do this. I have made some changes to my copy of the 9.1.1 code that encrypts/decrypts the database files on the fly using AES256 cypher. Very cool. It passes all the tests. :-) The changes are limited to : src/backend/storage/file/fd.c src/backend/storage/file/buffile.c src/backend/libpq/be-fsstubs.c Are you willing to submit a patch for people to review? I am not sure if the community would want this as backend code or not but it is definitely something to discuss. At the moment the password has been hardcoded into the source, I don't know how to get it passed in atm. I think the easiest way would be to look at the startup code that launches postmaster. If it detecs that the files are encrypted it would prompt for the passphrase. Others might have different ideas. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So, is COUNT(*) fast now?
On Saturday, October 22, 2011 05:20:26 PM Tom Lane wrote: Andres Freund and...@anarazel.de writes: On Friday, October 21, 2011 08:14:12 PM Robert Haas wrote: On Fri, Oct 21, 2011 at 2:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: It's not touching six times less data. It's touching the exact same number of tuples either way, just index tuples in one case and heap tuples in the other. Yeah, but it works out to fewer pages. But access to those is not sequential. I guess if you measure cache hit ratios the index scan will come out significantly worse. Huh? In the case he's complaining about, the index is all in RAM. Sequentiality of access is not an issue (at least not at the page level --- within a page I suppose there could be cache-line effects). I was talking about L2/L3 caches... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So, is COUNT(*) fast now?
Andres Freund and...@anarazel.de writes: On Saturday, October 22, 2011 05:20:26 PM Tom Lane wrote: Huh? In the case he's complaining about, the index is all in RAM. Sequentiality of access is not an issue (at least not at the page level --- within a page I suppose there could be cache-line effects). I was talking about L2/L3 caches... Yeah, but unless you think cache lines cross page boundaries (and we do take pains to align the buffers on 8K addresses), there's not going to be any sequentiality effect. Even if there were, it would only apply if the pages chanced to be adjacent in the buffer array, and there is no reason to expect that to be the case, for either seqscans or indexscans. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So, is COUNT(*) fast now?
On Fri, Oct 21, 2011 at 10:57 PM, Jeff Janes jeff.ja...@gmail.com wrote: Yeah, but it works out to fewer pages. But since those pages are already in RAM, why would it matter all that much? (Other than in the case of highly concurrent access, which you don't seem to be testing?) Well, because memory access takes time, and accessing more memory takes more time. In the testing that I've done recently, performance on in-memory workloads seems to be extremely sensitive to memory speed, so you'd think that cutting down on memory access would be a win. One of Tom's commits that made it not lock the same index page over and over again (once for each tuple on it) made me think it should be much faster than the seq scan, but a bit of random flailing about convinced me that any saving from this were compensated for by the high over head of FunctionCall2Coll and all of the hokey-pokey that that call entails, which a seqscan can skip entirely. Huh. Not sure whether that's significant or not. If count(*) could cause the index-only scan to happen in physical order of the index, rather than logical order, that might be a big win. Both for all in memory and for not-all-in-memory. That's an interesting point. I sort of assumed that would only help for not-all-in-memory, but maybe not. The trouble is that I think there are some problematic concurrency issues there. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] database file encryption.
On 10/22/2011 11:39 AM, nrdb wrote: Are you willing to submit a patch for people to review? I am not sure if the community would want this as backend code or not but it is definitely something to discuss. Yes! but I don't know what the procedure is to do that. http://wiki.postgresql.org/wiki/Submitting_a_Patch Others might have different ideas. I thought one way would be to pass the name of a named pipe in with a command argument and then have some program that asks the user for the password and writes it to the named pipe. Any patch you submit will be subject to quite a bit of discussion so be prepared for that. Also it will have to be portable to Windows. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] database file encryption.
On 10/23/2011 02:37 AM, Joshua D. Drake wrote: On 10/21/2011 05:42 PM, nrdb wrote: Hi, I am new to this list. I haven't ever contributed code before, and have no idea on how to do this. I have made some changes to my copy of the 9.1.1 code that encrypts/decrypts the database files on the fly using AES256 cypher. Very cool. It passes all the tests. :-) The changes are limited to : src/backend/storage/file/fd.c src/backend/storage/file/buffile.c src/backend/libpq/be-fsstubs.c Are you willing to submit a patch for people to review? I am not sure if the community would want this as backend code or not but it is definitely something to discuss. Yes! but I don't know what the procedure is to do that. At the moment the password has been hardcoded into the source, I don't know how to get it passed in atm. I think the easiest way would be to look at the startup code that launches postmaster. If it detecs that the files are encrypted it would prompt for the passphrase. Others might have different ideas. I thought one way would be to pass the name of a named pipe in with a command argument and then have some program that asks the user for the password and writes it to the named pipe. JD Neil Dugan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So, is COUNT(*) fast now?
- Цитат от Tom Lane (t...@sss.pgh.pa.us), на 22.10.2011 в 19:19 - Andres Freund and...@anarazel.de writes: On Saturday, October 22, 2011 05:20:26 PM Tom Lane wrote: Huh? In the case he's complaining about, the index is all in RAM. Sequentiality of access is not an issue (at least not at the page level --- within a page I suppose there could be cache-line effects). I was talking about L2/L3 caches... Yeah, but unless you think cache lines cross page boundaries (and we do take pains to align the buffers on 8K addresses), there's not going to be any sequentiality effect. Even if there were, it would only apply if the pages chanced to be adjacent in the buffer array, and there is no reason to expect that to be the case, for either seqscans or indexscans. regards, tom lane I worked on in-memory hash stables of parrot project. It is not the same as btrees but the structure and memory layout are not that different - tupples are going into pages etc. I have benchmarked iterating over such hash tables - sequential scan of the same table comes 20-30% faster than scan ordered by the hash value of the key. And this is overhead only of CPU cache lines - the numbers of instructions executed on the processor are pretty much the same (counted by valgrind). So I do think that if we have sequential scan of indexes (physical order) it will help even when all the data is in the buffercache. Best regards -- Luben Karavelov
Re: [HACKERS] So, is COUNT(*) fast now?
Robert Haas robertmh...@gmail.com writes: On Fri, Oct 21, 2011 at 10:57 PM, Jeff Janes jeff.ja...@gmail.com wrote: If count(*) could cause the index-only scan to happen in physical order of the index, rather than logical order, that might be a big win. Both for all in memory and for not-all-in-memory. That's an interesting point. I sort of assumed that would only help for not-all-in-memory, but maybe not. The trouble is that I think there are some problematic concurrency issues there. Yeah. We managed to make physical-order scanning work for VACUUM because it's okay if VACUUM sometimes sees the same index tuple twice; it'll just make the same decision about (not) deleting it. That will not fly for regular querying. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] database file encryption.
Joshua D. Drake j...@commandprompt.com writes: Any patch you submit will be subject to quite a bit of discussion so be prepared for that. Also it will have to be portable to Windows. The first question that's going to be asked is why you don't just use an encrypted file system, instead. Not every problem has to be solved at the database level. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] synchronized snapshots
Joachim Wieland j...@mcknight.de writes: [ synchronized snapshots patch ] Applied with, um, rather extensive editorialization. I'm not convinced that the SSI case is bulletproof yet, but it'll be easier to test with the code committed. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI implementation question
Dan Ports d...@csail.mit.edu writes: On Wed, Oct 19, 2011 at 04:36:41PM -0400, Tom Lane wrote: (2) as things stand, xact A need not be running in serializable mode --- if B is serializable, does *that* break any assumptions? [taking these in opposite order] Yes, I think that's going to be a problem. The obvious case where it's clearly not going to work is if A is older than the oldest active serializable xact (i.e. SxactGlobalXmin would have to move backwards). It's probably possible to make it work when that's not the case, but I think it's better to require A to be serializable -- if nothing else, it's a far simpler rule to document! There is another case that could be problematic, if A was READ ONLY, and B isn't. It sounds to me like that would also be a reasonable thing to forbid. I've committed the synchronized-snapshots patch with those two restrictions, ie, to import a snapshot into a serializable transaction (1) the source transaction must be serializable (and must still be running, of course); (2) you can't import a read-only transaction's snapshot into a read-write serializable transaction. I don't understand the SSI code well enough to tell if this is sufficient or not, so I hope you guys will take a closer look at the issue when you have time. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] synchronized snapshots
On 23 October 2011 00:25, Tom Lane t...@sss.pgh.pa.us wrote: Joachim Wieland j...@mcknight.de writes: [ synchronized snapshots patch ] Applied with, um, rather extensive editorialization. I'm not convinced that the SSI case is bulletproof yet, but it'll be easier to test with the code committed. Can I ask why it doesn't return the same snapshot ID each time? Surely it can't change since you can only export the snapshot of a serializable or repeatable read transaction? A SELECT count(pg_export_snapshot()) FROM generate_series(1,1000); would quickly bork the pg_snapshots directory which any user can run. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] database file encryption.
On 10/23/2011 08:17 AM, Tom Lane wrote: Joshua D. Drakej...@commandprompt.com writes: Any patch you submit will be subject to quite a bit of discussion so be prepared for that. Also it will have to be portable to Windows. The first question that's going to be asked is why you don't just use an encrypted file system, instead. Not every problem has to be solved at the database level. regards, tom lane Well the database files are always encrypted, with using a encrypted file system, there is an unencrypted view of the files visible. I realise that under normal setup this is readable by only the postgres user, and if you could read the files you would also be able to interrogate the server to get the data. But if the postgres server wasn't running yet (i.e. the password hadn't been entered), there would be no data visible. I don't know anything about Windows, does it have the same file security as Linux? I realise the difference is small. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] database file encryption.
On 10/23/2011 06:39 AM, Joshua D. Drake wrote: On 10/22/2011 11:39 AM, nrdb wrote: Are you willing to submit a patch for people to review? I am not sure if the community would want this as backend code or not but it is definitely something to discuss. Yes! but I don't know what the procedure is to do that. http://wiki.postgresql.org/wiki/Submitting_a_Patch I will submit the patch in a few days when I have cleaned it up some more. Basically it patches the FileRead() and FileWrite() in fd.c to do the encryption and decryption. Others might have different ideas. I thought one way would be to pass the name of a named pipe in with a command argument and then have some program that asks the user for the password and writes it to the named pipe. Any patch you submit will be subject to quite a bit of discussion so be prepared for that. Also it will have to be portable to Windows. I have no idea on making it portable to Windows, I very rarely use it and know nothing about coding on it. The only problem will be in the actual encryption/decryption functions, and the key setup. JD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] synchronized snapshots
Thom Brown t...@linux.com writes: Can I ask why it doesn't return the same snapshot ID each time? Surely it can't change since you can only export the snapshot of a serializable or repeatable read transaction? No, that's incorrect. You can export from a READ COMMITTED transaction; indeed, you'd more or less have to, if you want the control transaction to be able to see what the slaves do. A SELECT count(pg_export_snapshot()) FROM generate_series(1,1000); would quickly bork the pg_snapshots directory which any user can run. Shrug ... you can create a much more severe DOS problem by making zillions of tables, if the filesystem doesn't handle lots-o-files well. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.2] make_greater_string() does not return a string in some cases
On Thu, Oct 20, 2011 at 9:36 PM, Kyotaro HORIGUCHI horiguchi.kyot...@oss.ntt.co.jp wrote: This must be the basis of the behavior of pg_utf8_verifier(), and pg_utf8_increment() has taken over it. It may be good to describe this origin of the special handling as comment of these functions to avoid this sort of confusion. Oh, you know what? I'm misreading this code. *facepalm* I thought that code was looking for 0xED/0xF4 in the second position, but it's actually looking for them in the first position, which makes vastly more sense. Whee! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So, is COUNT(*) fast now?
Robert Haas robertmh...@gmail.com writes: On Fri, Oct 21, 2011 at 3:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Anyhow, here's the scoop. On my desktop machine running F14, running SELECT sum(1) FROM pgbench_accounts in a tight loop, 60 s worth of oprofile data: 176830 13.0801 postgres postgres ExecProject Hm, that's weird. In both these cases, I'd have expected that ExecProject would get optimized away thanks to selection of a physical tlist for the scan node. Wonder if that got broken ... If it did, it looks like it wasn't recent. I set up the same test case on my MacBook using REL9_1_STABLE and REL9_0_STABLE and set a breakpoint on ExecProject(). Both back-branches appear to also call ExecProject() for every tuple. Oh, the ExecProject calls are coming from advance_aggregates(). Move along, nothing to see here ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Deferrable unique constraints vs join removal -- bug?
Robert Haas robertmh...@gmail.com writes: Yuck. Well, that's certainly a bug. What's weird is that I thought we had put logic into the join removal code to ignore deferrable constraints. Apparently not. I poked around a bit more and could not find any evidence that we'd ever done that. Ah well. I think maybe what we should do is add an immediate field to IndexOptInfo, mirroring the existing unique flag, and have get_relation_info() populate it from indimmediate, and then make relation_has_unique_index() disqualify any non-immediate index. Yeah, this seems like the right fix. I considered redefining the unique flag to mean indisunique indimmediate, but that's wrong because of: has_unique_index() arguably needs a similar fix, although at present that appears to be used for only statistic purposes, so maybe it's OK. Yes, since this is meant for statistical purposes, I think it's appropriate for it to disregard indimmediate. A comment update might be a good idea, though. Or we could add a parameter to have the caller indicate which behavior is wanted. But for now I think a comment is enough. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
