CODEWITHSUNDEEP
postgresqlindexing
Anand Sowmithiran
Anand Sowmithiran

Reputation: 2920

How to detect if Postgresql function utilizing index on the tables or not

I have created a PL/pgSQL table-returning function that executes a SELECT statement and uses the input parameter in the WHERE clause of the query.

I frame the statement dynamically and execute it like this: EXECUTE sqlStmt USING empID;

sqlStmt is a variable of data type text that has the SELECT query which joins 3 tables.

When I execute that query in pgAdmin and analyze I could see that 'Index scan' on the tables are utilized as expected. However, when I do EXPLAIN ANALYZE SELECT * from fn_getDetails(12), the output just says "Function scan".

How do we know if the table indexes are utilized? Other SO answers to use auto_explain module did not provide details of the function body statements. And I am unable to use the PREPARE inside my function body.

The time taken by execution of the direct SELECT statement is almost the same as the use of function, just couple of milliseconds, but how can I know if the index was used?

Upvotes: 0

Views: 972

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246383

auto_explain will certainly provide the requested information.

Set the following parameters:

shared_preload_libraries = 'auto_explain'  # requires a restart
auto_explain.log_min_duration = 0          # log all statements
auto_explain.log_nested_statements = on    # log statements in functions too

The last parameter is required for tracking SQL statements inside functions.

To activate the module, you need to restart the database.

Of course, testing if the index is used in a query on a small table won't give you a reliable result. You need about as many test data as you expect to have in reality.

Upvotes: 4

Related Questions