Like described here, prepared statements are also interesting to get analyzed by their execution plan. This is especially interesting to identify long lasting queries whilst developing Icinga with Postgresql.
A normal statement would look like this.
icinga=# EXPLAIN SELECT 1;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
(1 Zeile)
First cost is startup, second cost if all rows are retrieved.
A prepared statement with bind parameters can be called with an actual value, but keep in mind that the cost is calculated on the prepared statement instead of the actual value. To fetch the execution plan on the actual value, see below with analyze.
icinga=# PREPARE stmt(int) AS SELECT $1;
icinga=# EXPLAIN EXECUTE stmt(1);
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
(1 Zeile)
On explain, you can use verbose as well.
icinga=# EXPLAIN VERBOSE EXECUTE stmt(1);
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0)
Output: $1
(2 Zeilen)
analyze will execute the query – for selects this will be rather harmless, but on insert/delete/update this may cause harm to your data! better wrap the test within a begin rollback call.
executing the query will explain the actual query, it’s runtime and results. this can be useful to track down wrongly expected outputs.
icinga=# BEGIN;
icinga=# EXPLAIN ANALYZE EXECUTE stmt(1);
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.004..0.005 rows=1 loops=1)
Total runtime: 0.057 ms
(2 Zeilen)
icinga=# ROLLBACK;