Skip site navigation (1) Skip section navigation (2)

Slow tab completion (7.4)

From: Rod Taylor <rbt(at)sitesell(dot)com>
To: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Slow tab completion (7.4)
Date: 2004-12-22 00:53:00
Message-ID: 1103676780.700.366.camel@home (view raw or flat)
Thread:
Lists: 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=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 <rbt(at)sitesell(dot)com>


Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2004-12-22 04:25:12
Subject: Re: Bgwriter behavior
Previous:From: Matthew T. O'ConnorDate: 2004-12-21 23:50:52
Subject: Re: pg_autovacuum w/ dbt2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group