PostgreSQL COPY: is it always faster to delete and recreate indexes

postgresql

In this answer, Erwin Brandstetter recommends

It is also substantially faster to delete indexes before a huge bulk INSERT / COPY and recreate them afterwards as it is much more costly to incrementally adjust indexes for every inserted row than to create them at once.

1) Is this true even when the table is already large? If not, is there an easy way to estimate the point of diminishing return? For example, I'm doing COPY of 30,000 records at a time into a table which already has say 100 million rows. Each batch is relatively small, but on the other hand the total batches on hand to import at the moment will more than double the size of the table.

2) If I were to delete the index before COPY, would recreating it be faster if the COPY is done with records already in index order?

There is only one index on the table, which is a 5-column primary key.

I'm doing this on a non-production machine, and I've already disabled fsync, syncronous_commit, autovacuum, etc. I'm executing the COPY operation from four separate processes concurrently on an 8-core machine. I've got 12GB of memory, so I could set maintenance_work_mem to 1GB or more if it would help.

Best Answer

My guess would be going with some benchmarking, measuring the time it's taking you to recreate the full index, the time it takes to insert 10k rows without index, with index. then I'd try with 1000 rows, 5000 rows, 10000 rows, to try to figure out a trend.

Because even if you try to reduce the amount of factors that could affect the operation time (as Auto-Vacuum) it still depends on your hardware and your whole postgres.conf

BONUS: if you share with us some benchmarks, we could gladly help you to figure out the trend.

Related Solutions

Postgresql – How to change table/indexes structure for a query with two aggregated columns

As suggested by @Mihai, adding an index that include docid as first column help. The best solution is to create two of them, one for (docid,creatingDate) and the second one for (docid,lastChangeDate). Please note for simplyfing the index scan, the second fild is ordered DESCENDING, infact the first two indexes are ignored. The new plan is now:

=# create index testtesttest1 on agenzia.systemdatabydocument (docid,creatingDate);
CREATE INDEX
=# create index testtesttest2 on agenzia.systemdatabydocument (docid,lastChangeDate);
CREATE INDEX
=# create index testtesttest3 on agenzia.systemdatabydocument (docid,creatingDate DESC);
CREATE INDEX
=# create index testtesttest4 on agenzia.systemdatabydocument (docid,lastChangeDate DESC);
CREATE INDEX
=# explain analyze SELECT MAX(creatingDate), MAX(lastChangeDate) FROM agenzia.SystemDataByDocument WHERE docId = 'Sedi';
                                                                  QUERY PLAN                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=5.40..5.41 rows=1 width=0) (actual time=0.151..0.151 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..2.70 rows=1 width=4) (actual time=0.091..0.091 rows=0 loops=1)
       ->  Index Scan using testtesttest3 on systemdatabydocument  (cost=0.00..1372.58 rows=508 width=4) (actual time=0.085..0.085 rows=0 loops=1)
             Index Cond: (((docid)::text = 'Sedi'::text) AND (creatingdate IS NOT NULL))
   InitPlan 2 (returns $1)
     ->  Limit  (cost=0.00..2.70 rows=1 width=4) (actual time=0.054..0.054 rows=0 loops=1)
       ->  Index Scan using testtesttest4 on systemdatabydocument  (cost=0.00..1372.58 rows=508 width=4) (actual time=0.053..0.053 rows=0 loops=1)
             Index Cond: (((docid)::text = 'Sedi'::text) AND (lastchangedate IS NOT NULL))
 Total runtime: 0.256 ms

PostgreSQL 8.4 – Should VACUUM ANALYZE Be Done Before REINDEXing?

If the CREATE INDEX statement sees that another session holds an active snapshot that might still be interested in the deleted records, then it includes those deleted records into the new index.

Similarly, if a REINDEX sees that another session holds an active snapshot that might still be interested in the deleted records, then it includes those deleted records into the new index.

If a VACUUM sees that another session holds an active snapshot that might still be interested in the deleted records, then it keeps those records in the table. And then the REINDEX or CREATE INDEX also need to carry them into the new index, as long a the snapshot still exists.

Once there or no longer any snapshots that could possible see the deleted rows, then the VACUUM may remove them from the table. But a CREATE INDEX or REINDEX could also just not carry them over into the new index, whether VACUUM had gotten around to removing them from the able or not.

So in your scenario, the role of the VACUUM between the initial CREATE INDEX and REINDEX is probably just to take up time, during which time your long-running transaction hopefully goes away on its own and drops the interfering snapshot.

Best Answer

Related Solutions

Postgresql – How to change table/indexes structure for a query with two aggregated columns

PostgreSQL 8.4 – Should VACUUM ANALYZE Be Done Before REINDEXing?

Related Question