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=1 width=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 <[EMAIL PROTECTED]>
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
