Is it necessary to have index for child table in following case?

On 3 Feb 2010, at 3:58, Yan Cheng Cheok wrote:

Due to the fact

"A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. Thus, in the terms of the above example: "

When I use table inheritance to implement table partition :

measurement {
primary_key
foreign_key1
foreign_key2

create foreign_key1_index
create foreign_key2_index
}

measurement_1 inherit from measurement {
primary_key
foreign_key1
foreign_key2

create foreign_key1_index
create foreign_key2_index
}

However, I realize having foreign_key1_index and foreign_key2_index for child table measurement_1, make up my data insert speed slow down by factor of 2~3

You probably want to check the output of EXPLAIN ANALYZE for your INSERT queries. That should point to what's slowing things down and can often tell why it does so. If it's not obvious, post the output here ;)

Are you using INSERT or COPY to insert your data? COPY tends to be a lot faster than separate INSERTs, especially if you don't wrap the INSERTs in a transaction block and COMMIT them in batches.

I was wondering whether is it necessary for me to create index foreign_key1_index for child table measurement_1?

(1) All my SELECT query is performed on parent table measurement.
(2) All my data INSERT code is performed based on child table measurement_1.

That depends on whether your SELECT query actually uses those indexes. Again, you can see that by using EXPLAIN on your SELECT query. I think you'll see that the indexes on the child tables do get used, but the ones on the parent table probably don't.

If you never insert any data into your parent table (or if it never contains more than a few rows if you do) then there's no point in having an index on it. As you already quoted, indexes can't span multiple tables. The only data in the index on the parent table is about the records that live there, which probably are none at all.

As an aside, I hope you do realise that your primary key isn't guaranteed to be unique across your child tables? The reason is the same one that you already quoted for indexes spanning multiple tables - a primary key is implemented using a unique index after all.

If that matters, what you can do is make your primary key a foreign key to a new table with just the primary key column in it. Make sure you always insert a record in the primary key table along with the one referencing it, so that you will get a unique violation when you try to insert a record for which the primary key already exists. This will of course slow things down some, but if it's necessary that's the price to pay.

If your primary key is a serial (meaning it's generated by a sequence) you probably don't need to worry about it, serials generate unique numbers (unless they wrap around when they run out of numbers, but you control whether they're allowed to and you can design them large enough that it won't happen).

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b6966ba10441687344184!

Are you using INSERT or COPY to insert your data? COPY
tends to be a lot faster than separate INSERTs, especially
if you don't wrap the INSERTs in a transaction block and
COMMIT them in batches.

But I do not use File or Stdio. The data is coming in row by row real-time. I need to insert the data programmatic real-time into the database. That's why I use INSERT. But maybe I miss out something on the usage of COPY, please advice :)

As an aside, I hope you do realise that your primary key
isn't guaranteed to be unique across your child tables? The
reason is the same one that you already quoted for indexes
spanning multiple tables - a primary key is implemented
using a unique index after all.

If that matters, what you can do is make your primary key a
foreign key to a new table with just the primary key column
in it. Make sure you always insert a record in the primary
key table along with the one referencing it, so that you
will get a unique violation when you try to insert a record
for which the primary key already exists. This will of
course slow things down some, but if it's necessary that's
the price to pay.

Oh. I didn't notice that. Thanks for pointing out. Luckily, thanks God. It doesn't matter much at this moment :)

On 02/03/2010 05:02 PM, Yan Cheng Cheok wrote:

Are you using INSERT or COPY to insert your data? COPY tends to be
a lot faster than separate INSERTs, especially if you don't wrap
the INSERTs in a transaction block and COMMIT them in batches.

But I do not use File or Stdio. The data is coming in row by row
real-time. I need to insert the data programmatic real-time into the
database. That's why I use INSERT. But maybe I miss out something on
the usage of COPY, please advice :)

I already answered that about a month ago but you ignored it:
http://archives.postgresql.org/pgsql-general/2010-01/msg00287.php

Joe

Sorry. I do read your previous post.However, I ignore and soon forget quite a while, as I do not how to use it with the column contains serial

PQexec(Database::instance().getConnection(), "copy unit_1 from stdin");
// | serial | int | int
/* But I just do not want to put 9999 as serial. I want it to be auto-increment. However, I have no idea how to make serial auto-increment, without using INSERT.
*/
PQputline(Database::instance().getConnection(),"9999\t1\t888\n");
PQputline(Database::instance().getConnection(),"\\.\n");
PQendcopy(Database::instance().getConnection());

Thanks and Regards
Yan Cheng CHEOK

--- On Thu, 2/4/10, Joe Conway <mail@joeconway.com> wrote:

On 02/03/2010 06:59 PM, Yan Cheng Cheok wrote:

PQexec(Database::instance().getConnection(), "copy unit_1 from
stdin"); // | serial | int | int /* But I just do not want to put
9999 as serial. I want it to be auto-increment. However, I have no
idea how to make serial auto-increment, without using INSERT. */
PQputline(Database::instance().getConnection(),"9999\t1\t888\n");
PQputline(Database::instance().getConnection(),"\\.\n");
PQendcopy(Database::instance().getConnection());

You really need to get up close and personal with the fine manual.

See:
-----------
http://developer.postgresql.org/pgdocs/postgres/sql-copy.html

Specifically:
-----------
Synopsis

COPY table_name [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ] ( option [, ...] ) ]

Example:
-----------
regression=# create table foo(f1 serial, f2 text);
NOTICE: CREATE TABLE will create implicit sequence "foo_f1_seq" for
serial column "foo.f1"
CREATE TABLE
regression=# copy foo (f2) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

hello
world
\.

regression=# select * from foo;
f1 | f2
----+-------
1 | hello
2 | world
(2 rows)

HTH,

Joe