How can we attribute time taken in index updation process while executing an insert statement in postgresql?
Add a comment
|
3 Answers
You will not get that information through explain. The best way is to run this in a loop using a script or pgbench with the index absent and then remove the data and repeat with the index present.
Just remember to VACUUM ANALYSE the table before each test. If you can close the table consider VACUUM FULL or CLUSTER.
Remember that the times may be different depending on the size of the table and the index, state of the table and the index and index level of complexity. So the result will be different if you insert into an empty table and if you insert into a table with 100M rows.
Comments
You can use EXPLAIN (ANALYZE) Be carefull, as this is sql query will be executed If you don't want it - use ROLLBACK like this:
BEGIN;
EXPLAIN ANALYZE <query>
ROLLBACK;
2 Comments
Jinendra Khabiya
I tried that. How do we figure out a particular section in the query plan which attributes to index update in case of a insert query?
Maria
I'm not quite understand your question but here is very good article (postgresqltutorial.com/postgresql-explain) about explain and about options, maybe uising them you will get what you want, e.g. VERBOSE
The only way I've found to get good answers is too benchmark with and without the index.
