Thread

1. index creation order?

   Allen Landsidel <all@biosys.net> — 2003-10-31T16:02:24Z

   Yet another question.. thanks to everyone responding to all these so far.. ;)
   
   This one is basically.. given I have a big table already in COPY format, 
   about 28 million rows, all keys guaranteed to be unique, I'm trying to find 
   out which of the following will get the import finished the fastest:
   
   a) CREATE TABLE with no indexes or keys.  Run the COPY (fast, ~30min), then 
   CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and 
   each fk needed.
   
   b) Same as above, but instead of ALTER TABLE -- ditch the FK, and CREATE 
   UNIQUE INDEX on the PK.
   
   c) CREATE TABLE with the PK/FK's in the table structure, CREATE INDEX on 
   needed columns, then run the COPY.
   
   d) .. is to c as b is to a .. Don't create PK/FK's, just CREATE UNIQUE 
   INDEX after table creation, then run the COPY.
   
   My gut instinct tells me that in order, fastest to slowest, it's going to 
   be d,b,c,a; this is what I've experienced on other DBs such as MSSQL and 
   Oracle.
   
   If there isn't a significant difference between all of them, performance 
   wise, I think something is dreadfully wrong here.  Running "a", the ALTER 
   TABLE to add the PK ran for 17 hours and still wasn't finished.
   
   The table without indexes or keys is:
   CREATE TABLE foo (
   id BIGINT NOT NULL DEFAULT nextval('foo_id_sequence'),
   master_id BIGINT NOT NULL,
   other_id INTEGER NOT NULL,
   status INTEGER NOT NULL,
   addtime TIMESTAMP WITH TIME ZONE DEFAULT now()
   );
   
   Details on machine and configuration are:
   
   The machine is the same one I've mentioned before.. SMP AthlonMP 2800+ 
   (2.1GHz), 4x18GB 15krpm SCSI RAID-0 with 256MB onboard cache on a 
   quad-channel ICP-Vortex controller, 2GB physical memory.  Running FreeBSD 
   RELENG_4, relevant filesystems with softupdates enabled and mounted noatime.
   
   kernel options are:
   maxusers        0
   
   options         MAXDSIZ="(1536UL*1024*1024)" # maximum limit
   options         MAXSSIZ="(512UL*1024*1024)"  # maximum stack
   options         DFLDSIZ="(512UL*1024*1024)"  # default limit
   options         VM_BCACHE_SIZE_MAX="(384UL*1024*1024)" # cache size upped 
   from default 200MB
   options         SYSVSHM                 #SYSV-style shared memory
   options         SYSVMSG                 #SYSV-style message queues
   options         SYSVSEM                 #SYSV-style semaphores
   options         SHMMAXPGS=262144
   options         SHMALL=262144
   options         SHMSEG=256
   options         SEMMNI=384
   options         SEMMNS=768
   options         SEMMNU=384
   options         SEMMAP=384
   
   postgresql.conf settings are:
   
   shared_buffers = 30000
   max_fsm_relations = 10000
   max_fsm_pages = 2000000
   max_locks_per_transaction = 64
   wal_buffers = 128
   sort_mem = 1310720 (1.2GB)
   vacuum_mem = 262144 (256MB)
   checkpoint_segments = 64
   checkpoint_timeout = 1200
   commit_delay = 20000
   commit_siblings = 2
   fsync=true
   random_page_cost = 1.7
   cpu_tuple_cost = 0.005
   cpu_index_tuple_cost = 0.005
   cpu_operator_cost = 0.0012
   
   stats_start_collector = true
   stats_command_string = true
   stats_row_level = true
   stats_block_level = true
   
   
   

