Thread: Binary transfer vs Text transfer

Binary transfer vs Text transfer

From
Aleksei Ivanov
Date:
Hello, colleagues,

I have one question related to my current measurements results.

I am fetching integers in text format like:

Select * from table limit 10000000. It take 18.5 seconds to finish and the transfer data is 633 MB. 

When I fetching the same data using binary cursor, the transfer data is 480 MB, but the transfer time is 21.5 seconds?

So, I have one question could someone explain what can be the reason why the transferring time for binary data is higher?

Best regards,

Re: Binary transfer vs Text transfer

From
Andres Freund
Date:
Hi,


On 2020-06-17 11:55:44 -0700, Aleksei Ivanov wrote:
> I have one question related to my current measurements results.
> 
> I am fetching integers in text format like:
> 
> Select * from table limit 10000000. It take 18.5 seconds to finish and the
> transfer data is 633 MB.
> 
> When I fetching the same data using binary cursor, the transfer data is 480
> MB, but the transfer time is 21.5 seconds?
> 
> So, I have one question could someone explain what can be the reason why
> the transferring time for binary data is higher?

This thread might be interesting:
https://www.postgresql.org/message-id/CAMkU%3D1whbRDUwa4eayD9%2B59K-coxO9senDkPRbTn3cg0pUz4AQ%40mail.gmail.com

Greetings,

Andres Freund



Re: Binary transfer vs Text transfer

From
Aleksei Ivanov
Date:

Hi,

Thanks for the attached link, but I also noticed using iftop, that during fetching the data there is almost no delay using text transfer, while there is several seconds in delay before data is starting fetching using binary transfer. Could you suggest where can I have a look to resolve it or decrease that time? 

Best regards,


On Wed, Jun 17, 2020 at 12:12 Andres Freund <andres@anarazel.de> wrote:
Hi,


On 2020-06-17 11:55:44 -0700, Aleksei Ivanov wrote:
> I have one question related to my current measurements results.
>
> I am fetching integers in text format like:
>
> Select * from table limit 10000000. It take 18.5 seconds to finish and the
> transfer data is 633 MB.
>
> When I fetching the same data using binary cursor, the transfer data is 480
> MB, but the transfer time is 21.5 seconds?
>
> So, I have one question could someone explain what can be the reason why
> the transferring time for binary data is higher?

This thread might be interesting:
https://www.postgresql.org/message-id/CAMkU%3D1whbRDUwa4eayD9%2B59K-coxO9senDkPRbTn3cg0pUz4AQ%40mail.gmail.com

Greetings,

Andres Freund