How big is the Postgres database in GB?
The amount of time invested will pay off a hundred times over. The Postgres community is your second best friend. Earlier this week the performance of one of our (many) databases was plagued by a few pathologically large, primary-key queries in a smallish table (10 GB, 15 million rows) used to feed our graph editor.
Is the slow PostgreSQL query still in effect?
The slow Postgres query is gone. The 0.1% unlucky few who would have been affected by the issue are happy too. Seeing the impact of the change using Datadog allowed us to instantly validate that altering that part of the query was the right thing to do.
How long does it take Postgres to respond to a query?
In 99.9% of accounts these queries would be zippy. In a few cases where the number of tags used to annotate metrics is large, these queries would take up to 20 seconds. That much time spent in a database meant that someone was waiting in front of their browser for the graph editor to respond.
Is there a way to improve Postgres database performance?
It still has a very noticeable impact on Postgres performance. Remember that the query is quite simple. It’s a primary key lookup so there aren’t many obvious ways to fix it without dramatically re-architecting the database or the application. We turned to the community for help via the PGSQL-Performance mailing list.
How to reduce query time in Postgresql 9.0?
A simple but non-obvious one-line change (ANY (ARRAY […]) to ANY (VALUES (…))) in a (bad) PostgreSQL 9.0 query cuts query time from 20s to 0.2s. Starting with low-level metrics we make our way to your best friend: EXPLAIN ANALYZE. The amount of time invested will pay off a hundred times over. The Postgres community is your second best friend.
What happens when the bitmap gets too big In Postgres?
If the bitmap gets too large, the query optimizer changes the way it looks up data. In our case it has a large number of keys to check so it uses the more approximative way to retrieve the candidate rows and checks each row individually for a match on x_key and tags.