Re: Redundant SQL commands

Erwin Brandstetter wrote:
> Hi developers! Hi Dave!
>
> Testing pgAdmin III v1.6.3 rev: 6112, client Win XP, host: Debian Sarge,
> PG 8.1.8.
>
> Not sure, wheter this is any important.

Probably not unless you observe some performance issues as a result of this.

>
> The last two SELECTs are done twice. Seems redundant?

The properties display and the SQL display both grab the up-to-date
values before displaying their output. This i probably for the best as
in theory both functions can be called independently of each other.

> To get the bigger picture I checked on every object type in the tree and
> found a couple of similar redundancies when refreshing.
> Here is a summary, quoting only the redundant parts:
>
> table
> SELECT opcname FROM pg_opclass WHERE oid=1978
> SELECT opcname FROM pg_opclass WHERE oid=1978
>
> view
> SELECT opcname FROM pg_opclass WHERE oid=1978
> SELECT opcname FROM pg_opclass WHERE oid=1978

This is it getting op class names for each column in an index. It could
probably be munged into a single query - patches welcome :-)

> schema
> SELECT t.oid, format_type(t.oid, t.typtypmod) AS typname, n.nspname FROM
> pg_type t, pg_namespace n WHERE t.typnamespace = n.oid
> SELECT t.oid, format_type(t.oid, t.typtypmod) AS typname, n.nspname FROM
> pg_type t, pg_namespace n WHERE t.typnamespace = n.oid
> SELECT t.oid, format_type(t.oid, t.typtypmod) AS typname, n.nspname FROM
> pg_type t, pg_namespace n WHERE t.typnamespace = n.oid

pgDatatype doing it's thing (which it does a lot!). I've been toying
with ways of keeping the results cached - that'll fit nicely in with
some other tweaks I have in mind for 1.9

> database
> SELECT has_table_privilege('pg_authid', 'SELECT')
> SELECT has_table_privilege('pg_authid', 'SELECT')

Once for login roles, once for group roles. They share a lot of code,
but are of course seperate nodes.

> One more case when opening the properties dialogue for a view:
> SELECT usename FROM pg_user ORDER BY usename
> SELECT usename FROM pg_user ORDER BY usename

Hmm, that was a minor bug. Fixed now, thanks.

> Does not happen with properties dialogue of any other object.
> But I've seen that line a lot:
> SELECT 1;
> Seems redundant per se? (Or maybe to check whether the connection is
> still alive?)

It is indeed.

> Is it appropriate to post that kind of stuff in -support or would you
> rather have me post it in _hackers?

-support is meant more for people asking for help which of course
sometimes equates to a bug, but often doesn't.

As our unofficial VP of Product QA, your reports are definitely -hackers
material. 99 times out of 100, you don't need help - you're helping us :-)

> Oh, and let me know if this report is of use to you at all. I'd rather
> not waste any of your precious time. (Neither mine ;) )

A couple of things to bear in mind - there are all sorts of strange
orders and ways in which internal functions are called in pgAdmin -
because we cannot always assume that one will always be called before or
after another, sometimes we do end up running the same or similar
queries a couple of times together - which you wouldn't normally notice
anyway unless those queries are called once for every one of 5000 objects.

Secondly, unfortunately we're not at the stage where micro optimisations
are a good use of time yet. There are bigger improvements to be made -
for example:
http://svn.pgadmin.org/cgi-bin/viewcvs.cgi?rev=6004&view=rev which made
loading the function data something like 10x faster on my laptop.

So.... I'd suggest keep an eye out for any obviously slow operations,
then trying to figure out what the cause is. It won't always be obvious
from the logs though; the fix above was just a vastly improved way of
accessing data in memory repeatedly. In those case, feel free to try to
figure it out from the code, or email the list with your findings and
we'll try to work out whats up.

Thanks, Dave.