Skip site navigation (1) Skip section navigation (2)

Re: Which indexes to drop

From: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
To: PgSQL Novice ML <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Which indexes to drop
Date: 2002-06-18 22:51:20
Message-ID: 1024440681.11520.48.camel@rebel (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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(dot)l(dot)johnson(at)cox(dot)net         |
| Jefferson, LA  USA     |
|                                                             |
| "Object-oriented programming is an exceptionally bad idea   |
|  which could only have originated in California."           |
|  --Edsger Dijkstra                                          |

In response to


pgsql-novice by date

Next:From: Wei WangDate: 2002-06-19 02:15:57
Subject: in_array: Need to specify dimensions
Previous:From: IndiraDate: 2002-06-18 19:11:03
Subject: Re: unsubscribe NOVICE

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group