Re: Redundant SQL commands - Mailing list pgadmin-support

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.