Thread: Number of internal operations for a single DML/DDL
Hi Team,
I wanted to know how many internal operations postgres performs for a single DML or DDL statement.
For ex: if I run an insert like insert into emp values(1,'Name'); then one operation postgres does, is it internally updates the pg_stat_*_tables to increase the count of n_tup_ins and n_live_tup for the emp table.
Apart from these what all operations does postgres do internally, I wanted an average estimated count as I know it would be difficult to get the details for each of the DML/DDL statements
Thanks and Regards,
Nikhil
On Wednesday, March 17, 2021, Nikhil Shetty <nikhil.dba04@gmail.com> wrote:
I wanted to know how many internal operations postgres performs for a single DML or DDL statement.
Why?
Apart from these what all operations does postgres do internally, I wanted an average estimated count as I know it would be difficult to get the details for each of the DML/DDL statements
Frankly, that number doesn’t exist; the nature of the input and the state of the system directly impacts what any individual command does internally and so one cannot derive a count, or even an understanding of what happens, without knowledge of those two things. Which brings us again to “why?”.
David J.
Hi David,
Thanks for your input.
We got this requirement from one of our clients as they were able to derive a count of internal operations for their oracle databases and they wanted to know how it can be done for Postgres. It is basically to check the I/O that happens at the backend for each DML/DDL. We did know that it is very tough to get the exact or at least an estimated count and so requested help from the community.
Thanks and Regards,
Nikhil
On Thu, Mar 18, 2021 at 11:39 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Wednesday, March 17, 2021, Nikhil Shetty <nikhil.dba04@gmail.com> wrote:I wanted to know how many internal operations postgres performs for a single DML or DDL statement.Why?Apart from these what all operations does postgres do internally, I wanted an average estimated count as I know it would be difficult to get the details for each of the DML/DDL statementsFrankly, that number doesn’t exist; the nature of the input and the state of the system directly impacts what any individual command does internally and so one cannot derive a count, or even an understanding of what happens, without knowledge of those two things. Which brings us again to “why?”.David J.