Re: [HACKERS] So, is COUNT(*) fast now?

2011-10-22 Thread Andres Freund
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

Re: [HACKERS] So, is COUNT(*) fast now?

2011-10-22 Thread desmodemone
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

Re: [HACKERS] Synchronized snapshots versus multiple databases

2011-10-22 Thread Simon Riggs
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

Re: [HACKERS] So, is COUNT(*) fast now?

2011-10-22 Thread Tom Lane
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

Re: [HACKERS] Synchronized snapshots versus multiple databases

2011-10-22 Thread Tom Lane
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

Re: [HACKERS] database file encryption.

2011-10-22 Thread Joshua D. Drake
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

Re: [HACKERS] So, is COUNT(*) fast now?

2011-10-22 Thread Andres Freund
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

Re: [HACKERS] So, is COUNT(*) fast now?

2011-10-22 Thread Tom Lane
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

Re: [HACKERS] So, is COUNT(*) fast now?

2011-10-22 Thread Robert Haas
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,

Re: [HACKERS] database file encryption.

2011-10-22 Thread Joshua D. Drake
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.

Re: [HACKERS] database file encryption.

2011-10-22 Thread nrdb
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

Re: [HACKERS] So, is COUNT(*) fast now?

2011-10-22 Thread karavelov
- Цитат от 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

Re: [HACKERS] So, is COUNT(*) fast now?

2011-10-22 Thread Tom Lane
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

Re: [HACKERS] database file encryption.

2011-10-22 Thread Tom Lane
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

Re: [HACKERS] synchronized snapshots

2011-10-22 Thread Tom Lane
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

Re: [HACKERS] SSI implementation question

2011-10-22 Thread Tom Lane
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

Re: [HACKERS] synchronized snapshots

2011-10-22 Thread Thom Brown
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

Re: [HACKERS] database file encryption.

2011-10-22 Thread nrdb
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

Re: [HACKERS] database file encryption.

2011-10-22 Thread nrdb
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

Re: [HACKERS] synchronized snapshots

2011-10-22 Thread Tom Lane
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,

Re: [HACKERS] [v9.2] make_greater_string() does not return a string in some cases

2011-10-22 Thread Robert Haas
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

Re: [HACKERS] So, is COUNT(*) fast now?

2011-10-22 Thread Tom Lane
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

Re: [HACKERS] [PATCH] Deferrable unique constraints vs join removal -- bug?

2011-10-22 Thread Tom Lane
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