Re: [HACKERS] texteq/byteaeq: avoid detoast [REVIEW]
On Tue, Jan 18, 2011 at 10:03:01AM +0200, Heikki Linnakangas wrote: >> That isn't ever going to happen, unless you'd like to give up hash joins >> and hash aggregation on text values. > > You could canonicalize the string first in the hash function. I'm not > sure if we have all the necessary information at hand there, but at > least with some encoding/locale-specific support functions it'd be > possible. This is what strxfrm() was created for. strcoll(a,b) == strcmp(strxfrm(a),strxfrm(b)) Sure there's a cost, the question is only how much and whether it makes hash join unfeasible. I doubt it, since by definition it must be faster than strcoll(). I suppose a test would be interesting. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle signature.asc Description: Digital signature
Re: [HACKERS] texteq/byteaeq: avoid detoast [REVIEW]
Robert Haas writes: > On Tue, Jan 18, 2011 at 11:44 AM, Tom Lane wrote: >> Oh, I misread Itagaki-san's comment to imply that that *was* in the >> patch. Maybe I should go read it. > Perhaps. :-) > While you're at it you might commit it. :-) Yeah, as penance I'll take this one. 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] texteq/byteaeq: avoid detoast [REVIEW]
On Tue, Jan 18, 2011 at 11:44 AM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Jan 18, 2011 at 11:15 AM, Tom Lane wrote: >>> No, I don't think so. Has any evidence been submitted that that part of >>> the patch is of benefit? > >> I think you might be mixing up what's actually in the patch with >> another idea that was proposed but isn't actually in the patch. The >> patch itself does nothing controversial. > > Oh, I misread Itagaki-san's comment to imply that that *was* in the > patch. Maybe I should go read it. Perhaps. :-) While you're at it you might commit it. :-) -- 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] texteq/byteaeq: avoid detoast [REVIEW]
Robert Haas writes: > On Tue, Jan 18, 2011 at 11:15 AM, Tom Lane wrote: >> No, I don't think so. Has any evidence been submitted that that part of >> the patch is of benefit? > I think you might be mixing up what's actually in the patch with > another idea that was proposed but isn't actually in the patch. The > patch itself does nothing controversial. Oh, I misread Itagaki-san's comment to imply that that *was* in the patch. Maybe I should go read it. 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] texteq/byteaeq: avoid detoast [REVIEW]
On Tue, Jan 18, 2011 at 11:15 AM, Tom Lane wrote: >> It's a very light-weight alternative of memcmp the byte data, >> but there is still the same issue -- we might have different >> compressed results if we use different algorithm for TOASTing. > > Which makes it a lightweight waste of cycles. > >> So, it would be better to apply the present patch as-is. > > No, I don't think so. Has any evidence been submitted that that part of > the patch is of benefit? I think you might be mixing up what's actually in the patch with another idea that was proposed but isn't actually in the patch. The patch itself does nothing controversial. -- 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] texteq/byteaeq: avoid detoast [REVIEW]
Itagaki Takahiro writes: > On Tue, Jan 18, 2011 at 05:39, Tom Lane wrote: >> I haven't looked at this patch, but it seems to me that it would be >> reasonable to conclude A != B if the va_extsize values in the toast >> pointers don't agree. > It's a very light-weight alternative of memcmp the byte data, > but there is still the same issue -- we might have different > compressed results if we use different algorithm for TOASTing. Which makes it a lightweight waste of cycles. > So, it would be better to apply the present patch as-is. No, I don't think so. Has any evidence been submitted that that part of the patch is of benefit? 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] texteq/byteaeq: avoid detoast [REVIEW]
On Tue, Jan 18, 2011 at 05:39, Tom Lane wrote: > I haven't looked at this patch, but it seems to me that it would be > reasonable to conclude A != B if the va_extsize values in the toast > pointers don't agree. It's a very light-weight alternative of memcmp the byte data, but there is still the same issue -- we might have different compressed results if we use different algorithm for TOASTing. So, it would be better to apply the present patch as-is. We can improve the comparison logic over the patch in another development cycle if possible. -- Itagaki Takahiro -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On 17.01.2011 22:33, Tom Lane wrote: Peter Eisentraut writes: On mån, 2011-01-17 at 07:35 +0100, Magnus Hagander wrote: In fact, aren't there cases where the *length test* also fails? Currently, two text values are only equal of strcoll() considers them equal and the bits are the same. So this patch is safe in that regard. There is, however, some desire to loosen this. That isn't ever going to happen, unless you'd like to give up hash joins and hash aggregation on text values. You could canonicalize the string first in the hash function. I'm not sure if we have all the necessary information at hand there, but at least with some encoding/locale-specific support functions it'd be possible. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On mån, 2011-01-17 at 15:33 -0500, Tom Lane wrote: > Peter Eisentraut writes: > > On mån, 2011-01-17 at 07:35 +0100, Magnus Hagander wrote: > >> In fact, aren't there cases where the *length test* also fails? > > > Currently, two text values are only equal of strcoll() considers them > > equal and the bits are the same. So this patch is safe in that regard. > > > There is, however, some desire to loosen this. > > That isn't ever going to happen, unless you'd like to give up hash joins > and hash aggregation on text values. Since citext exists and supports hashing, it's obviously possible nevertheless. -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On Mon, Jan 17, 2011 at 02:36:56PM -0600, Jim Nasby wrote: > On Jan 17, 2011, at 9:22 AM, Noah Misch wrote: > > Just to be clear, the code already has these length tests today. This patch > > just moves them before the detoast. > > Any reason we can't do this for other varlena? I'm wondering if it makes more > sense to have some generic toast comparison functions that don't care what > the data in toast actually is... We could not apply the optimization to varlenas generically. For example, bpchareq() ignores trailing white space during comparison, so "foo " = "foo ". It would work for biteq(), though I'm not sure how often large-scale varbits come up. numericeq() does not qualify, because you might have a NumericLong in a binary-upgraded table that would now become a NumericShort. So, there very well may be other places where we should apply the same optimization, but each one needs individual consideration. Thanks, nm -- 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] texteq/byteaeq: avoid detoast [REVIEW]
Magnus Hagander writes: > I wonder if we can trust the *equality* test, but not the inequality? > E.g. if compressed(A) == compressed(B) we know they're the same, but > if compressed(A) != compressed(B) we don't know they're not they still > might be. I haven't looked at this patch, but it seems to me that it would be reasonable to conclude A != B if the va_extsize values in the toast pointers don't agree. Once you've fetched the toasted values, you've spent enough cycles that there's not going to be much point in trying to do any cute optimizations beyond that. So if the patch is doing a memcmp on the compressed data, I'd be inclined to get rid of that part. 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] texteq/byteaeq: avoid detoast [REVIEW]
On Jan 17, 2011, at 9:22 AM, Noah Misch wrote: > On Mon, Jan 17, 2011 at 07:35:52AM +0100, Magnus Hagander wrote: >> On Mon, Jan 17, 2011 at 06:51, Itagaki Takahiro >> wrote: >>> On Mon, Jan 17, 2011 at 04:05, Andy Colson wrote: This is a review of: https://commitfest.postgresql.org/action/patch_view?id=468 Purpose: Equal and not-equal _may_ be quickly determined if their lengths are different. ? This _may_ be a huge speed up if we don't have to detoast. >>> >>> We can skip detoast to compare lengths of two text/bytea values >>> with the patch, but we still need detoast to compare the contents >>> of the values. >>> >>> If we always generate same toasted byte sequences from the same raw >>> values, we don't need to detoast at all to compare the contents. >>> Is it possible or not? >> >> For bytea, it seems it would be possible. >> >> For text, I think locales may make that impossible. Aren't there >> locale rules where two different characters can "behave the same" when >> comparing them? I know in Swedish at least w and v behave the same >> when sorting (but not when comparing) in some variants of the locale. >> >> In fact, aren't there cases where the *length test* also fails? I >> don't know this for sure, but unless we know for certain that two >> different length strings can never be the same *independent of >> locale*, this whole patch has a big problem... > > Just to be clear, the code already has these length tests today. This patch > just moves them before the detoast. Any reason we can't do this for other varlena? I'm wondering if it makes more sense to have some generic toast comparison functions that don't care what the data in toast actually is... -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] texteq/byteaeq: avoid detoast [REVIEW]
Peter Eisentraut writes: > On mån, 2011-01-17 at 07:35 +0100, Magnus Hagander wrote: >> In fact, aren't there cases where the *length test* also fails? > Currently, two text values are only equal of strcoll() considers them > equal and the bits are the same. So this patch is safe in that regard. > There is, however, some desire to loosen this. That isn't ever going to happen, unless you'd like to give up hash joins and hash aggregation on text values. 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] texteq/byteaeq: avoid detoast [REVIEW]
On mån, 2011-01-17 at 07:55 -0500, Robert Haas wrote: > > There is, however, some desire to loosen this. Possible > applications > > are case-insensitive comparison and Unicode normalization. It's not > > going to happen soon, but it may be worth considering not putting in > an > > optimization that we'll end up having to rip out again in a year > > perhaps. > > Hmm. I hate to give up on this - it's a nice optimization for the > cases to which it applies. Would it be possible to jigger things so > that we can still do it byte-for-byte when case-insensitive comparison > or Unicode normalization AREN'T in use? Well, at the moment it's all theoretical anyway. These features aren't on the table, and no one has implemented them. It's quite possible, however, that if we go that way and investigate which locales are safe for this, we might end up with the same answer as for which locales are safe for LIKE optimization, namely none. -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On Mon, Jan 17, 2011 at 11:05:09AM +0100, Magnus Hagander wrote: > On Mon, Jan 17, 2011 at 09:13, Itagaki Takahiro > wrote: > > 2011/1/17 KaiGai Kohei : > >> Are you talking about an idea to apply toast id as an alternative key? > > > > No, probably. I'm just talking about whether "diff -q A.txt B.txt" and > > "diff -q A.gz ?B.gz" always returns the same result or not. Interesting. > > ... I found it depends on version of gzip. So, if we use such logic, > > we cannot improve toast compression logic because the data is migrated > > by pg_upgrade. > > Yeah, that might be a bad tradeoff. > > I wonder if we can trust the *equality* test, but not the inequality? > E.g. if compressed(A) == compressed(B) we know they're the same, but > if compressed(A) != compressed(B) we don't know they're not they still > might be. Exactly. > I guess with two different versions or even completely different > algorithms we could end up with exactly the same compressed value for > different plaintexts (it's not a cryptographic hash after all), so > that's probably not an acceptable comparison either. It's safe to assume that will never happen. If compressed(A) == compressed(B) when A != B, we would have a lossy compression algorithm. As you say, though, _inequality_ implies nothing for an arbitrary decompressor. One can trivially construct many inputs to the zlib decompressor that yield the same output. "gzip -1" ... "gzip -9" do this, for example. So the main win here would come if we tightly controlled the compressor, such that we could infer something from compressed(A) != compressed(B). That would be an intriguing path to explore. nm -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On Mon, Jan 17, 2011 at 07:35:52AM +0100, Magnus Hagander wrote: > On Mon, Jan 17, 2011 at 06:51, Itagaki Takahiro > wrote: > > On Mon, Jan 17, 2011 at 04:05, Andy Colson wrote: > >> This is a review of: > >> https://commitfest.postgresql.org/action/patch_view?id=468 > >> > >> Purpose: > >> > >> Equal and not-equal _may_ be quickly determined if their lengths are > >> different. ? This _may_ be a huge speed up if we don't have to detoast. > > > > We can skip detoast to compare lengths of two text/bytea values > > with the patch, but we still need detoast to compare the contents > > of the values. > > > > If we always generate same toasted byte sequences from the same raw > > values, we don't need to detoast at all to compare the contents. > > Is it possible or not? > > For bytea, it seems it would be possible. > > For text, I think locales may make that impossible. Aren't there > locale rules where two different characters can "behave the same" when > comparing them? I know in Swedish at least w and v behave the same > when sorting (but not when comparing) in some variants of the locale. > > In fact, aren't there cases where the *length test* also fails? I > don't know this for sure, but unless we know for certain that two > different length strings can never be the same *independent of > locale*, this whole patch has a big problem... Just to be clear, the code already has these length tests today. This patch just moves them before the detoast. -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On Mon, Jan 17, 2011 at 2:56 AM, Peter Eisentraut wrote: > On mån, 2011-01-17 at 07:35 +0100, Magnus Hagander wrote: >> For text, I think locales may make that impossible. Aren't there >> locale rules where two different characters can "behave the same" when >> comparing them? I know in Swedish at least w and v behave the same >> when sorting (but not when comparing) in some variants of the locale. >> >> In fact, aren't there cases where the *length test* also fails? I >> don't know this for sure, but unless we know for certain that two >> different length strings can never be the same *independent of >> locale*, this whole patch has a big problem... > > Currently, two text values are only equal of strcoll() considers them > equal and the bits are the same. So this patch is safe in that regard. > > There is, however, some desire to loosen this. Possible applications > are case-insensitive comparison and Unicode normalization. It's not > going to happen soon, but it may be worth considering not putting in an > optimization that we'll end up having to rip out again in a year > perhaps. Hmm. I hate to give up on this - it's a nice optimization for the cases to which it applies. Would it be possible to jigger things so that we can still do it byte-for-byte when case-insensitive comparison or Unicode normalization AREN'T in use? -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On Mon, Jan 17, 2011 at 09:13, Itagaki Takahiro wrote: > 2011/1/17 KaiGai Kohei : >> Are you talking about an idea to apply toast id as an alternative key? > > No, probably. I'm just talking about whether "diff -q A.txt B.txt" and > "diff -q A.gz B.gz" always returns the same result or not. > > ... I found it depends on version of gzip. So, if we use such logic, > we cannot improve toast compression logic because the data is migrated > by pg_upgrade. Yeah, that might be a bad tradeoff. I wonder if we can trust the *equality* test, but not the inequality? E.g. if compressed(A) == compressed(B) we know they're the same, but if compressed(A) != compressed(B) we don't know they're not they still might be. I guess with two different versions or even completely different algorithms we could end up with exactly the same compressed value for different plaintexts (it's not a cryptographic hash after all), so that's probably not an acceptable comparison either. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- 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] texteq/byteaeq: avoid detoast [REVIEW]
2011/1/17 KaiGai Kohei : > Are you talking about an idea to apply toast id as an alternative key? No, probably. I'm just talking about whether "diff -q A.txt B.txt" and "diff -q A.gz B.gz" always returns the same result or not. ... I found it depends on version of gzip. So, if we use such logic, we cannot improve toast compression logic because the data is migrated by pg_upgrade. > I did similar idea to represent security label on user tables for row > level security in the v8.4/9.0 based implementation. Because a small > number of security labels are shared by massive tuples, it is waste of > space if we have all the text data being toasted individually, not only > performance loss in toast/detoast. It looks the same issue as large object rather than the discussion here. We have vacuumlo in contrib to solve the issue. So, we could have vacuumlo-like special sweeping logic for the security label table. -- Itagaki Takahiro -- 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] texteq/byteaeq: avoid detoast [REVIEW]
2011/1/17 Itagaki Takahiro : > On Mon, Jan 17, 2011 at 16:13, Pavel Stehule wrote: If we always generate same toasted byte sequences from the same raw values, we don't need to detoast at all to compare the contents. Is it possible or not? >>> >>> For bytea, it seems it would be possible. >>> >>> For text, I think locales may make that impossible. Aren't there >>> locale rules where two different characters can "behave the same" when >>> comparing them? I know in Swedish at least w and v behave the same >>> when sorting (but not when comparing) in some variants of the locale. >>> >> Some string's comparation operations are binary now too. But it is >> question what will be new with collate support. > > Right. We are using memcmp() in texteq and textne now. We consider > collations only in <, <=, =>, > and compare support functions. > So, I think there is no regression here as long as raw values and > toasted byte sequences have one-to-one correspondence. > I am sure, so this isn't a problem in Czech locale, but I am not sure about German or Turkish. There was issue (if I remember well with German "ss" char) ? Pavel > -- > Itagaki Takahiro > -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On mån, 2011-01-17 at 07:35 +0100, Magnus Hagander wrote: > For text, I think locales may make that impossible. Aren't there > locale rules where two different characters can "behave the same" when > comparing them? I know in Swedish at least w and v behave the same > when sorting (but not when comparing) in some variants of the locale. > > In fact, aren't there cases where the *length test* also fails? I > don't know this for sure, but unless we know for certain that two > different length strings can never be the same *independent of > locale*, this whole patch has a big problem... Currently, two text values are only equal of strcoll() considers them equal and the bits are the same. So this patch is safe in that regard. There is, however, some desire to loosen this. Possible applications are case-insensitive comparison and Unicode normalization. It's not going to happen soon, but it may be worth considering not putting in an optimization that we'll end up having to rip out again in a year perhaps. -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On Mon, Jan 17, 2011 at 16:13, Pavel Stehule wrote: >>> If we always generate same toasted byte sequences from the same raw >>> values, we don't need to detoast at all to compare the contents. >>> Is it possible or not? >> >> For bytea, it seems it would be possible. >> >> For text, I think locales may make that impossible. Aren't there >> locale rules where two different characters can "behave the same" when >> comparing them? I know in Swedish at least w and v behave the same >> when sorting (but not when comparing) in some variants of the locale. >> > Some string's comparation operations are binary now too. But it is > question what will be new with collate support. Right. We are using memcmp() in texteq and textne now. We consider collations only in <, <=, =>, > and compare support functions. So, I think there is no regression here as long as raw values and toasted byte sequences have one-to-one correspondence. -- Itagaki Takahiro -- 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] texteq/byteaeq: avoid detoast [REVIEW]
2011/1/17 Magnus Hagander : > On Mon, Jan 17, 2011 at 06:51, Itagaki Takahiro > wrote: >> On Mon, Jan 17, 2011 at 04:05, Andy Colson wrote: >>> This is a review of: >>> https://commitfest.postgresql.org/action/patch_view?id=468 >>> >>> Purpose: >>> >>> Equal and not-equal _may_ be quickly determined if their lengths are >>> different. This _may_ be a huge speed up if we don't have to detoast. >> >> We can skip detoast to compare lengths of two text/bytea values >> with the patch, but we still need detoast to compare the contents >> of the values. >> >> If we always generate same toasted byte sequences from the same raw >> values, we don't need to detoast at all to compare the contents. >> Is it possible or not? > > For bytea, it seems it would be possible. > > For text, I think locales may make that impossible. Aren't there > locale rules where two different characters can "behave the same" when > comparing them? I know in Swedish at least w and v behave the same > when sorting (but not when comparing) in some variants of the locale. > > In fact, aren't there cases where the *length test* also fails? I > don't know this for sure, but unless we know for certain that two > different length strings can never be the same *independent of > locale*, this whole patch has a big problem... > Some string's comparation operations are binary now too. But it is question what will be new with collate support. Regards Pavel Stehule > -- > Magnus Hagander > Me: http://www.hagander.net/ > Work: http://www.redpill-linpro.com/ > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On Mon, Jan 17, 2011 at 06:51, Itagaki Takahiro wrote: > On Mon, Jan 17, 2011 at 04:05, Andy Colson wrote: >> This is a review of: >> https://commitfest.postgresql.org/action/patch_view?id=468 >> >> Purpose: >> >> Equal and not-equal _may_ be quickly determined if their lengths are >> different. This _may_ be a huge speed up if we don't have to detoast. > > We can skip detoast to compare lengths of two text/bytea values > with the patch, but we still need detoast to compare the contents > of the values. > > If we always generate same toasted byte sequences from the same raw > values, we don't need to detoast at all to compare the contents. > Is it possible or not? For bytea, it seems it would be possible. For text, I think locales may make that impossible. Aren't there locale rules where two different characters can "behave the same" when comparing them? I know in Swedish at least w and v behave the same when sorting (but not when comparing) in some variants of the locale. In fact, aren't there cases where the *length test* also fails? I don't know this for sure, but unless we know for certain that two different length strings can never be the same *independent of locale*, this whole patch has a big problem... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- 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] texteq/byteaeq: avoid detoast [REVIEW]
(2011/01/17 14:51), Itagaki Takahiro wrote: > On Mon, Jan 17, 2011 at 04:05, Andy Colson wrote: >> This is a review of: >> https://commitfest.postgresql.org/action/patch_view?id=468 >> >> Purpose: >> >> Equal and not-equal _may_ be quickly determined if their lengths are >> different. This _may_ be a huge speed up if we don't have to detoast. > > We can skip detoast to compare lengths of two text/bytea values > with the patch, but we still need detoast to compare the contents > of the values. > > If we always generate same toasted byte sequences from the same raw > values, we don't need to detoast at all to compare the contents. > Is it possible or not? > Are you talking about an idea to apply toast id as an alternative key? I did similar idea to represent security label on user tables for row level security in the v8.4/9.0 based implementation. Because a small number of security labels are shared by massive tuples, it is waste of space if we have all the text data being toasted individually, not only performance loss in toast/detoast. In this case, I represented security label (text) using security-id (oid) which is a primary key pointing out a certain text data in catalog table. It well reduced storage consumption and achieved good performance in comparison operation. One challenge was to reclaim orphan texts. In this case, we needed to lock out a user table referencing the toast values, then we delete all the orphan entries. Thanks, -- KaiGai Kohei -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On Mon, Jan 17, 2011 at 04:05, Andy Colson wrote: > This is a review of: > https://commitfest.postgresql.org/action/patch_view?id=468 > > Purpose: > > Equal and not-equal _may_ be quickly determined if their lengths are > different. This _may_ be a huge speed up if we don't have to detoast. We can skip detoast to compare lengths of two text/bytea values with the patch, but we still need detoast to compare the contents of the values. If we always generate same toasted byte sequences from the same raw values, we don't need to detoast at all to compare the contents. Is it possible or not? -- Itagaki Takahiro -- 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] texteq/byteaeq: avoid detoast [REVIEW]
2011/1/16 Noah Misch : > On Sun, Jan 16, 2011 at 10:07:13PM +0100, Pavel Stehule wrote: >> I think, so we can have a function or macro that compare a varlena >> sizes. Some like >> >> Datum texteq(..) >> { >> if (!datumsHasSameLength(PG_GETARG_DATUM(0), PG_GETARG_DATUM(1)) >> PG_RETURN_FALSE(); >> >> ... actual code .. >> } > > Good point. Is this something that would be useful many places? One thing > that > bugged me slightly writing this patch is that texteq, textne, byteaeq and > byteane all follow the same pattern rather tightly. (Indeed, I think one > could > easily implement texteq and byteaeq with the exact same C function.). It isn't good idea. Theoretically, there can be differencies between text and bytea in future - there can be important collations. Now, these types are distinct and some basic methods should be distinct too. Different situation is on varlena level. Regards Pavel Stehule I like how > we handle this for tsvector (see TSVECTORCMPFUNC in tsvector_op.c) to avoid > the > redundancy. If datumHasSameLength would mainly apply to these four functions > or > ones very similar to them, maybe we should abstract out the entire function > body > like we do for tsvector? > > A topic for a different patch in any case, I think. > -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On Sun, Jan 16, 2011 at 10:07:13PM +0100, Pavel Stehule wrote: > I think, so we can have a function or macro that compare a varlena > sizes. Some like > > Datum texteq(..) > { > if (!datumsHasSameLength(PG_GETARG_DATUM(0), PG_GETARG_DATUM(1)) > PG_RETURN_FALSE(); > > ... actual code .. > } Good point. Is this something that would be useful many places? One thing that bugged me slightly writing this patch is that texteq, textne, byteaeq and byteane all follow the same pattern rather tightly. (Indeed, I think one could easily implement texteq and byteaeq with the exact same C function.) I like how we handle this for tsvector (see TSVECTORCMPFUNC in tsvector_op.c) to avoid the redundancy. If datumHasSameLength would mainly apply to these four functions or ones very similar to them, maybe we should abstract out the entire function body like we do for tsvector? A topic for a different patch in any case, I think. -- 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] texteq/byteaeq: avoid detoast [REVIEW]
On Sun, Jan 16, 2011 at 01:05:11PM -0600, Andy Colson wrote: > This is a review of: > https://commitfest.postgresql.org/action/patch_view?id=468 Thanks! > I created myself a more real world test, with a table with indexes and id's > and a large toasted field. > This will make about 600 records within the same xgroup. As well as a simple > 'c15' type of value in c we can search for. My thinking is you may not know > the exact unique id, but you do know what group its in, so that'll cut out > 90% of the records, and then you'll have to add " and c = 'c15'" to get the > exact one you want. Good to have a benchmark like that, rather than just looking at the extrema. -- 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] texteq/byteaeq: avoid detoast [REVIEW]
Hello I looked on this patch too. It's good idea. I think, so we can have a function or macro that compare a varlena sizes. Some like Datum texteq(..) { if (!datumsHasSameLength(PG_GETARG_DATUM(0), PG_GETARG_DATUM(1)) PG_RETURN_FALSE(); ... actual code .. } Regards Pavel Stehule 2011/1/16 Andy Colson : > This is a review of: > https://commitfest.postgresql.org/action/patch_view?id=468 > > Purpose: > > Equal and not-equal _may_ be quickly determined if their lengths are > different. This _may_ be a huge speed up if we dont have to detoat. > > > The Patch: > == > I was able to read and understand the patch, its a simple change and looked > correct to me (a non PG hacker). > It applies clean to git head, compiles and runs fine with debug enabled. > > make check passes > > > Usability: > == > I used _may_ above. The benchmark included with the patch, showing huge > speedups, is really contrived. It uses a where clause with a thousand > character constant: (where c = > 'long...long...long...long...ConstantText...etc'). In my opinion this is > very uncommon (the author does note this is a "best case"). If you have a > field large enough to be toasted you are not going to be using that to > search on, you are going to have an ID field that is indexed. (select c > where id = 7) > > This also only touches = and <>. > < and like wont be touched. So I think > the scope of this is limited. > > THAT being said, the patch is simple, and if you do happen to hit the code, > it will speed things up. As a user of PG I'd like to have this included. > Its a corner case, but a big corner, and its a small, simple change, and it > wont slow anything else down. > > > Performance: > > I created myself a more real world test, with a table with indexes and id's > and a large toasted field. > > create table junk(id serial primary key, xgroup integer, c text); > create index junk_group on junk(xgroup); > > > I filled it full of junk: > > do $$ > declare i integer; > declare j integer; > begin > for i in 1..100 loop > for j in 1..500 loop > insert into junk(xgroup, c) values (j, 'c'||i); > insert into junk (xgroup, c) select j, repeat('abc', > 2000)|| n from generate_series(1, 5) n; > end loop; > end loop; > end$$; > > > This will make about 600 records within the same xgroup. As well as a > simple 'c15' type of value in c we can search for. My thinking is you may > not know the exact unique id, but you do know what group its in, so that'll > cut out 90% of the records, and then you'll have to add " and c = 'c15'" to > get the exact one you want. > > I still saw a nice performance boost. > > Old PG: > $ psql < bench3.sql > Timing is on. > DO > Time: 2010.412 ms > > Patched: > $ psql < bench3.sql > Timing is on. > DO > Time: 184.602 ms > > > bench3.sql: > do $$ > declare i integer; > begin > for i in 1..400 loop > perform count(*) from junk where xgroup = i and c like 'c' || > i; > end loop; > end$$; > > > > Summary: > > Performance speed-up: Oh yeah! If you just happen to hit it, and if you do > hit it, you might want to re-think your layout a little bit. > > Do I want it? Yes please. > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] texteq/byteaeq: avoid detoast [REVIEW]
This is a review of: https://commitfest.postgresql.org/action/patch_view?id=468 Purpose: Equal and not-equal _may_ be quickly determined if their lengths are different. This _may_ be a huge speed up if we dont have to detoat. The Patch: == I was able to read and understand the patch, its a simple change and looked correct to me (a non PG hacker). It applies clean to git head, compiles and runs fine with debug enabled. make check passes Usability: == I used _may_ above. The benchmark included with the patch, showing huge speedups, is really contrived. It uses a where clause with a thousand character constant: (where c = 'long...long...long...long...ConstantText...etc'). In my opinion this is very uncommon (the author does note this is a "best case"). If you have a field large enough to be toasted you are not going to be using that to search on, you are going to have an ID field that is indexed. (select c where id = 7) This also only touches = and <>. > < and like wont be touched. So I think the scope of this is limited. THAT being said, the patch is simple, and if you do happen to hit the code, it will speed things up. As a user of PG I'd like to have this included. Its a corner case, but a big corner, and its a small, simple change, and it wont slow anything else down. Performance: I created myself a more real world test, with a table with indexes and id's and a large toasted field. create table junk(id serial primary key, xgroup integer, c text); create index junk_group on junk(xgroup); I filled it full of junk: do $$ declare i integer; declare j integer; begin for i in 1..100 loop for j in 1..500 loop insert into junk(xgroup, c) values (j, 'c'||i); insert into junk (xgroup, c) select j, repeat('abc', 2000)|| n from generate_series(1, 5) n; end loop; end loop; end$$; This will make about 600 records within the same xgroup. As well as a simple 'c15' type of value in c we can search for. My thinking is you may not know the exact unique id, but you do know what group its in, so that'll cut out 90% of the records, and then you'll have to add " and c = 'c15'" to get the exact one you want. I still saw a nice performance boost. Old PG: $ psql < bench3.sql Timing is on. DO Time: 2010.412 ms Patched: $ psql < bench3.sql Timing is on. DO Time: 184.602 ms bench3.sql: do $$ declare i integer; begin for i in 1..400 loop perform count(*) from junk where xgroup = i and c like 'c' || i; end loop; end$$; Summary: Performance speed-up: Oh yeah! If you just happen to hit it, and if you do hit it, you might want to re-think your layout a little bit. Do I want it? Yes please. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers