Improving the performance of psql tab completion
От | Merlin Moncure |
---|---|
Тема | Improving the performance of psql tab completion |
Дата | |
Msg-id | CAHyXU0z-nqatjySsTw3FpMfc6wiFWDb+4iPcMQES7s0KZ4Oz5Q@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Improving the performance of psql tab completion
|
Список | pgsql-hackers |
Hackers, I have a database with 94059 entries in pg_class. Things are mostly working fine but psql tab completion is frustratingly slow (around 2.5 seconds on this box). I poked around in psql a bit and saw that the main culprit was the table visibility condition check. Here's a typical query (there are other portions unioned in that are not relevant to performance): SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'f') AND substring(pg_catalog.quote_ident(c.relname),1,7)='pg_stat' AND pg_catalog.pg_table_is_visible(c.oid)AND c.relnamespace <> (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog') By swapping out AND pg_catalog.pg_table_is_visible(c.oid) with AND c.relnamespace in(select oid from pg_namespace where nspname in (select unnest(current_schemas(true)))) the response time of the tab completion query got knocked down to a breezy 88ms. Now, this is a bit crude compared to what RelationIsVisible is doing. In particular, besides checking the schema path it's doing this: /* * If it is in the path, it might still not be visible; it could be * hidden by another relation of the same name earlier in the path. So * we must do a slow check for conflicting relations. */ ...but isn't that overkill for tab completion? The simple query above seems to exhibit the same behavior (for psql) but am I missing something? merlin
В списке pgsql-hackers по дате отправления: