Thread: Which indexes to drop
Hi, I'm optimising my data load by dropping indexes. My question is, what do I do with the indexes created by postgres for primary keys ? Is it OK to drop them ? What will happen to the constraint checking ? How do I create them again afterwards, so that they are used correctly for constraint checking ? Say I have a table: create table transmission ( id serial not null, start timestamp not null default CURRENT_TIMESTAMP, stop timestamp, complete boolean not null default false, primary key(id,complete) ) This will have an index: transmission_pkey After data loading can I create it again as follows ? CREATE INDEX transmission_pkey ON transmission(id,complete) Thanks JohnT
John, > I'm optimising my data load by dropping indexes. > > My question is, what do I do with the indexes created by postgres for > primary keys ? > > Is it OK to drop them ? No. > What will happen to the constraint checking ? It won't happen, assuming that Postgres allows the DROP INDEX statement at all. > How do I create them again afterwards, so that they are used > correctly for constraint checking ? I'm not sure that it's possible to add a primary key to an existing populated table. I'd reccommend instead dropping all other indexes but the primary key. It's just not a good idea. How much is this slowing down the data load? Can you do a comparison test on a keyed vs. keyless table? FYI, in 7.4 or 8.0 we will have DEFERRABLE UNIQUE constraints, which means that it may be possible for you to hold the PK checking until the data load is finished, adding some speed to the process. -Josh Berkus
On Tue, 2002-06-18 at 10:36, Josh Berkus wrote:
> John,
>
> > I'm optimising my data load by dropping indexes.
> >
> > My question is, what do I do with the indexes created by postgres for
> > primary keys ?
> >
> > Is it OK to drop them ?
>
> No.
I've done it many times...
alter table t_lane_tx
add constraint c_lane_tx_pk primary key (lane_tx_id);
> > How do I create them again afterwards, so that they are used
> > correctly for constraint checking ?
In this instance, you can have speed or you can have correctness.
Pick one. Of course, if you know that your input data is good,
you have both...
> I'm not sure that it's possible to add a primary key to an existing
> populated table.
See above.
> I'd reccommend instead dropping all other indexes but the primary key.
> It's just not a good idea. How much is this slowing down the data
> load? Can you do a comparison test on a keyed vs. keyless table?
Ack! Have you tried loading millions of rows to an indexed table?
Imagine, if you will, a 1MHz 8080 writing to a floppy drive...
> FYI, in 7.4 or 8.0 we will have DEFERRABLE UNIQUE constraints, which
> means that it may be possible for you to hold the PK checking until the
> data load is finished, adding some speed to the process.
Still, I bet that creating an (implicit or explicit) index would
be faster if you are loading the data in bite-sized chunks.
--
+-------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron.l.johnson@cox.net |
| Jefferson, LA USA http://ronandheather.dhs.org:81 |
| |
| "Object-oriented programming is an exceptionally bad idea |
| which could only have originated in California." |
| --Edsger Dijkstra |
+-------------------------------------------------------------+
On Tuesday 18 June 2002 23:51, Ron Johnson wrote: > On Tue, 2002-06-18 at 10:36, Josh Berkus wrote: > > John, > > > > > I'm optimising my data load by dropping indexes. > > > > > > My question is, what do I do with the indexes created by postgres for > > > primary keys ? > > > > > > Is it OK to drop them ? > > > > No. > > I've done it many times... > > alter table t_lane_tx > add constraint c_lane_tx_pk primary key (lane_tx_id); > > > > How do I create them again afterwards, so that they are used > > > correctly for constraint checking ? > > In this instance, you can have speed or you can have correctness. > Pick one. Of course, if you know that your input data is good, > you have both... > I think I'm going to assume that my data is good, otherwise the load will take about 30 hours ! So do I need to: remove the constraint load add the constraint or drop index load add the constraint ie: Will removing the constraint automatically drop the index or Will dropping the index remove the constraint Thanks JohnT
On Wed, 2002-06-19 at 03:24, John Taylor wrote: > On Tuesday 18 June 2002 23:51, Ron Johnson wrote: > > On Tue, 2002-06-18 at 10:36, Josh Berkus wrote: > > > John, > > > > > > > I'm optimising my data load by dropping indexes. > > > > > > > > My question is, what do I do with the indexes created by postgres for > > > > primary keys ? > > > > > > > > Is it OK to drop them ? > > > > > > No. > > > > I've done it many times... > > > > alter table t_lane_tx > > add constraint c_lane_tx_pk primary key (lane_tx_id); > > > > > > How do I create them again afterwards, so that they are used > > > > correctly for constraint checking ? > > > > In this instance, you can have speed or you can have correctness. > > Pick one. Of course, if you know that your input data is good, > > you have both... > > > > I think I'm going to assume that my data is good, otherwise the load will take about 30 hours ! > > So do I need to: > remove the constraint Creating a PK constraint implicitly creates an index with the same name. So, drop the "same named" index and the PK constraint will go away... > load > add the constraint > or > drop index See above... > load > add the constraint > > > ie: > Will removing the constraint automatically drop the index > > Will dropping the index remove the constraint The docs are pretty clear about it... -- +-------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA http://ronandheather.dhs.org:81 | | | | "Object-oriented programming is an exceptionally bad idea | | which could only have originated in California." | | --Edsger Dijkstra | +-------------------------------------------------------------+
On Wednesday 19 June 2002 11:35, Ron Johnson wrote: > On Wed, 2002-06-19 at 03:24, John Taylor wrote: > > ie: > > Will removing the constraint automatically drop the index > > > > Will dropping the index remove the constraint > > The docs are pretty clear about it... > Would you like to give some hints about where in the docs I should look ? Thanks JohnT
On Wed, 2002-06-19 at 10:09, John Taylor wrote: > On Wednesday 19 June 2002 11:35, Ron Johnson wrote: > > On Wed, 2002-06-19 at 03:24, John Taylor wrote: > > > > ie: > > > Will removing the constraint automatically drop the index > > > > > > Will dropping the index remove the constraint > > > > The docs are pretty clear about it... > > > > Would you like to give some hints about where in the docs I should look ? In http://www.postgresql.org/idocs/index.php, I searched for "drop constraint": http://www.postgresql.org/idocs/index.php?sql-altertable.html Under the "Notes" section: > To remove a PRIMARY or UNIQUE constraint, drop the relevant > index using the DROP INDEX command. -- +-------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA http://ronandheather.dhs.org:81 | | | | "Object-oriented programming is an exceptionally bad idea | | which could only have originated in California." | | --Edsger Dijkstra | +-------------------------------------------------------------+