Troubleshoot queries

A particular query executes more slowly than expected

PostgreSQL‘s EXPLAIN feature allows users to understand the underlying query plan that PostgreSQL uses to execute a query. There are multiple ways that PostgreSQL can execute a query: for example, a query might be fulfilled using a slow sequence scan or a much more efficient index scan. The choice of plan depends on what indexes are created on the table, the statistics that PostgreSQL has about your data, and various planner settings. The EXPLAIN output lets you know which plan PostgreSQL is choosing for a particular query. PostgreSQL has an in-depth explanation of this feature.

To understand the query performance on a hypertable, first make sure that the planner statistics and table maintenance is up-to-date on the hypertable by running VACUUM ANALYZE <your-hypertable>;. Then, run the following version of EXPLAIN:

EXPLAIN (ANALYZE on, BUFFERS on) <original query>;

If you suspect that your performance issues are due to slow IOs from disk, you can get even more information by enabling the track_io_timing variable with SET track_io_timing = 'on'; before running the above EXPLAIN.