2. Re: index creation order?

   Rod Taylor <rbt@rbt.ca> — 2003-10-31T16:23:30Z

   If it is 7.4 beta 5 or later, I would definitely go with A.
   
   Adding indexes after the fact seems to be much quicker. Foreign keys use
   the same algorithm prior to beta 5 regardless of timing. 
   
   A primary key and unique index will have approx the same performance (a
   check for NULL isn't very costly).
   
   On Fri, 2003-10-31 at 11:02, Allen Landsidel wrote:
   > Yet another question.. thanks to everyone responding to all these so far.. ;)
   > 
   > This one is basically.. given I have a big table already in COPY format, 
   > about 28 million rows, all keys guaranteed to be unique, I'm trying to find 
   > out which of the following will get the import finished the fastest:
   > 
   > a) CREATE TABLE with no indexes or keys.  Run the COPY (fast, ~30min), then 
   > CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and 
   > each fk needed.
   > 
   > b) Same as above, but instead of ALTER TABLE -- ditch the FK, and CREATE 
   > UNIQUE INDEX on the PK.
   > 
   > c) CREATE TABLE with the PK/FK's in the table structure, CREATE INDEX on 
   > needed columns, then run the COPY.
   > 
   > d) .. is to c as b is to a .. Don't create PK/FK's, just CREATE UNIQUE 
   > INDEX after table creation, then run the COPY.
   > 
   > My gut instinct tells me that in order, fastest to slowest, it's going to 
   > be d,b,c,a; this is what I've experienced on other DBs such as MSSQL and 
   > Oracle.
   > 
   > If there isn't a significant difference between all of them, performance 
   > wise, I think something is dreadfully wrong here.  Running "a", the ALTER 
   > TABLE to add the PK ran for 17 hours and still wasn't finished.
   > 
   > The table without indexes or keys is:
   > CREATE TABLE foo (
   > id BIGINT NOT NULL DEFAULT nextval('foo_id_sequence'),
   > master_id BIGINT NOT NULL,
   > other_id INTEGER NOT NULL,
   > status INTEGER NOT NULL,
   > addtime TIMESTAMP WITH TIME ZONE DEFAULT now()
   > );
   > 
   > Details on machine and configuration are:
   > 
   > The machine is the same one I've mentioned before.. SMP AthlonMP 2800+ 
   > (2.1GHz), 4x18GB 15krpm SCSI RAID-0 with 256MB onboard cache on a 
   > quad-channel ICP-Vortex controller, 2GB physical memory.  Running FreeBSD 
   > RELENG_4, relevant filesystems with softupdates enabled and mounted noatime.
   > 
   > kernel options are:
   > maxusers        0
   > 
   > options         MAXDSIZ="(1536UL*1024*1024)" # maximum limit
   > options         MAXSSIZ="(512UL*1024*1024)"  # maximum stack
   > options         DFLDSIZ="(512UL*1024*1024)"  # default limit
   > options         VM_BCACHE_SIZE_MAX="(384UL*1024*1024)" # cache size upped 
   > from default 200MB
   > options         SYSVSHM                 #SYSV-style shared memory
   > options         SYSVMSG                 #SYSV-style message queues
   > options         SYSVSEM                 #SYSV-style semaphores
   > options         SHMMAXPGS=262144
   > options         SHMALL=262144
   > options         SHMSEG=256
   > options         SEMMNI=384
   > options         SEMMNS=768
   > options         SEMMNU=384
   > options         SEMMAP=384
   > 
   > postgresql.conf settings are:
   > 
   > shared_buffers = 30000
   > max_fsm_relations = 10000
   > max_fsm_pages = 2000000
   > max_locks_per_transaction = 64
   > wal_buffers = 128
   > sort_mem = 1310720 (1.2GB)
   > vacuum_mem = 262144 (256MB)
   > checkpoint_segments = 64
   > checkpoint_timeout = 1200
   > commit_delay = 20000
   > commit_siblings = 2
   > fsync=true
   > random_page_cost = 1.7
   > cpu_tuple_cost = 0.005
   > cpu_index_tuple_cost = 0.005
   > cpu_operator_cost = 0.0012
   > 
   > stats_start_collector = true
   > stats_command_string = true
   > stats_row_level = true
   > stats_block_level = true
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 2: you can get off all lists at once with the unregister command
   >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
   > 
   

