Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009

    Unanswered: Slow adding large indexes

    Hi All,

    I have recently moved postgres from one server to another (a newer server) and have encounter a problem I cant figure out. I have a table (roughly 7.5GB) that gets re-created every night with fresh data, to insert the data I run the following procedure.

    Drop all indexes
    Truncate Table
    Insert Data
    Create Indexes

    The problem I have is that it takes around 15 mins to insert the data into the table which is a massive improvement from the old server but it then takes 40 mins to create two indexes one 1GB index and another that is a 2GB index this is considerably slower than on the older server.

    I have had a play around with some of the settings but with no real success, in particular the maintenance_work_mem setting. Any help on this would be greatly appreciated. The new server is a Virtual Server running RedHat Linux, Postgres 8.3 , 8GB of RAM with 4 x 2.66ghz. The current settings in postgresql.conf I have are

    shared_buffers = 768MB
    temp_buffers = 100MB
    #max_prepared_transactions = 5
    work_mem = 64MB
    maintenance_work_mem = 800MB
    #max_stack_depth = 2MB

    # - Free Space Map -

    max_fsm_pages = 2097152
    #max_fsm_relations = 1000

    # - Kernel Resource Usage -

    #max_files_per_process = 1000
    #shared_preload_libraries = ''

    # - Cost-Based Vacuum Delay -

    #vacuum_cost_delay = 0
    #vacuum_cost_page_hit = 1
    #vacuum_cost_page_miss = 10
    #vacuum_cost_page_dirty = 20
    #vacuum_cost_limit = 200

    # - Background Writer -

    #bgwriter_delay = 200ms
    #bgwriter_lru_maxpages = 100
    #bgwriter_lru_multiplier = 2.0


    kernel.shmmax = 4294967295
    kernel.shmall = 268435456

    Any help would be greatly appreciated, thanks in advance


  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    Which version of PostgreSQL are you using?

    You don't have the WAL archive mode set on, do you?

    Are you using COPY to insert the records? This is the preferred (fastest) method in this case. If you must use inserts, first prepare once, then execute N times, which will save the repeated parsing/preparation of the inserts.

    Are there ANY foreign key constraints which reference the table? They should be dropped & recreated, if so.

    Review Populating a Database
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts