Thread: Need indexes on inherited tables?
Dear Postgresql experts, I have a base table that declares a primary key spanning a couple of columns: create table B ( id integer, xx someothertype, ..... primary key (id, xx) ); and a number of derived tables that inherit from B: create table T (.... ) inherits (B); An index is automatically created for B because of the primary key. If I search for something in T using the key columns, e.g. I do select * from T where id=1 and xx=something; will the index be used? Or must I explicity create an index on id and xx for T and each of the other derived tables? Is it any different if I search in B and find rows that are actually in T? (Slightly unrelated: does the index on (id,xx) help when I am searching only on id?) Thanks for any insight anyone can offer. --Phil.
Table T is not inheriting any indexes, neither the primary key constraint. That means that the search is going to use anindex scan on table B and a sequential scan on table T (unless of course you add a primary key constraint or an index ontable T).<br /><br /> You can check this things doing: <br /> ->SET enable_seqscan TO off;<br /> ->EXPLAIN SELECT* FROM B WHERE id=5; <br /> you'll see an index scan on table B and sequential scans on the other tables.<br /><br/> Doing:<br /> ->SELECT C.relname AS table_name, C2.relname AS index_name FROM pg_index I LEFT JOIN pg_class C ON(I.indrelid=C.oid) LEFT JOIN pg_class C2 ON (C2.oid=I.indexrelid) WHERE C.relname ILIKE '<table_name>'<br /><br/> you can find out what indexes are available for table_name (or \d <table_name> in psql).<br /><br /><br />On Sat, 2004-06-26 at 16:29, Phil Endecott wrote: <blockquote type="CITE"><pre><font color="#737373"><i>Dear Postgresqlexperts, I have a base table that declares a primary key spanning a couple of columns: create table B ( id integer, xx someothertype, ..... primary key (id, xx) ); and a number of derived tables that inherit from B: create table T (.... ) inherits (B); An index is automatically created for B because of the primary key. If I search for something in T using the key columns, e.g. I do select * from T where id=1 and xx=something; will the index be used? Or must I explicity create an index on id and xx for T and each of the other derived tables? Is it any different if I search in B and find rows that are actually in T? (Slightly unrelated: does the index on (id,xx) help when I am searching only on id?) Thanks for any insight anyone can offer. --Phil. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings </i></font></pre></blockquote>
I asked if derived tables use primary key indexes generated in the base tables that they inherit from. Franco Bruno Borghesi replied: > [the derived table] is not inheriting any indexes, neither the > primary key constraint. OK, thanks! I had come to the conclusion that it was not using the index, but I'm really surprised to hear that the primarykey constraint that I define in a base table is not inherited. Are any constraints inherited? What happens if Ideclare a single-column primary key? What if I declare a "not null" constraint or a "check" constraint in a base table? Having to replicate the constraints and indexes for each derived table is a pain - lots of error-prone typing - but thereis a more serious problem: how can I ensure that these keys are unique across all of the derived tables? (i.e. if T1and T2 inherit from B, and B's primary key is (id,xx), then I want there to be at most one row in (T1 union T2) that hasany value of (id,xx).) Is this a possible area for future enhancements? Regards, --Phil.
> Is this a possible area for future enhancements? Yes. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
you can find more information here: <a href="http://www.postgresql.org/docs/7.4/static/sql-createtable.html">http://www.postgresql.org/docs/7.4/static/sql-createtable.html</a> (underthe parameters section). For unique constraints, the only thing I can think of is a table check constraint (SELECTcount(pk)=0).<br /><br /> I agree with you, right now there are many inconveniences with postgresql inheritance, that'swhy I never use inheritance this way. <br /> I usually stick to the "standard" way, for example, I create a peopletable, and for every people I want to insert in my database there is one row in this table, plus one row with the sameid/pk (which is also a fk) in a specialized table (let's say students, teachers, whatever). <br /> This simplifies things,because attribute definitions/defaults/constraints are always in one table. Maybe you get into some other troubles,but I find them less painfull <img align="middle" alt=":)" border="0" src="cid:1088440168.910.151.camel@taz.oficina"/><br /><br /> On Sat, 2004-06-26 at 19:30, Phil Endecott wrote: <blockquotetype="CITE"><pre><font color="#737373"><i>I asked if derived tables use primary key indexes generated in the basetables that they inherit from. Franco Bruno Borghesi replied: > [the derived table] is not inheriting any indexes, neither the > primary key constraint. OK, thanks! I had come to the conclusion that it was not using the index, but I'm really surprised to hear that the primarykey constraint that I define in a base table is not inherited. Are any constraints inherited? What happens if Ideclare a single-column primary key? What if I declare a "not null" constraint or a "check" constraint in a base table? Having to replicate the constraints and indexes for each derived table is a pain - lots of error-prone typing - but thereis a more serious problem: how can I ensure that these keys are unique across all of the derived tables? (i.e. if T1and T2 inherit from B, and B's primary key is (id,xx), then I want there to be at most one row in (T1 union T2) that hasany value of (id,xx).) Is this a possible area for future enhancements? Regards, --Phil. </i></font></pre></blockquote>