Question on the right way to think about order by

[Paula Kirsch]

When I think about using order by in a select statement, e.g.

select id, name, price from stuff order by name;


I think of order by as an operator sorting data returned by the select
statement.

When I think about order by in an aggregate statement, e.g.

select string_agg(product, ' | ' order by product) from products;


is it correct to think of order by as a parameter passed to string_agg?

When should parameters be passed as text v. binary?

[Paula Kirsch]

I'm just trying to understand the trade-offs between sending everything
always as text, all integer parameters as binary, floats as binary, etc.

Re: When should parameters be passed as text v. binary?

[Paula Kirsch]

Good point and I loved the way you put it. More low level stuff I need to
learn.

I'm still struggling trying to find the list of data type oids either in
the documentation or in the postgresql source code so that I can specify
the data correctly (assuming, of course, I make sure the binary on both
sides is compatible.

Thank you.

On Sat, Jan 4, 2020 at 3:30 PM Justin  wrote:

> As noted by Adrian what is the USE CASE
>
> As a general rule one wants to use the format the data is being stored
> in.  every time data is cast to another type its going to eat those all so
> precious CPU cycles.  (all the horror of electrons turned into infrared
> beams)
>
> converting Bytea type to a string encoded in Base64 adds 30% overhead.
> converting an integer tor ASCII can add allot of overhead.
>
> The answer is it depends on the USE CASE if casting adds any benefit.  my
> gut tells me it will not add any benefiet
>
>
>
> On Sat, Jan 4, 2020 at 1:59 PM Andrew Gierth 
> wrote:
>
>> >>>>> "Paula" == Paula Kirsch  writes:
>>
>>  Paula> I'm just trying to understand the trade-offs between sending
>>  Paula> everything always as text, all integer parameters as binary,
>>  Paula> floats as binary, etc.
>>
>> For passing data from client to server, there's no particular reason not
>> to use the binary format for any data type that you understand (and
>> where you're passing the data type oid explicitly in the query, rather
>> than just leaving it as unknown).
>>
>> For results, things are harder, because libpq is currently
>> all-or-nothing about result type formats, and if you start using
>> extension types then not all of them even _have_ a binary format. And to
>> decode a binary result you need to know the type, and have code to
>> handle every specific type's binary format.
>>
>> --
>> Andrew (irc:RhodiumToad)
>>
>>
>>