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

Question

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?

Laurenz Albe · Accepted Answer · 2021-05-18 14:32:15Z

4

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.

edited May 18, 2021 at 14:32

answered May 17, 2021 at 9:42

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anand Sowmithiran Over a year ago

As mentioned in my question, used auto explain, even enabled the log_nested_statements, but it did not show if the function used the index. Is there any server restart required, for this to take effect? I am running pg13 on windows server 2016.

Laurenz Albe Over a year ago

Oh, sorry, I thought you knew that since you said that you used auto_explain. Activating it requires a restart. I have extended the answer.

Anand Sowmithiran Over a year ago

Log destination and related params were not configured in the postgres .conf file on this server. I configured csvlog and related parameters, and got the output of auto_explain for my queries.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related