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
Date: Wed, 11 Mar 2020 16:59:21 -0500
Message-ID: <20200311215921.GZ29065@telsasoft.com> (raw)
In-Reply-To: <CABR0jERv5L7U0eYyNxv3ZES9HbJj9r2KPK3_=oPMBXTbTC+oXw@mail.gmail.com>
References: <CABR0jERv5L7U0eYyNxv3ZES9HbJj9r2KPK3_=oPMBXTbTC+oXw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, rmichael@edgeofthenet.org
Subject: Re: Slow ext'd query via client native implementation vs. libpq & simple psql
In-Reply-To: <20200311215921.GZ29065@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox