Subject: Re: Need indexes on inherited tables?
From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
To: Phil Endecott <spam_from_postgresql_sql@chezphil.org>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <1902614@chezphil.org>
References: <1902614@chezphil.org>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-zJSmuVO4lsYeefuhcEhw"
Organization: AK y Asociados S.R.L.
Message-Id: <1088283980.1215.19.camel@taz.oficina>
Mime-Version: 1.0
Date: Sat, 26 Jun 2004 18:06:20 -0300

--=-zJSmuVO4lsYeefuhcEhw
Content-Type: multipart/alternative; boundary="=-BZqJwdGRK3j67pZeSO6D"

--=-BZqJwdGRK3j67pZeSO6D
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

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:=20
->SET enable_seqscan TO off;
->EXPLAIN SELECT * FROM B WHERE id=3D5;=20
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=3DC.oid) LEFT JOIN pg_class C2 ON
(C2.oid=3DI.indexrelid) WHERE  C.relname ILIKE '<table_name>'

you can find out what indexes are available for table_name (or \d
<table_name> in psql).


On Sat, 2004-06-26 at 16:29, Phil Endecott wrote:

> Dear Postgresql experts,
>=20
> I have a base table that declares a primary key spanning a couple of colu=
mns:
>=20
> create table B (
>   id integer,
>   xx someothertype,
>   .....
>   primary key (id, xx)
> );
>=20
> and a number of derived tables that inherit from B:
>=20
> create table T (
>  ....
> ) inherits (B);
>=20
> An index is automatically created for B because of the primary key.
>=20
> If I search for something in T using the key columns, e.g. I do
>=20
>   select * from T where id=3D1 and xx=3Dsomething;
>=20
> 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?
>=20
> Is it any different if I search in B and find rows that are actually in T?
>=20
> (Slightly unrelated: does the index on (id,xx) help when I am searching o=
nly on id?)
>=20
> Thanks for any insight anyone can offer.
>=20
> --Phil.
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>=20

--=-BZqJwdGRK3j67pZeSO6D
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; CHARSET=3DUTF-8">
  <META NAME=3D"GENERATOR" CONTENT=3D"GtkHTML/3.0.10">
</HEAD>
<BODY>
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 s=
equential scan on table T (unless of course you add a primary key constrain=
t or an index on table T).<BR>
<BR>
You can check this things doing: <BR>
-&gt;SET enable_seqscan TO off;<BR>
-&gt;EXPLAIN SELECT * FROM B WHERE id=3D5; <BR>
you'll see an index scan on table B and sequential scans on the other table=
s.<BR>
<BR>
Doing:<BR>
-&gt;SELECT C.relname AS table_name, C2.relname AS index_name FROM pg_index=
 I LEFT JOIN pg_class C ON (I.indrelid=3DC.oid) LEFT JOIN pg_class C2 ON (C=
2.oid=3DI.indexrelid) WHERE&nbsp; C.relname ILIKE '&lt;table_name&gt;'<BR>
<BR>
you can find out what indexes are available for table_name (or \d &lt;table=
_name&gt; in psql).<BR>
<BR>
<BR>
On Sat, 2004-06-26 at 16:29, Phil Endecott wrote:
<BLOCKQUOTE TYPE=3DCITE>
<PRE><FONT COLOR=3D"#737373"><I>Dear Postgresql experts,

I have a base table that declares a primary key spanning a couple of column=
s:

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=3D1 and xx=3Dsomething;

will the index be used?  Or must I explicity create an index on id and xx f=
or 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 onl=
y 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>
</BODY>
</HTML>

--=-BZqJwdGRK3j67pZeSO6D--

--=-zJSmuVO4lsYeefuhcEhw
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (FreeBSD)

iD8DBQBA3eVM21dVnhLsBV0RAn2GAJwIlSQtyRrRfemGq4fibwcbDoHvaACfZsSh
I3+B/McBtkM2u2Y+RPJZ7fg=
=4hdX
-----END PGP SIGNATURE-----

--=-zJSmuVO4lsYeefuhcEhw--