Mysql – Measuring SQL execution time in PostgreSQL

MySQLpostgresql

MySQL has a performance_schema database that allows one to capture SQL statement execution time data in a table (e.g. performance_schema.events_statements_history_long ; useful MySQL link).

I was wondering if a similar set of tools existed in PostgreSQL?

Best Answer

There isn't a reasonably easy way to do this, but you can:

Log slow queries
Use EXPLAIN ANALYZE to see where the query is actually spending its time.

You could probably store plans in the database somehow but I am not quite sure how this would be done (maybe with a pl/perl function?). You'd have to roll your own solution though.

Storing plans in the database however is not easy. One of the huge problems that you will have is getting the recursive structure right so you can query it. A simple approach might be to take a JSON format and store that, or store XML.

Related Solutions

Postgresql – Does the pgpool-II extension allow for parallel execution of any query

To my knowledge it does not allow that level of parallelism. If that's what you are trying to do the best approach is to go with Postgres-XC or GridSQL, but Postgres-XC while more complex is also more flexible.

http://postgres-xc.sourceforge.net/ is the project page.

PostgreSQL – Time Oriented Design with Temporal Tables

The WITH keyword defines the operator which is used to test for "equality" between two rows.

A regular unique index is just a special case of an exclusion constraint. If you were to define a "normal" unique constraint using an exclusion constraint, you would use = as the operator, e.g: exclude using btree (id with =) instead of unique (id)

The && for range types is the "overlaps" operator which tests if two ranges, well, overlap.

e.g.

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-08', '2019-03-01')

returns true (as the ranges overlap)

while

daterange('2019-01-01', '2019-02-21') && daterange('2019-02-22', '2019-03-01')

will return false as there is no overlap.