3. Re: index creation order?

   Josh Berkus <josh@agliodbs.com> — 2003-10-31T17:10:30Z

   Allen,
   
   > a) CREATE TABLE with no indexes or keys.  Run the COPY (fast, ~30min), then
   > CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and
   > each fk needed.
   
   Did you ANALYZE after the copy?
   
   > If there isn't a significant difference between all of them, performance
   > wise, I think something is dreadfully wrong here.  Running "a", the ALTER
   > TABLE to add the PK ran for 17 hours and still wasn't finished.
   
   Adding the *primary key* locked up?   This seems unlikely; we have a known 
   problem with *foreign* keys until the current beta.  But I've added primary 
   keys on 20Gb tables and had it complete in a couple of hours.  Ignore this 
   adivice and look for Stephan Szabo's FK patch instead if what you really 
   meant was that the FK creation locked up.
   
   > shared_buffers = 30000
   hmmm ... 236MB ....
   > max_fsm_pages = 2000000
   2MB, fine ...
   > wal_buffers = 128
   1MB, also fine ...
   > sort_mem = 1310720 (1.2GB)
   Problem here.   As documented everywhere, sort_mem is allocated *per sort* not 
   per query, user, or shared.   This means that if the "add PK" operation 
   involves 2 or more sorts (not sure, haven't tested it), then you're 
   allocating .7GB RAM more than you acutally have.  This may be the cause of 
   your problem, particularly if *anything* is going on concurrent to the load.
   
   > checkpoint_segments = 64
   IF you have the disk space (+ 2GB) I'd raise this to 150-300 during the load 
   operation.
   
   > commit_delay = 20000
   > commit_siblings = 2
   These settings are for heavy multi-user update activity. They are not useful 
   for a single-user load, and may even lower performance.
   
   > stats_start_collector = true
   > stats_command_string = true
   > stats_row_level = true
   > stats_block_level = true
   
   If you can do without stats collection during load, I would suggest that you 
   do so.  The above add both RAM and I/O overhead to your operation.
   
   -- 
   Josh Berkus
   Aglio Database Solutions
   San Francisco
   
   

4. Re: index creation order?

   Allen Landsidel <all@biosys.net> — 2003-10-31T18:27:12Z

   At 12:10 10/31/2003, Josh Berkus wrote:
   >Allen,
   >
   > > a) CREATE TABLE with no indexes or keys.  Run the COPY (fast, ~30min), then
   > > CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and
   > > each fk needed.
   >
   >Did you ANALYZE after the copy?
   
   No, and this was my major mistake.  I normally run analyze periodically 
   from cron, anywhere from once an hour to ever 15 minutes depending on the 
   db.. I had disabled that for this because I didn't want anything competing 
   with this stuff for disk I/O.
   
   I followed your other suggestions as well, canceled the index that was 
   running, analyzed the whole db, and ran the queries again.  All of them are 
   running in under 10 or so minutes after the analyze.
   
   I'll just be adding the PKs and the Indexes, I can add triggers/rules of my 
   own for the RI, rather than worry about FK creation screwing up.
   
   I had no idea analyze was playing such a big role in this sense.. I really 
   thought that other than saving space, it wasn't doing much for tables that 
   don't have indexes on the.
   
   Thanks for the help.
   
   > > shared_buffers = 30000
   >hmmm ... 236MB ....
   > > max_fsm_pages = 2000000
   >2MB, fine ...
   > > wal_buffers = 128
   >1MB, also fine ...
   > > sort_mem = 1310720 (1.2GB)
   >Problem here.   As documented everywhere, sort_mem is allocated *per sort* 
   >not
   >per query, user, or shared.   This means that if the "add PK" operation
   >involves 2 or more sorts (not sure, haven't tested it), then you're
   >allocating .7GB RAM more than you acutally have.  This may be the cause of
   >your problem, particularly if *anything* is going on concurrent to the load.
   
   I didn't know this was per-sort per-backend, I thought it was per-backend 
   for all sorts running on that backend.  I've dropped it down to 256MB.
   
   > > checkpoint_segments = 64
   >IF you have the disk space (+ 2GB) I'd raise this to 150-300 during the load
   >operation.
   
   Done, at 128, which seems to be enough for now.  I'll fiddle more with this 
   later on.
   
   > > commit_delay = 20000
   > > commit_siblings = 2
   >These settings are for heavy multi-user update activity. They are not useful
   >for a single-user load, and may even lower performance.
   
   That's what's going on.. this database I'm working on isn't the only one in 
   the system, and some things are using different schemas in the database I'm 
   working on, so this isn't something I can afford to turn off.  Most of the 
   activity is heavy and transient.. many INSERT/UPDATE/DELETE cycles.
   
   Again, thanks for the help, I really do appreciate it.  It's gratifying and 
   depressing to know the last two or so days work could've been compressed 
   into 3 hours if I'd just run that damn analyze. ;)
   
   
   
   
   

5. Re: index creation order?

   Allen Landsidel <all@biosys.net> — 2003-10-31T18:28:44Z

   Nope, still 7.3.4 here.. I am very excited about 7.4 though.. almost as 
   excited as I am about FreeBSD 5.x going -STABLE..  it's a close race 
   between the two..
   
   I'll keep this in mind for when I update though, thanks.
   
   At 11:23 10/31/2003, Rod Taylor wrote:
   >If it is 7.4 beta 5 or later, I would definitely go with A.
   >
   >Adding indexes after the fact seems to be much quicker. Foreign keys use
   >the same algorithm prior to beta 5 regardless of timing.
   >
   >A primary key and unique index will have approx the same performance (a
   >check for NULL isn't very costly).
   >
   >On Fri, 2003-10-31 at 11:02, Allen Landsidel wrote:
   > > Yet another question.. thanks to everyone responding to all these so 
   > far.. ;)
   > >
   > > This one is basically.. given I have a big table already in COPY format,
   > > about 28 million rows, all keys guaranteed to be unique, I'm trying to 
   > find
   > > out which of the following will get the import finished the fastest:
   > >
   > > a) CREATE TABLE with no indexes or keys.  Run the COPY (fast, ~30min), 
   > then
   > > CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and
   > > each fk needed.
   > >
   > > b) Same as above, but instead of ALTER TABLE -- ditch the FK, and CREATE
   > > UNIQUE INDEX on the PK.
   > >
   > > c) CREATE TABLE with the PK/FK's in the table structure, CREATE INDEX on
   > > needed columns, then run the COPY.
   > >
   > > d) .. is to c as b is to a .. Don't create PK/FK's, just CREATE UNIQUE
   > > INDEX after table creation, then run the COPY.
   > >
   > > My gut instinct tells me that in order, fastest to slowest, it's going to
   > > be d,b,c,a; this is what I've experienced on other DBs such as MSSQL and
   > > Oracle.
   > >
   > > If there isn't a significant difference between all of them, performance
   > > wise, I think something is dreadfully wrong here.  Running "a", the ALTER
   > > TABLE to add the PK ran for 17 hours and still wasn't finished.
   > >
   > > The table without indexes or keys is:
   > > CREATE TABLE foo (
   > > id BIGINT NOT NULL DEFAULT nextval('foo_id_sequence'),
   > > master_id BIGINT NOT NULL,
   > > other_id INTEGER NOT NULL,
   > > status INTEGER NOT NULL,
   > > addtime TIMESTAMP WITH TIME ZONE DEFAULT now()
   > > );
   > >
   > > Details on machine and configuration are:
   > >
   > > The machine is the same one I've mentioned before.. SMP AthlonMP 2800+
   > > (2.1GHz), 4x18GB 15krpm SCSI RAID-0 with 256MB onboard cache on a
   > > quad-channel ICP-Vortex controller, 2GB physical memory.  Running FreeBSD
   > > RELENG_4, relevant filesystems with softupdates enabled and mounted 
   > noatime.
   > >
   > > kernel options are:
   > > maxusers        0
   > >
   > > options         MAXDSIZ="(1536UL*1024*1024)" # maximum limit
   > > options         MAXSSIZ="(512UL*1024*1024)"  # maximum stack
   > > options         DFLDSIZ="(512UL*1024*1024)"  # default limit
   > > options         VM_BCACHE_SIZE_MAX="(384UL*1024*1024)" # cache size upped
   > > from default 200MB
   > > options         SYSVSHM                 #SYSV-style shared memory
   > > options         SYSVMSG                 #SYSV-style message queues
   > > options         SYSVSEM                 #SYSV-style semaphores
   > > options         SHMMAXPGS=262144
   > > options         SHMALL=262144
   > > options         SHMSEG=256
   > > options         SEMMNI=384
   > > options         SEMMNS=768
   > > options         SEMMNU=384
   > > options         SEMMAP=384
   > >
   > > postgresql.conf settings are:
   > >
   > > shared_buffers = 30000
   > > max_fsm_relations = 10000
   > > max_fsm_pages = 2000000
   > > max_locks_per_transaction = 64
   > > wal_buffers = 128
   > > sort_mem = 1310720 (1.2GB)
   > > vacuum_mem = 262144 (256MB)
   > > checkpoint_segments = 64
   > > checkpoint_timeout = 1200
   > > commit_delay = 20000
   > > commit_siblings = 2
   > > fsync=true
   > > random_page_cost = 1.7
   > > cpu_tuple_cost = 0.005
   > > cpu_index_tuple_cost = 0.005
   > > cpu_operator_cost = 0.0012
   > >
   > > stats_start_collector = true
   > > stats_command_string = true
   > > stats_row_level = true
   > > stats_block_level = true
   > >
   > >
   > > ---------------------------(end of broadcast)---------------------------
   > > TIP 2: you can get off all lists at once with the unregister command
   > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
   > >
   
   
   

