Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having a situation very similar to the one described here:

I've got a SaaS situation where I'm using 1000+ schemas in a single database (each schema contains the same tables, just different data per tenant). I used schemas so that the shared app servers could share a connection to the single database for all schemas. Things are working fine.

and, while the application itself appears to be working fine, some queries involving system catalogs are very slow. Also, psql's auto-completion is totally useless and \dt is very slow.

In particular, I need to calculate the on-disk size of each schema using something like this:

SELECT sum(pg_total_relation_size(c.oid)) AS size
FROM   pg_namespace n
JOIN   pg_class     c ON c.relnamespace = n.oid
WHERE  n.nspname = 'abbiecarmer'
AND    c.relkind = 'r';

which is quite slow. Looking at the query plan, I see

Aggregate  (cost=136903.16..136903.17 rows=1 width=4) (actual time=1024.420..1024.420 rows=1 loops=1)             
  ->  Hash Join  (cost=8.28..136902.86 rows=59 width=4) (actual time=143.247..1016.749 rows=60 loops=1)           
        Hash Cond: (c.relnamespace = n.oid)                                                                       
        ->  Seq Scan on pg_class c  (cost=0.00..133645.24 rows=866333 width=8) (actual time=0.045..943.029 rows=879788 loops=1)                                                                                                    │
              Filter: (relkind = 'r'::"char")                                                                     
              Rows Removed by Filter: 2610112                                                                     
        ->  Hash  (cost=8.27..8.27 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=1)                      
              Buckets: 1024  Batches: 1  Memory Usage: 1kB                                                        
              ->  Index Scan using pg_namespace_nspname_index on pg_namespace n  (cost=0.00..8.27 rows=1 width=4)(actual time=0.029..0.030 rows=1 loops=1)                                                                        │
                    Index Cond: (nspname = 'abbiecarmer'::name)                                                   
Total runtime: 1024.476 ms                                                                                        

Which, if I understand it right, tells that 90% of the query time is spent sequentially scanning pg_class relation.

I stopped postmaster, ran the backend in single-user mode and added the following indexes:

create index pg_class_relnamespace_index on pg_class(relnamespace);
REINDEX INDEX pg_class_relnamespace_index;

create index pg_class_reltablespace_index on pg_class(reltablespace);
REINDEX INDEX pg_class_reltablespace_index;

(I've also got thousands of tablespaces too). Now the query is ~100 times faster and the plan looks much nicer:

Aggregate  (cost=846.91..846.92 rows=1 width=4) (actual time=10.609..10.610 rows=1 loops=1)                       
  ->  Nested Loop  (cost=0.00..846.61 rows=60 width=4) (actual time=0.069..0.320 rows=60 loops=1)                 
        ->  Index Scan using pg_namespace_nspname_index on pg_namespace n  (cost=0.00..8.27 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)                                                                              │
              Index Cond: (nspname = 'abbiecarmer'::name)                                                        
        ->  Index Scan using pg_class_relnamespace_index on pg_class c  (cost=0.00..837.59 rows=75 width=8) (actual time=0.043..0.271 rows=60 loops=1)                                                                             │
              Index Cond: (relnamespace = n.oid)                                                                 
              Filter: (relkind = 'r'::"char")                                                                    
              Rows Removed by Filter: 102                                                                        
Total runtime: 10.696 ms                                                                                         

However, in the above thread, Tom Lane, who is one of Postgres core contributors, says:

There are a lot of gotchas here, notably that the session in which you create the index won't know it's there (so in this case, a reindex on pg_class would likely be advisable afterwards). I still think you'd be nuts to try it on a production database, but ...

I'm also worried by the fact that modification of system catalogs seemed to be completely disabled in Postgres 9.0 and 9.1 (I'm using 9.2) - I suppose it was done for a reason?

So, the question is: what are the gotchas in adding an index to a system catalog in Postgres and will I be nuts if I (eventually) do that on a production system?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

The diagnosis wasn't so far off the point after all. Obviously the catalogs are not well prepared to deal with thousands of schemas.

Unfortunately you are trying to use the views from the information schema, which can be excruciatingly slow. Those are complex views involving many tables to produce an exactly standard-compliant state. Just have a look at the output of EXPLAIN ANALYZE or the graphic representation in pgAdmin to get an impression.

Use the catalog tables directly instead:

And, while there are no guarantees from the project, basic elements of pg_namespace or pg_class are not likely to change across major versions, either.

Try this query instead. Should be much faster out of the box:

SELECT pg_total_relation_size(c.oid) AS size
FROM   pg_namespace n
JOIN   pg_class     c ON c.relnamespace = n.oid
WHERE  n.nspname = :schema_name
AND    c.relkind = 'r';

Or maybe a bit faster:

SELECT pg_total_relation_size(c.oid) AS size
FROM   pg_class c 
WHERE  c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = :schema_name)
AND    c.relkind = 'r';

-> SQLfiddle demo (including update)

If you are going to use indexes, make the first one on pg_class a partial index:

CREATE INDEX pg_class_relnamespace_idx on pg_class(relnamespace)
WHERE relkind = 'r';

Smaller, faster, less likely to cause problems, because the index itself is not included in the index.

Your second index in the question is probably a copy / paste artifact. You did not mention tablespaces playing a role, nor do your query or query plan show anything correxponding to it.

create index pg_class_reltablespace_index on pg_class(reltablespace);

Would have to be:

CREATE INDEX pg_namespace_nspname_idx on pg_namespace(nspname);

However, I certainly won't claim to know nearly as much about the system catalog as Tom Lane does. If he says you'd be nuts to try this in a productive system, then you are nuts to do it anyway.

Then again, Tom wrote:

I still think you'd be nuts to try it on a production database, but ...

Bold emphasis mine. Which tells me, he is not completely against it, just not willing to guarantee for anything. Which makes it a lot less nutty. I still wouldn't recommend it.

share|improve this answer
    
I've done some testing and there does not seem to be a dramatic difference between querying from pg_tables and the query you proposed - your query still does a sequential scan on a table with one million rows. Adding a couple of indexes, however, makes a huge difference. I've edited my question to include query plans (note that I used your query, not the original one). So the question stands :) –  Sergey Jul 28 '13 at 21:52
    
@Sergey: Consider the updates. –  Erwin Brandstetter Jul 29 '13 at 1:13

Personally I'd probably do this by creating an ON COMMIT DROP temporary table to contain a copy of pg_class, building an index on it, and querying that. That way you aren't messing with the catalogs, and you aren't paying index maintenance costs on your added indexes. Unless you're running this query *all * the time it's probably the safest bet.

IIRC some database engines can even do this for you automatically, planning a query to materialize a result and create a temporary index on it. PostgreSQL doesn't support doing this, but you can do it manually when you know it's the fastest option.

share|improve this answer
    
Wouldn't copying a table with one million rows be an expensive operation? I'm also finding that adding the indexes improved general Postgres performance - I guess it is able to use the indexes when looking up namespaces etc., although I'm not sure how to test it. –  Sergey Jul 30 '13 at 2:00
    
@Sergey Well, it depends on how often you query it. All I'm suggesting is a lower impact way to do it if you're concerned that what you're doing might be unsafe. In practice copying a million rows from a table that's cached in RAM to a temp table is actually going to be pretty cheap. –  Craig Ringer Jul 30 '13 at 2:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.