What causes large INSERT to slow down and disk usage to explode?

Question

I have a table of about 3.1 million rows with the following definition and indexes:

CREATE TABLE digiroad_liikenne_elementti (
    ogc_fid serial NOT NULL,
    wkb_geometry geometry(Geometry,4258),
    tiee_tila numeric(9,0),
    vaylatyypp numeric(9,0),
    toiminnall numeric(9,0),
    eurooppati character varying(254),
    kansalline numeric(9,0),
    tyyppi numeric(9,0),
    liikennevi numeric(9,0),
    ens_talo_o numeric(9,0),
    talonumero numeric(9,0),
    ens_talo_v numeric(9,0),
    oik_puol_t character varying(254),
    tieosan_ta numeric(9,0),
    viim_talo_ numeric(9,0),
    viim_tal_1 numeric(9,0),
    vas_puol_t character varying(254),
    laut_tyypp numeric(9,0),
    lautta_lii numeric(9,0),
    inv_paalu_ numeric(19,11),
    inv_paal_1 numeric(19,11),
    liitalue_o numeric(9,0),
    ketju_oid numeric(9,0),
    tietojoukk numeric(9,0),
    ajoratanum numeric(4,0),
    viite_guid character varying(254),
    "timestamp" date,
    tiee_kunta numeric(9,0),
    toissij_ti character varying(254),
    viite_oid numeric(9,0),
    k_elem_id numeric(9,0),
    region character varying(40) DEFAULT 'REGION'::character varying,
    CONSTRAINT digiroad_liikenne_elementti_pkey PRIMARY KEY (ogc_fid)
);

CREATE INDEX digiroad_liikenne_elementti_wkb_geometry_geom_idx
  ON digiroad_liikenne_elementti USING gist (wkb_geometry);

CREATE INDEX dle_k_elem_id_idx
  ON digiroad_liikenne_elementti USING btree (k_elem_id);

CREATE INDEX dle_ogc_fid_idx
  ON digiroad_liikenne_elementti USING btree (ogc_fid);

CREATE INDEX dle_region_idx
  ON digiroad_liikenne_elementti USING btree (region COLLATE pg_catalog."default");

Another table with 8.6 million rows contains attributes for the rows of the first table, the tables can be joined with k_elem_id AND region.

CREATE TABLE digiroad_segmentti (
    ogc_fid serial NOT NULL,
    wkb_geometry geometry(Geometry,4258),
    segm_tila numeric(9,0),
    tyyppi numeric(9,0),
    loppupiste numeric(19,11),
    alkupiste numeric(19,11),
    vaikutuska numeric(9,0),
    vaikutussu numeric(9,0),
    vaikutusai character varying(254),
    tieosanume numeric(19,11),
    tienumero numeric(9,0),
    dyn_arvo numeric(9,0),
    dyn_tyyppi numeric(9,0),
    omistaja_t numeric(9,0),
    pysakki_va numeric(9,0),
    pysakki_ty numeric(9,0),
    pysakki_su numeric(9,0),
    pysakki_ka numeric(9,0),
    pysakki_yl character varying(254),
    palvelu_pa numeric(9,0),
    toissijain numeric(9,0),
    siltataitu numeric(9,0),
    rdtc_tyypp numeric(9,0),
    rdtc_alaty numeric(9,0),
    rdtc_paikk numeric(19,11),
    rdtc_luokk numeric(9,0),
    rdtc_liitt character varying(254),
    palvelu_ob numeric(9,0),
    ketju_oid numeric(9,0),
    tietojoukk numeric(9,0),
    ajoratanum numeric(4,0),
    viite_guid character varying(254),
    "timestamp" date,
    sivusiirty numeric(19,11),
    toissij_ti character varying(254),
    viite_oid numeric(9,0),
    k_elem_id numeric(9,0),
    region character varying(40) DEFAULT 'REGION'::character varying,
    CONSTRAINT digiroad_segmentti_pkey PRIMARY KEY (ogc_fid)
);

CREATE INDEX digiroad_segmentti_wkb_geometry_geom_idx
  ON digiroad_segmentti USING gist (wkb_geometry);

CREATE INDEX ds_dyn_arvo_idx
  ON digiroad_segmentti USING btree (dyn_arvo);

CREATE INDEX ds_dyn_tyyppi_idx
  ON digiroad_segmentti USING btree (dyn_tyyppi);

CREATE INDEX ds_k_elem_id_idx
  ON digiroad_segmentti USING btree (k_elem_id);

CREATE INDEX ds_ogc_fid_idx
  ON digiroad_segmentti USING btree (ogc_fid);

CREATE INDEX ds_region_idx
  ON digiroad_segmentti USING btree (region COLLATE pg_catalog."default");

CREATE INDEX ds_tyyppi_idx
  ON digiroad_segmentti USING btree (tyyppi);

I am trying to insert the rows of the first table (with some modification) into a new table:

