There is an insert query inserting data into a partitioned table using values clause.

insert into t (c1, c2, c3) values (v1,v2,v3);

Database is AWS Aurora v11. Around 20 sessions run in parallel, executing ~2million individual insert statements in total. Seeing DataFileRead as the wait event, wondering why would this wait event show up for an insert statement? Would it be because each insert statement has to check if the PK/UK keys already exists in the table before committing the insert statement? Or other reasons?

CodePudding user response：

Each inserted row has to read the relevant leaf pages of each of the table's indexes in order to do index maintenance (insert the index entries for the new row into their proper locations--it has to dirty the page, but it first needs to read the page before it can dirty it), and also to verify PK/UK constraints. And maybe it also needs to read index leaf pages of other table's indexes in order to verify FKs.

If you insert the new tuples is the right order, you an hit the same leaf pages over and over in quick sequence, maximizing the cacheability. But if you have multiple indexes, there might be no ordering that can satisfy all of them.