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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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 http://ronandheather.dhs.org:81 |
| |
| "Object-oriented programming is an exceptionally bad idea |
| which could only have originated in California." |
| --Edsger Dijkstra |
+-------------------------------------------------------------+
From | Date | Subject | |
---|---|---|---|
Next Message | Wei Wang | 2002-06-19 02:15:57 | in_array: Need to specify dimensions |
Previous Message | Indira | 2002-06-18 19:11:03 | Re: unsubscribe NOVICE |