CREATE TABLE edge_table (
    id serial NOT NULL,
    geom geometry,
    source integer,
    target integer,
    km double precision,
    kmh double precision DEFAULT 60,
    kmh_winter double precision DEFAULT 50,
    cost double precision,
    cost_winter double precision,
    reverse_cost double precision,
    reverse_cost_winter double precision,
    x1 double precision,
    y1 double precision,
    x2 double precision,
    y2 double precision,
    k_elem_id integer,
    region character varying(40),
    CONSTRAINT edge_table_pkey PRIMARY KEY (id)
);

Since running a single insert statement would take a long time and I would not be able to see if the statement is stuck or something, I have decided to do it in smaller chunks inside a loop in a function.

The function looks like this:

DROP FUNCTION IF EXISTS insert_function();
CREATE OR REPLACE FUNCTION insert_function()
    RETURNS VOID AS
    $$
DECLARE
    const_type_1 CONSTANT int := 5;
    const_type_2 CONSTANT int := 11;
    i int := 0;
    row_count int;
BEGIN

    CREATE TABLE IF NOT EXISTS edge_table (
        id serial PRIMARY KEY,
        geom geometry,
        source integer,
        target integer,
        km double precision,
        kmh double precision DEFAULT 60,
        kmh_winter double precision DEFAULT 50,
        cost double precision,
        cost_winter double precision,
        reverse_cost double precision,
        reverse_cost_winter double precision,
        x1 double precision,
        y1 double precision,
        x2 double precision,
        y2 double precision,
        k_elem_id integer,
        region varchar(40)
    );


    batch_size := 1000;
    SELECT COUNT(*) FROM digiroad_liikenne_elementti INTO row_count;

    WHILE i*batch_size < row_count LOOP

        RAISE NOTICE 'insert: % / %', i * batch_size, row_count;

        INSERT INTO edge_table (kmh, kmh_winter, k_elem_id, region)
        SELECT      CASE WHEN DS.dyn_arvo IS NULL THEN 60 ELSE DS.dyn_arvo END,
                    CASE WHEN DS.dyn_Arvo IS NULL THEN 50 ELSE DS.dyn_arvo END,
                    DR.k_elem_id,
                    DR.region
        FROM        (
                        SELECT  DLE.k_elem_id,
                                DLE.region,
                        FROM    digiroad_liikenne_elementti DLE
                        WHERE   DLE.ogc_fid >= i * batch_size
                                AND
                                DLE.ogc_fid <= i * batch_size + batch_size
                    ) AS DR
                    LEFT JOIN
                    digiroad_segmentti DS ON
                        DS.k_elem_id = DR.k_elem_id
                        AND
                        DS.region = DR.region
                        AND
                        DS.tyyppi = const_type_1
                        AND
                        DS.dyn_tyyppi = const_type_2;

        i := i + 1;
    END LOOP;
END;
$$
LANGUAGE 'plpgsql' VOLATILE STRICT;

The problem is that it starts off going through the loops quite fast, but then at some point slows down to a crawl. When it slows down, at the same time the Disk usage in my Windows 8 Task Manager rises up to 99% so I suspect this is related to the problem somehow.

Running the INSERT statement on its own with some random value of i executes very quickly, so the problem seems to only arise when running it in the loop inside a function. Here is the EXPLAIN (ANALYZE,BUFFERS) from one such single execution:

Insert on edge_table  (cost=0.86..361121.68 rows=1031 width=23) (actual time=3405.101..3405.101 rows=0 loops=1)
  Buffers: shared hit=36251 read=3660 dirtied=14
  ->  Nested Loop Left Join  (cost=0.86..361121.68 rows=1031 width=23) (actual time=61.901..3377.609 rows=986 loops=1)
        Buffers: shared hit=32279 read=3646
        ->  Index Scan using dle_ogc_fid_idx on digiroad_liikenne_elementti dle  (cost=0.43..85.12 rows=1031 width=19) (actual time=31.918..57.309 rows=986 loops=1)
              Index Cond: ((ogc_fid >= 200000) AND (ogc_fid < 201000))
              Buffers: shared hit=27 read=58
        ->  Index Scan using ds_k_elem_id_idx on digiroad_segmentti ds  (cost=0.44..350.16 rows=1 width=23) (actual time=2.861..3.337 rows=0 loops=986)
              Index Cond: (k_elem_id = dle.k_elem_id)
              Filter: ((tyyppi = 5::numeric) AND (dyn_tyyppi = 11::numeric) AND (vaikutussu = 3::numeric) AND ((region)::text = (dle.region)::text))
              Rows Removed by Filter: 73
              Buffers: shared hit=31266 read=3588
Total runtime: 3405.270 ms

My system is running PostgreSQL 9.3.5 on Windows 8 with 8Gb of RAM.

I have experimented with different batch sizes, doing the query in different ways and increasing the memory variables in Postgres configuration, but nothing seems to have really solved the issue.

Configuration variables that have been changed from their default values:

shared_buffers = 2048MB
work_mem = 64MB
effective_cache_size = 6000MB

I'd like to find out what is causing this to happen and what could be done about it.

