In PostgreSQL you can check if an index is being used for a query by using the EXPLAIN (ANALYZE , BUFFERS) before the query like:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE id = '01hjp66v55x0...';

The (ANALYZE , BUFFERS) executes the command and provides more information. Keep in mind that running EXPLAIN (ANALYZE, BUFFERS) will actually execute the query, which might not be desirable on a production system with heavy load or with queries that modify data. In such situations you can use just EXPLAIN.

• EXPLAIN: Shows the estimated execution plan without executing the query.
• EXPLAIN (ANALYZE): Shows the actual execution plan by running the query.
• EXPLAIN (ANALYZE, BUFFERS): Shows the actual execution plan with additional information about buffer usage (requires superuser privileges).

In the output, you’re looking for lines that indicate the use of an index. Here are some possible indications:

• Index Scan: This indicates that PostgreSQL is using an index to directly retrieve the data.
• Bitmap Heap Scan with Bitmap Index Scan: This indicates that PostgreSQL is using a bitmap index scan to first build a bitmap of matching rows, and then it retrieves the rows from the table.

If you see a Seq Scan (sequential scan), it means that PostgreSQL is scanning the entire table row by row instead of using an index. This could happen if PostgreSQL believes that using an index would not be more efficient (for example, if the table is very small or if a large percentage of the rows would be returned by the query).