[SQL] 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. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Need indexes on inherited tables?
Table T is not inheriting any indexes, neither the primary key constraint. That means that the search is going to use an index scan on table B and a sequential scan on table T (unless of course you add a primary key constraint or an index on table T). You can check this things doing: ->SET enable_seqscan TO off; ->EXPLAIN SELECT * FROM B WHERE id=5; you'll see an index scan on table B and sequential scans on the other tables. Doing: ->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 '' you can find out what indexes are available for table_name (or \d in psql). On Sat, 2004-06-26 at 16:29, Phil Endecott wrote: 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. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings signature.asc Description: This is a digitally signed message part
Re: [SQL] Need indexes on inherited tables?
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 primary key constraint that I define in a base table is not inherited. Are any constraints inherited? What happens if I declare 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 there is a more serious problem: how can I ensure that these keys are unique across all of the derived tables? (i.e. if T1 and 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 has any value of (id,xx).) Is this a possible area for future enhancements? Regards, --Phil. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Question about a CIDR based query
I did a vacuum analyze before I run the following explain June_03=# explain select * from tmp where route >>='62.1.1.0/24'; QUERY PLAN Seq Scan on tmp (cost=0.00..606.60 rows=14544 width=33) Filter: (route >>= '62.1.1.0/24'::cidr) (2 rows) The select returns just one route, Thanks [EMAIL PROTECTED] (Josh Berkus) wrote in message news:<[EMAIL PROTECTED]>... > Georgos, > > > select * from tmp where route >>= some_cidr > > Can you post an EXPLAIN ANALYZE for this? And when's the last time you ran > ANALYZE on the table? > > > The index on route is not used and I get a sequential scan. The index is > > used only for the <<= operator. > > Most likely Postgres thinks that the >>= query is returning 60% of your table, > which makes indexes useless. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Need indexes on inherited tables?
> 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
