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

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

From
Yan Cheng Cheok
Date:
Due to the fact

"A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key
constraintsonly apply to single tables, not to their inheritance children. This is true on both the referencing and
referencedsides 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
insertspeed slow down by factor of 2~3 

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.

Thanks!

Thanks and Regards
Yan Cheng CHEOK





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

From
Alban Hertroys
Date:
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
constraintsonly apply to single tables, not to their inheritance children. This is true on both the referencing and
referencedsides 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
insertspeed 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
thingsdown 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
youdon'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
SELECTquery. I think you'll see that the indexes on the child tables do get used, but the ones on the parent table
probablydon'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'sno point in having an index on it. As you already quoted, indexes can't span multiple tables. The only data in
theindex 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
reasonis the same one that you already quoted for indexes spanning multiple tables - a primary key is implemented using
aunique 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
init. Make sure you always insert a record in the primary key table along with the one referencing it, so that you will
geta unique violation when you try to insert a record for which the primary key already exists. This will of course
slowthings 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,
serialsgenerate unique numbers (unless they wrap around when they run out of numbers, but you control whether they're
allowedto 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!



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

From
Yan Cheng Cheok
Date:
>
> 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-timeinto 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 :)





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

From
Joe Conway
Date:
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


Attachment

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

From
Yan Cheng Cheok
Date:
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
columncontains 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
makeserial 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:

> From: Joe Conway <mail@joeconway.com>
> Subject: Re: [GENERAL] Is it necessary to have index for child table in following case?
> To: "Yan Cheng Cheok" <yccheok@yahoo.com>
> Cc: "Alban Hertroys" <dalroi@solfertje.student.utwente.nl>, pgsql-general@postgresql.org
> Date: Thursday, February 4, 2010, 9:08 AM
> 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
>
>





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

From
Joe Conway
Date:
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



Attachment