There are several StackOverflow posts with others that have similar issues in PostgreSQL queries that use LIMIT clauses with ORDER BY ( Example 1, Example 2, Example 3). But while this recommendation made other queries run faster, it did not help this problem. OtterTune’s automated health checks initially recommended that the customer to increase statistics collection on the table (i.e., ANALYZE) by increasing PostgreSQL’s statistics_target configuration knob. In this case, the application retrieves the first ten items from a set named ‘ABC.’ But the customer reported that the query took an inexplicably long time to complete (18 seconds!). This is a common query pattern in many applications to show the top n entries in a table (e.g., the most recent orders for a customer). JOIN item_set ON (item_set.id = em_set_id) This LIMIT will cause us problems later in this article, which is why we are calling it out. This OtterTune user reported that a query ran slowly when using ORDER BY along with the LIMIT 10 clause. Recently, a Postgres user contacted us via the OtterTune Community Slack channel. OtterTune has a health check in place to determine if ANALYZE needs to be run more often.īut what if running ANALYZE doesn’t provide the necessary improvement? A recent interaction with an OtterTune user might shed light on your issue. It’s like the “restart your computer when it gets slow” advice for databases. We have found that running ANALYZE more frequently has solved many of our customers’ slow query problems. PostgreSQL’s query planner then uses these statistics to help determine the most efficient execution plans for queries. PostgreSQL’s ANALYZE command collects statistics about specific table columns, entire tables, or entire databases and stores the results in the pg_statistic system catalog. What’s an otter to do? The first thing to do - without question - is run ANALYZE.
#Postgres query plan free
If you’re too busy to read this and just want to see how OtterTune uses AI to help your PostgreSQL or MySQL database running on Amazon RDS or Amazon Aurora, sign up today for a 30-day free trial! 🦦Īh, the frustrating slow-running PostgreSQL query.
0 kommentar(er)
