That's 1690 msec (1.69 seconds, and that is how long it takes to fetch 20k (small-ish) rows without LOBs (LOBs are a few lines below on the screenshot)
On Mon, Sep 3, 2018 at 4:40 PM Dave Cramer <p...@fastcrypt.com> wrote: > the one you have highlighted ~1.69ms > > Dave Cramer > > da...@postgresintl.com > www.postgresintl.com > > > On Mon, 3 Sep 2018 at 10:38, Mate Varga <m...@matevarga.net> wrote: > >> Which frame do you refer to? >> >> On Mon, Sep 3, 2018 at 3:57 PM Dave Cramer <p...@fastcrypt.com> wrote: >> >>> Not sure why reading from a socket is taking 1ms ? >>> >>> Dave Cramer >>> >>> da...@postgresintl.com >>> www.postgresintl.com >>> >>> >>> On Mon, 3 Sep 2018 at 09:39, Mate Varga <m...@matevarga.net> wrote: >>> >>>> Hi, >>>> >>>> https://imgur.com/a/ovsJPRv -- I've uploaded the profiling info (as an >>>> image, sorry). It seems this is a JDBC-level problem. I understand that the >>>> absolute timing is not meaningful at all because you don't know how large >>>> the resultset is, but I can tell that this is only a few thousands rows + >>>> few thousand largeobjects, each largeobject is around 1 kByte. (Yes I know >>>> this is not a proper use of LOBs -- it's a legacy db structure that's hard >>>> to change.) >>>> >>>> Thanks. >>>> Mate >>>> >>>> On Mon, Sep 3, 2018 at 11:52 AM Mate Varga <m...@matevarga.net> wrote: >>>> >>>>> Hey, >>>>> >>>>> we'll try to test this with pure JDBC versus hibernate. Thanks! >>>>> >>>>> >>>>> On Mon, Sep 3, 2018 at 11:48 AM Dave Cramer <p...@fastcrypt.com> wrote: >>>>> >>>>>> >>>>>> >>>>>> On Mon, 3 Sep 2018 at 03:55, Mate Varga <m...@matevarga.net> wrote: >>>>>> >>>>>>> Basically there's a class with a byte[] field, the class is mapped >>>>>>> to table T and the byte field is annotated with @Lob so it goes to the >>>>>>> pg_largeobject table. >>>>>>> >>>>>> >>>>>> Ah, so hibernate is in the mix. I wonder if that is causing some >>>>>> challenges ? >>>>>> >>>>>> >>>>>>> The DB is on separate host but relatively close to the app, and I >>>>>>> can reproduce the problem locally as well. One interesting bit is that >>>>>>> turning of SSL between the app and PSQL speeds up things by at least >>>>>>> 50%. >>>>>>> >>>>>>> Ah, one addition -- the binary objects are encrypted, so their >>>>>>> entropy is very high. >>>>>>> >>>>>>> Any chance you could write a simple non-hibernate test code to time >>>>>> the code ? >>>>>> >>>>>> Dave Cramer >>>>>> >>>>>> dave.cra...@crunchydata.ca >>>>>> www.crunchydata.ca >>>>>> >>>>>> >>>>>> >>>>>>> Mate >>>>>>> >>>>>>> On Sun, Sep 2, 2018 at 12:55 AM Dave Cramer <p...@fastcrypt.com> >>>>>>> wrote: >>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On Fri, 31 Aug 2018 at 10:15, Mate Varga <m...@matevarga.net> wrote: >>>>>>>> >>>>>>>>> I see -- we could try that, though we're mostly using an ORM >>>>>>>>> (Hibernate) to do this. Thanks! >>>>>>>>> >>>>>>>>> On Fri, Aug 31, 2018 at 3:57 PM Dmitry Igrishin <dmit...@gmail.com> >>>>>>>>> wrote: >>>>>>>>> >>>>>>>>>> пт, 31 авг. 2018 г. в 16:35, Mate Varga <m...@matevarga.net>: >>>>>>>>>> > >>>>>>>>>> > Hi, >>>>>>>>>> > >>>>>>>>>> > we're fetching binary data from pg_largeobject table. The data >>>>>>>>>> is not very large, but we ended up storing it there. If I'm copying >>>>>>>>>> the >>>>>>>>>> data to a file from the psql console, then it takes X time (e.g. a >>>>>>>>>> second), >>>>>>>>>> fetching it through the JDBC driver takes at least 10x more. We >>>>>>>>>> don't see >>>>>>>>>> this difference between JDBC and 'native' performance for anything >>>>>>>>>> except >>>>>>>>>> largeobjects (and bytea columns, for the record). >>>>>>>>>> > >>>>>>>>>> > Does anyone have any advice about whether this can be tuned or >>>>>>>>>> what the cause is? >>>>>>>>>> I don't know what a reason of that, but I think it's reasonable >>>>>>>>>> and >>>>>>>>>> quite simple to call lo_import()/lo_export() via JNI. >>>>>>>>>> >>>>>>>>> >>>>>>>> Can't imagine that's any faster. The driver simply implements the >>>>>>>> protocol >>>>>>>> >>>>>>>> Do you have any code to share ? Any other information ? >>>>>>>> >>>>>>>> Is the JDBC connection significantly further away network wise ? >>>>>>>> >>>>>>>> >>>>>>>> Dave Cramer >>>>>>>> >>>>>>>> da...@postgresintl.com >>>>>>>> www.postgresintl.com >>>>>>>> >>>>>>>