Answer 1

When creating a new table avoid the cost of writing Write Ahead Log (WAL) completely with CREATE TABLE AS.
_{See @Kassandry's answer for an explanation how WAL figures into this.}

CREATE OR REPLACE FUNCTION insert_function()
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   const_type_1 CONSTANT int := 5;
   const_type_2 CONSTANT int := 11;
BEGIN    
   CREATE SEQUENCE edge_table_id_seq;
    
   CREATE TABLE edge_table AS
   SELECT nextval('edge_table_id_seq'::regclass)::int AS id
        , NULL::geometry         AS geom
        , NULL::integer          AS source
        , target::integer        AS target
        , NULL::float8           AS km
        , COALESCE(DS.dyn_arvo::float8, float8 '60') AS kmh
        , COALESCE(DS.dyn_Arvo::float8, float8 '50') AS kmh_winter
        , NULL::float8           AS cost
        , NULL::float8           AS cost_winter
        , NULL::float8           AS reverse_cost
        , NULL::float8           AS reverse_cost_winter
        , NULL::float8           AS x1
        , NULL::float8           AS y1
        , NULL::float8           AS x2
        , NULL::float8           AS y2
        , D.k_elem_id::integer   AS k_elem_id
        , D.region::varchar(40)  AS region
   FROM   digiroad_liikenne_elementti D
   LEFT   JOIN digiroad_segmentti DS
             ON DS.k_elem_id = D.k_elem_id
            AND DS.region = D.region
            AND DS.tyyppi = const_type_1
            AND DS.dyn_tyyppi = const_type_2;
    
   ALTER TABLE edge_table
      ADD CONSTRAINT edge_table_pkey PRIMARY KEY(id)
    , ALTER COLUMN id SET NOT NULL
    , ALTER COLUMN id SET DEFAULT nextval('edge_table_id_seq'::regclass)
    , ALTER COLUMN kmh SET DEFAULT 60
    , ALTER COLUMN kmh_winter SET DEFAULT 50;
    
   ALTER SEQUENCE edge_table_id_seq OWNED BY edge_table.id;    
END
$func$;

The manual:

Aside from avoiding the time for the archiver or WAL sender to process the WAL data, doing this will actually make certain commands faster, because they are designed not to write WAL at all if wal_level is minimal. (They can guarantee crash safety more cheaply by doing an fsync at the end than by writing WAL.) This applies to the following commands:

• CREATE TABLE AS SELECT

• CREATE INDEX (and variants such as ALTER TABLE ADD PRIMARY KEY)

• ALTER TABLE SET TABLESPACE

• CLUSTER

• COPY FROM, when the target table has been created or truncated earlier in the same transaction

Also important

• CREATE TABLE AS makes it impossible to use the pseudo-type serial directly. But since that is just a "makro", you can do everything by hand instead: Create the sequence, use it to generate id values. Finally, set the column default and make the column own the sequence. Related:

• How to convert primary key from integer to serial?

• The plpgsql function wrapper is optional (handy for repeated use), you could just run plain SQL in a transaction: BEGIN; ... COMMIT;

• Adding the PRIMARY KEY after inserting the data is also faster because creating the (underlying) index in one piece is faster than adding values incrementally.

• You had a logic error in your partitioning:

    WHERE DLE.ogc_fid >= i * batch_size
    AND   DLE.ogc_fid <= i * batch_size + batch_size
  

  The last row would overlap with the next partition, the row would be inserted repeatedly,leading to a unique violation in the PK. The use of < instead of <= would fix that - but I removed the partitioning altogether.

• If you run this repeatedly, a multicolumn index on digiroad_segmentti (k_elem_id, tyyppi, dyn_tyyppi, region) might pay, depending on data distribution.

Minor things

• Don't quote the language plpgsql name, it's an identifier.

• It would be pointless to mark a function without parameters as STRICT.

• VOLATILE is the default and just noise.

• Use COALESCE to provide a default for NULL values.

• Some of your double precision (float8) columns might work better as integer since you mostly had numeric (9,0) in your old tables, which can probably be replaced with the cheaper plain integer.

• The column region varchar(40) looks like a candidate for normalization (unless regions are mostly unique?) Create a region table and just use region_id as FK column in the main table.

Answer 2

If you only changed the shared_buffers,work_mem, and effective_cache_size config variables, then you're probably still running with checkpoint_segments=3.

In this case you only have three WAL segments, and as such, need to continuously recycle them, forcing writes out to the data files each time, which causes a huge amount of I/O activity and can certainly slow your machine to a crawl. You can check the checkpointing behavior by looking in the log and searching for the phrase checkpoints are occurring too frequently. You can also look at what they're doing by enabling log_checkpoints=on in your postgresql.conf

I would recommend changing your checkpoint_segments to something larger, like 40, and the checkpoint_completion_target to 0.9 to try and smooth out the behavior that you're describing.

The settings are further described here in the PostgreSQL documentation for 9.3 in the Write Ahead Log section. =)