It seems that with more than a few thousand structures in pg_class, tab
completion seems to take quite a long time in the en_US locale.
Simply switching the where clauses around gets a substantial performance
increase. Using an index would be even better, but it remains unused due
to the "pattern matching in a locale" issue.
dragos=# explain analyze
dragos-# SELECT c.oid,
dragos-# n.nspname,
dragos-# c.relname
dragos-# FROM pg_catalog.pg_class c
dragos-# LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
dragos-# WHERE pg_catalog.pg_table_is_visible(c.oid)
dragos-# AND c.relname ~ '^product_affiliate_weekly$'
dragos-# ORDER BY 2, 3;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------Sort
(cost=67.73..67.74 rows=1 width=132) (actual
time=5038.271..5038.272 rows=1 loops=1) Sort Key: n.nspname, c.relname -> Nested Loop Left Join (cost=0.00..67.72
rows=1width=132)
(actual time=13.345..5038.228 rows=1 loops=1) -> Seq Scan on pg_class c (cost=0.00..63.12 rows=1 width=72)
(actual time=13.271..5038.139 rows=1 loops=1) Filter: (pg_table_is_visible(oid) AND (relname ~
'^product_affiliate_weekly$'::text)) -> Index Scan using pg_namespace_oid_index on pg_namespace n
(cost=0.00..4.58 rows=1 width=68) (actual time=0.050..0.059 rows=1
loops=1) Index Cond: (n.oid = "outer".relnamespace)Total runtime: 5038.566 ms
dragos=# explain analyze SELECT c.oid,
dragos-# n.nspname,
dragos-# c.relname
dragos-# FROM pg_catalog.pg_class c
dragos-# LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
c.relnamespace
dragos-# WHERE c.relname ~ '^product_affiliate_weekly$'
dragos-# AND pg_catalog.pg_table_is_visible(c.oid)
dragos-# ORDER BY 2, 3;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------Sort
(cost=67.73..67.74 rows=1 width=132) (actual time=44.486..44.487
rows=1 loops=1) Sort Key: n.nspname, c.relname -> Nested Loop Left Join (cost=0.00..67.72 rows=1 width=132)
(actual time=13.455..43.015 rows=1 loops=1) -> Seq Scan on pg_class c (cost=0.00..63.12 rows=1 width=72)
(actual time=11.129..40.667 rows=1 loops=1) Filter: ((relname ~ '^product_affiliate_weekly$'::text)
AND pg_table_is_visible(oid)) -> Index Scan using pg_namespace_oid_index on pg_namespace n
(cost=0.00..4.58 rows=1 width=68) (actual time=2.281..2.296 rows=1
loops=1) Index Cond: (n.oid = "outer".relnamespace)Total runtime: 44.788 ms
(8 rows)
--
Rod Taylor <rbt@sitesell.com>
