Обсуждение: Redundant SQL commands

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.
While checking on the SQL sent to the database for displaying sequence 
properties (with log_statement = 'all' in postgresql.conf) I made the 
following observations.

When I refresh the properties of a sequence in the pgAdmin object tree 
(hitting F5) these statements are sent:
(full quote)
SELECT cl.oid, relname, pg_get_userbyid(relowner) AS seqowner, relacl, 
description     FROM pg_class cl     LEFT OUTER JOIN pg_description des ON des.objoid=cl.oid    WHERE relkind = 'S' AND
relnamespace = 2200::oid      AND cl.oid=1537768::oid    ORDER BY relname
 
SELECT blks_read, blks_hit FROM pg_statio_all_sequences WHERE relid = 
1537768::oid
SELECT last_value, min_value, max_value, cache_value, is_cycled, 
increment_by FROM termin_termin_id_seq
SELECT last_value, min_value, max_value, cache_value, is_cycled, 
increment_by FROM termin_termin_id_seq
SELECT blks_read, blks_hit FROM pg_statio_all_sequences WHERE relid = 
1537768::oid

The last two SELECTs are done twice. Seems redundant?
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

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

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

No redundancies when refreshing other objects:
domain, aggregate, fuction, procedure, type etc. (didn't have any custom 
conversions, slony nodes, operators or operator classes to check on)

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

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?)

Is it appropriate to post that kind of stuff in -support or would you 
rather have me post it in _hackers?
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 ;) )


Regards
Erwin

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.