I created two multi-column indexes on a big table (~2,5 millions rows 370Mb and 621Mb with indexes).

SELECT queries are very fast.

Know I want to check if we have a regression for INSERT/UPDATE queries.

Is there any script to perform EXPLAIN ANALYZE INSERT/UPDATE without write anything in the table (with BEGIN, ROLLBACK statements)?

I want to run the script without indexes and with indexes to compare execution times.

I am using Postgresql v9.5


You can run EXPLAIN ANALYZE inside a transaction and then roll back the transaction. This will write to the table and indexes, but the writes will not become visible to other users, other then through locks and side-channels (e.g. table and index bloat).

If you already have a script which runs the updates you want, adding BEGIN and ROLLBACK (or changing COMMIT to ROLLBACK if your script already does explicit transactions) should be incredibly trivial; so it is not clear what you are asking.

But you should clone your database to a test environment, and then run your tests in the test environment in a realistic manner. Running aborted updates in a tight loop might not have the same performance as running committed updates in a tight loop.

  • Ok, thank you. How can i running loops for insert or update statements in a script? I do not know how to write this kind of script. Have you an example please? – Mika Feb 23 '18 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.