Re: Optimizing query

[Peter J. Holzer]

On 2026-09-09 18:22:52 -0400, Ron Johnson wrote:
> On Wed, Sep 9, 2026 at 1:43 PM Igor Korot <ikoro...@gmail.com> wrote:
>     Can below 4 queries
> 
>         std::wstring query1 = L"SELECT rolname FROM pg_roles";
>         std::wstring query2 = L"SELECT datname FROM pg_database WHERE
>     datistemplate = true;";
>         std::wstring query3 = L"SELECT pg_encoding_to_char( conforencoding
>     ) AS name FROM pg_conversion";
>         std::wstring query4 = L"SELECT collname, collencoding,
>     collprovider collctype FROM pg_collation";
>         std::wstring query5 = L"SELECT spcname FROM pg_tablespace";
> 
>     be made as one big query to use a 1 DB hit?
> 
> 
> Bereft of the C++ cruft, here are the queries:
> SELECT rolname
> FROM pg_roles;
> 
> SELECT datname
> FROM pg_database
> WHERE datistemplate = true;
> 
> SELECT pg_encoding_to_char(conforencoding) AS name
> FROM pg_conversion;
> 
> SELECT collname, collencoding, collprovider, collctype
> FROM pg_collation;
> 
> SELECT spcname
> FROM pg_tablespace;
> 
> The sticky wicket is the four columns in the pg_collation query.
> 
> You could probably write a stored function or procedure which returns a 
> complex
> json object with the results of the four queries (or four jsonb objects, one
> for each query).

You don't need a stored procedure for that. Simple (although a bit
verbose) SQL is sufficient.

I'm just using two of the queries here for brevity, since the other two
have same structure as the pg_roles query.

> Without the pg_collation query, you could make a UNION ALL like:

You can still do this:

    SELECT 'pg_roles' as "table", array_to_json(array_agg(rolname)) as "info"
    FROM pg_roles
    union all
    SELECT 'pg_collation',
        array_to_json(array_agg(json_build_object(
            'collname', collname,
            'collencoding', collencoding,
            'collprovider', collprovider,
            'collctype', collctype
        )))
    FROM pg_collation;

or you could use CTEs and a join to put them into columns of a single
row:

    with pg_roles as (
        SELECT array_to_json(array_agg(rolname)) as pg_roles
        FROM pg_roles
    ),
    pg_collation as (
    SELECT
        array_to_json(array_agg(json_build_object(
            'collname', collname,
            'collencoding', collencoding,
            'collprovider', collprovider,
            'collctype', collctype
        ))) as pg_collation
        from pg_collation
    )
    select * from pg_roles, pg_collation;

Personally I would stick with the individual queries. I don't see much
value in packing all of that into a single query.

        hjp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | h...@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

signature.asc
Description: PGP signature