Thread: 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.