6. Re: index creation order?

   Neil Conway <neilc@samurai.com> — 2003-10-31T18:40:22Z

   On Fri, 2003-10-31 at 13:27, Allen Landsidel wrote:
   > I had no idea analyze was playing such a big role in this sense.. I really 
   > thought that other than saving space, it wasn't doing much for tables that 
   > don't have indexes on the.
   
   ANALYZE doesn't save any space at all -- VACUUM is probably what you're
   thinking of.
   
   -Neil
   
   
   
   

7. Re: index creation order?

   Josh Berkus <josh@agliodbs.com> — 2003-10-31T18:58:19Z

   Allen,
   
   > I had no idea analyze was playing such a big role in this sense.. I really
   > thought that other than saving space, it wasn't doing much for tables that
   > don't have indexes on the.
   
   Among other things, ANALYZE tells postgres how many rows are in the table.  So 
   if you add a PK constraint after loading 10 million rows without ANALYZE, 
   PostgreSQL is likely to think that there is only one row in the table ... and 
   choose a nested loop or some other really inefficient method of checking for 
   uniqueness.
   
   -- 
   Josh Berkus
   Aglio Database Solutions
   San Francisco
   
   

8. Re: index creation order?

   Allen Landsidel <all@biosys.net> — 2003-10-31T19:01:28Z

   At 13:40 10/31/2003, Neil Conway wrote:
   >On Fri, 2003-10-31 at 13:27, Allen Landsidel wrote:
   > > I had no idea analyze was playing such a big role in this sense.. I really
   > > thought that other than saving space, it wasn't doing much for tables that
   > > don't have indexes on the.
   >
   >ANALYZE doesn't save any space at all -- VACUUM is probably what you're
   >thinking of.
   
   Actually, I was thinking VACUUM ANALYZE.. which is what I ran after the 
   COPY.. sorry for my lack of precision.
   
   I've yet to run straight-up ANALYZE AFAIK.
   
   -Allen
   
   
   

