Using EXPLAIN to Analyze SQL Performance¶

The SQL EXPLAIN directive can be used to determine details about how the database engine will handle a specific SQL query. It requires using the PostgreSQL console (psql).

It is important to follow this process so that the results most closely match how FWD uses SQL. Without the prepared statement, the results may differ from what will happen at runtime.

The process:

• PREPARE to prepare the statement/plan.
• EXPLAIN [ANALYZE] EXECUTE to get the explain output for the execution of the prepared statement.
• DEALLOCATE to free the prepared statement/plan's resources.

Here's an example of a query on the p2j_test.person table, where the 1st substitution parameter is an int, the 2nd is a string, and the 3rd is an int:

p2j_test=> prepare q1 (int, text, int) as select id from person p where p.emp_num = $1 and upper(rtrim(p.last_name)) = $2 order by p.ssn asc limit $3;
PREPARE
p2j_test=> explain analyze execute q1 (200, 'BARNS', 1);
                                                  QUERY PLAN                                                  
---------------------------------------------------------------------------------------------------------------
 Limit  (cost=1.17..1.17 rows=1 width=40) (actual time=0.086..0.086 rows=1 loops=1)
   ->  Sort  (cost=1.17..1.17 rows=1 width=40) (actual time=0.084..0.084 rows=1 loops=1)
         Sort Key: ssn
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on person p  (cost=0.00..1.16 rows=1 width=40) (actual time=0.022..0.026 rows=1 loops=1)
               Filter: ((emp_num = 200) AND (upper(rtrim(last_name)) = 'BARNS'::text))
               Rows Removed by Filter: 7
 Execution time: 0.168 ms
(8 rows)

Then, when you're done with the prepared query q1:

deallocate q1;

or just disconnect psql.

© 2020 Golden Code Development Corporation. ALL RIGHTS RESERVED.