Slow tab completion (7.4) - Mailing list pgsql-hackers

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>