9. Re: index creation order?

   Chester Kustarz <chester@arbor.net> — 2003-10-31T20:32:30Z

   is there any way to update the stats inside a transaction? what i have is
   something like:
   
   select count(*) from foo;
   -> 0
   
   begin;
   
   copy foo from '/tmp/foo'; -- about 100k rows
   
   -- run some queries on foo which perform horribly because the stats
   -- are way off (100k rows v. 0 rows)
   
   commit;
   
   
   it seems that you cannot run analyze inside a transaction:
   
   begin;
   analyze foo;
   ERROR:  ANALYZE cannot run inside a BEGIN/END block
   
   i am using version 7.2.3.
   
   any work-a-rounds? should i try updating pg_statistic manually?
   
   On Fri, 31 Oct 2003, Josh Berkus wrote:
   > Among other things, ANALYZE tells postgres how many rows are in the table.  So
   > if you add a PK constraint after loading 10 million rows without ANALYZE,
   > PostgreSQL is likely to think that there is only one row in the table ... and
   > choose a nested loop or some other really inefficient method of checking for
   > uniqueness.
   
   
   

10. Re: index creation order?

    Rod Taylor <rbt@rbt.ca> — 2003-10-31T20:57:38Z

    > begin;
    > analyze foo;
    > ERROR:  ANALYZE cannot run inside a BEGIN/END block
    > 
    > i am using version 7.2.3.
    
    Time to upgrade. 7.3 / 7.4 allows this to happen.
    

11. Re: index creation order?

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-10-31T21:33:32Z

    Chester Kustarz <chester@arbor.net> writes:
    > it seems that you cannot run analyze inside a transaction:
    
    You can in 7.3.* ...
    
    			regards, tom lane