Date: Wed, 11 Mar 2020 16:59:21 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Richard Michael <rmichael@edgeofthenet.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow ext'd query via client native implementation vs. libpq &
 simple psql
Message-ID: <20200311215921.GZ29065@telsasoft.com>
References: 
 <CABR0jERv5L7U0eYyNxv3ZES9HbJj9r2KPK3_=oPMBXTbTC+oXw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CABR0jERv5L7U0eYyNxv3ZES9HbJj9r2KPK3_=oPMBXTbTC+oXw@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk

On Wed, Mar 11, 2020 at 03:31:48PM -0400, Richard Michael wrote:
> The query is trivial: `SELECT [cols] FROM t WHERE id = X` on a 65K row
> table.

> The Elixir client executes this as an extended query in >500-700ms, very
> slow.
> If relevant, the client does not use libpq, it is a native implementation.

> A simple query via psql `PREPARE q AS ... ; EXECUTE q;` executes in ~130ms.
> (Aside, please let me know if psql can execute extended queries.)
> To compare another extended query protocol client, I wrote a tiny C program
> using libpq PQprepare()/PQexecPrepared() executes in ~100ms.

psql can't do it, but pygres can do it since last year.

> Would wireshark on client messages reveal anything postgres can't log?

I think maybe, like how many round trips there are.  
Does the difference between the clients scale linearly with the number of rows
returned?

> Elixir/postgrex extended query: (always >500ms)
> -------------------------------

> 2020-03-11 13:46:20.658 EDT [3401] LOG:  duration: 513.792 ms  plan:
...
> Seq Scan on public.genes g0  (cost=0.00..6591.62 rows=60632 width=224)
> (actual time=0.523..33.229 rows=60623 loops=1)

You can see the "actual time" is low (that's postgres running the query), so
the rest seems to comes from something like sending data back to the client or
the client parsing the results.

-- 
Justin