Re: Bad performance on simple query

On Mon, Nov 17, 2008 at 6:14 PM, Dimi Paun <dimi(at)lattica(dot)com> wrote:
>
> On Mon, 2008-11-17 at 10:40 -0700, Scott Marlowe wrote:
>> I'm guessing a fair bit of that time is pgadminIII prettifying the
>> output for you, etc. I.e. it's not all transfer time. Hard to say
>> without hooking some kind of profiler in pgadminIII. Is psql running
>> local and pgadminIII remotely? Or are they both remote? If both psql
>> and pgadminIII are remote (i.e. same basic circumstances) then it's
>> got to be a difference in the client causing the extra time. OR is
>> this output of explain analyze?
>
> With \timing on I get basically the same output (local vs remote)
> in psql (0.668ms vs. 0.760ms). More like it.
>
>
> WTH is pgadminIII reporting?!?

Exactly what it's supposed to be, however it's using libpq's
asynchronous query interface and has to pass the query result through
the wxWidgets event handling system, both of which seem to add a few
milliseconds to the overall query time from the quick testing I've
just done. In a GUI app like pgAdmin, we need use this kind of
architecture to allow the UI to continue processing events (such as
button clicks, redraws etc), and to allow multiple windows to work
independently without one query locking up the whole app.

Note that the rendering time that Tom mentioned the other day which
used to confuse things has not been an issue for a couple of years -
that was dependent on resultset size and could lead to much bigger
variations. that was fixed by having libpq act as a virtual data store
for the UI instead of transferring data from the PGresult to the data
grid's own data store.

I think the bottom line is that you cannot compare psql and pgAdmin's
timings because the architectures of the two apps are very different.
Further, pgAdmin isn't the best choice for micro-optimisation of
extremely fast queries.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com