[GENERAL] Function runtime increases after 5 calls in the same session.
Hi, all. I wrote a function and during testing it I came across the strange behaviour. Function runtime is about 200ms first 5 times, 6th and futher calls takes ~22000 ms. I simplified my schema, you can see it in attached file. I've tested on 9.4beta3 and 9.3.5. When I removed security_barrier from the view or alter column "time" type to integer (bigint, I don't remember) all goes as expected. It looks very strange for me. I will be pleased if somebody checks this. -- С уважением, Ащепков Илья koc...@gmail.com test.sql Description: application/sql -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JSONB spaces in text presentation
With the same data:
# create cast (jsonb as bytea) without function;
# select
sum(length(data::text))::float/sum(octet_length((data::jsonb)::bytea)) from
data.packets;
?column?
---
0.630663654967513
and 0.554666142734544 without spaces
On Wed, Sep 24, 2014 at 9:22 PM, Seref Arikan wrote:
> This is interesting. Most binary encoding methods I use produce smaller
> files than the text files for the same content.
> Having read your mail, I've realized that I have no reason to accept the
> same from the jsonb. I did a quick google search to see if it is wrong to
> expect binary encoding to decrease size and saw that I'm not alone (which
> still does not mean I'm being reasonable).
> This project: http://ubjson.org/#size is one of the hits which mentions
> some nice space gains thanks to binary encoding.
>
> The "much larger" part is a bit scary. Is this documented somewhere?
>
> Best regards
> Seref
>
>
> On Wed, Sep 24, 2014 at 2:44 PM, Merlin Moncure
> wrote:
>
>> On Wed, Sep 24, 2014 at 2:44 AM, Ilya I. Ashchepkov
>> wrote:
>> > I'm sorry about sending email several times. I haven't understand, was
>> it
>> > sent by gmail or not.
>> >
>> >
>> > On Wed, Sep 24, 2014 at 2:30 PM, John R Pierce
>> wrote:
>> >>
>> >> On 9/24/2014 12:23 AM, Ilya I. Ashchepkov wrote:
>> >>>
>> >>>
>> >>> Is spaces is necessary in text presentation of JSONB?
>> >>> In my data resulting text contains ~12% of spaces.
>> >>
>> >>
>> >> can you show us an example of this?
>> >
>> >
>> > One record
>> > # select data from events.data limit 1;
>> > {"can": {"lls": {"1": 76.4}, "mhs": 4674.85, "rpm": 168.888, "speed":
>> 74,
>> > "runned": 166855895, "fuel_consumption": 74213.5}, "crc": 10084, "gps":
>> 1,
>> > "gsm": {"signal": 100}, "lls": {"1": 733, "2": 717}, "used": 19,
>> "speed":
>> > 87.4, "valid": 1, "msg_id": 89, "runned": 72.75, "boot_no": 256,
>> "digital":
>> > {"in": {"1": 1, "2": 0, "3": 0, "4": 0, "5": 0, "6": 0}, "out": {"1": 0,
>> > "2": 0}}, "visible": 20, "ignition": 1, "location": {"course": 265,
>> > "altitude": 143, "latitude": 55.127888997395836, "longitude":
>> > 80.8046142578125}, "protocol": 4, "coldstart": 1, "timesource":
>> "terminal",
>> > "receiver_on": 1, "external_power": 28.07, "internal_power": 4.19}
>> >
>> > Whitespacis percents in this record:
>> > # select array_length(regexp_split_to_array(data::text, text ' '),
>> > 1)*100./length(data::text) from events.data limit 1;
>> > ?column?
>> > -
>> > 12.3417721518987342
>> >
>> > Whitespace in test data
>> > # select count(*),avg(array_length(regexp_split_to_array(data::text,
>> text '
>> > '), 1)*100./length(data::text)) from events.data ;
>> > count | avg
>> > +-
>> > 24 | 12.3649234646118312
>>
>>
>> For jsonb (unlike json), data is not actually stored as json but in a
>> binary format. It will generally be much larger than the text
>> representation in fact but in exchange for that many operations will
>> be faster. The spaces you see are generated when the jsonb type is
>> converted to text for output. I actually think it's pretty reasonable
>> to want to redact all spaces from such objects in all cases where
>> converstion to text happens (output functions, xxxto_json, etc)
>> because ~12% savings are nothing to sneeze at when moving large
>> documents in and out of the database.
>>
>> On the flip side, a more verbose prettification would be pretty nice
>> too. I wonder if a hypothetical GUC is the best way to control this
>> behavior...
>>
>> merlin
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
--
С уважением,
Ащепков Илья koc...@gmail.com
Re: [GENERAL] JSONB spaces in text presentation
IMHO, prettification is useful only for debugging.
It would be nice to have a session variable for the debug output with
spaces, new lines and indentation.
On Wed, Sep 24, 2014 at 8:44 PM, Merlin Moncure wrote:
> On Wed, Sep 24, 2014 at 2:44 AM, Ilya I. Ashchepkov
> wrote:
> > I'm sorry about sending email several times. I haven't understand, was it
> > sent by gmail or not.
> >
> >
> > On Wed, Sep 24, 2014 at 2:30 PM, John R Pierce
> wrote:
> >>
> >> On 9/24/2014 12:23 AM, Ilya I. Ashchepkov wrote:
> >>>
> >>>
> >>> Is spaces is necessary in text presentation of JSONB?
> >>> In my data resulting text contains ~12% of spaces.
> >>
> >>
> >> can you show us an example of this?
> >
> >
> > One record
> > # select data from events.data limit 1;
> > {"can": {"lls": {"1": 76.4}, "mhs": 4674.85, "rpm": 168.888, "speed": 74,
> > "runned": 166855895, "fuel_consumption": 74213.5}, "crc": 10084, "gps":
> 1,
> > "gsm": {"signal": 100}, "lls": {"1": 733, "2": 717}, "used": 19, "speed":
> > 87.4, "valid": 1, "msg_id": 89, "runned": 72.75, "boot_no": 256,
> "digital":
> > {"in": {"1": 1, "2": 0, "3": 0, "4": 0, "5": 0, "6": 0}, "out": {"1": 0,
> > "2": 0}}, "visible": 20, "ignition": 1, "location": {"course": 265,
> > "altitude": 143, "latitude": 55.127888997395836, "longitude":
> > 80.8046142578125}, "protocol": 4, "coldstart": 1, "timesource":
> "terminal",
> > "receiver_on": 1, "external_power": 28.07, "internal_power": 4.19}
> >
> > Whitespacis percents in this record:
> > # select array_length(regexp_split_to_array(data::text, text ' '),
> > 1)*100./length(data::text) from events.data limit 1;
> > ?column?
> > -
> > 12.3417721518987342
> >
> > Whitespace in test data
> > # select count(*),avg(array_length(regexp_split_to_array(data::text,
> text '
> > '), 1)*100./length(data::text)) from events.data ;
> > count | avg
> > +-
> > 24 | 12.3649234646118312
>
>
> For jsonb (unlike json), data is not actually stored as json but in a
> binary format. It will generally be much larger than the text
> representation in fact but in exchange for that many operations will
> be faster. The spaces you see are generated when the jsonb type is
> converted to text for output. I actually think it's pretty reasonable
> to want to redact all spaces from such objects in all cases where
> converstion to text happens (output functions, xxxto_json, etc)
> because ~12% savings are nothing to sneeze at when moving large
> documents in and out of the database.
>
> On the flip side, a more verbose prettification would be pretty nice
> too. I wonder if a hypothetical GUC is the best way to control this
> behavior...
>
> merlin
>
--
С уважением,
Ащепков Илья koc...@gmail.com
Re: [GENERAL] JSONB spaces in text presentation
I'm sorry about sending email several times. I haven't understand, was it
sent by gmail or not.
On Wed, Sep 24, 2014 at 2:30 PM, John R Pierce wrote:
> On 9/24/2014 12:23 AM, Ilya I. Ashchepkov wrote:
>
>>
>> Is spaces is necessary in text presentation of JSONB?
>> In my data resulting text contains ~12% of spaces.
>>
>
> can you show us an example of this?
>
One record
# select data from events.data limit 1;
{"can": {"lls": {"1": 76.4}, "mhs": 4674.85, "rpm": 168.888, "speed": 74,
"runned": 166855895, "fuel_consumption": 74213.5}, "crc": 10084, "gps": 1,
"gsm": {"signal": 100}, "lls": {"1": 733, "2": 717}, "used": 19, "speed":
87.4, "valid": 1, "msg_id": 89, "runned": 72.75, "boot_no": 256, "digital":
{"in": {"1": 1, "2": 0, "3": 0, "4": 0, "5": 0, "6": 0}, "out": {"1": 0,
"2": 0}}, "visible": 20, "ignition": 1, "location": {"course": 265,
"altitude": 143, "latitude": 55.127888997395836, "longitude":
80.8046142578125}, "protocol": 4, "coldstart": 1, "timesource": "terminal",
"receiver_on": 1, "external_power": 28.07, "internal_power": 4.19}
Whitespacis percents in this record:
# select array_length(regexp_split_to_array(data::text, text ' '),
1)*100./length(data::text) from events.data limit 1;
?column?
-
12.3417721518987342
Whitespace in test data
# select count(*),avg(array_length(regexp_split_to_array(data::text, text
' '), 1)*100./length(data::text)) from events.data ;
count | avg
+-
24 | 12.3649234646118312
>
> --
> john r pierce 37N 122W
> somewhere on the middle of the left coast
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
С уважением,
Ащепков Илья koc...@gmail.com
[GENERAL] JSONB spaces in text presentation
Hi. Is spaces is nessesary in text presentation of JSONB? In my data resulting text contains ~12% of spaces. I'm developing web application, and want to get json-string from pg and send it to browser without repacking. -- С уважением, Ащепков Илья koc...@gmail.com
[GENERAL] JSONB spaces in text presentation
Hi. Is spaces is necessary in text presentation of JSONB? In my data resulting text contains ~12% of spaces. I'm developing web application, and want to get json-string from pg and send it to browser without repacking. -- С уважением, Ащепков Илья koc...@gmail.com
[GENERAL] JSONB spaces in text presentation
Hi. Is spaces is nessesary in text presentation of JSONB? In my data resulting text contains ~12% of spaces. I'm developing web application, and want to get json-string from pg and send it to browser without repacking. -- С уважением, Ащепков Илья koc...@gmail.com
