Table of Contents
How does a single PostgreSQL config change improved slow query performance?
To see the alternative query plans PostgreSQL considered before picking Hash Join, I disabled hash join and reran the query. There you go! The same query finished 50x faster when using a Nested Loop instead of a Hash Join. So why did PostgreSQL choose a worse plan for app A?
How does nested loop improve PostgreSQL query performance?
Interestingly, app A only accessed 10x more data than app B, but the response time was 3000x longer. To see the alternative query plans PostgreSQL considered before picking Hash Join, I disabled hash join and reran the query. There you go! The same query finished 50x faster when using a Nested Loop instead of a Hash Join.
How does a sequential scan improve PostgreSQL query performance?
The PostgreSQL execution plan for this query was unexpected. Even though both tables have Indexes, PostgreSQL decided to do a Hash Join with a sequential scan on the large table. The sequential scan on a large table contributed to most of the query time.
What does it mean when PostgreSQL says it is running?
If a query (or set of queries) in question has the status of ‘active’, then it’s actually running. If the whole query isn’t available in pg_stat_activity, fetch it from the developers or the postgresql log and start exploring the query planner.? This example shows a query plan for a two table join that also hits a partitioned table.
To see the alternative query plans PostgreSQL considered before picking Hash Join, I disabled hash join and reran the query. There you go! The same query finished 50x faster when using a Nested Loop instead of a Hash Join. So why did PostgreSQL choose a worse plan for app A?
The PostgreSQL execution plan for this query was unexpected. Even though both tables have Indexes, PostgreSQL decided to do a Hash Join with a sequential scan on the large table. The sequential scan on a large table contributed to most of the query time.
Interestingly, app A only accessed 10x more data than app B, but the response time was 3000x longer. To see the alternative query plans PostgreSQL considered before picking Hash Join, I disabled hash join and reran the query. There you go! The same query finished 50x faster when using a Nested Loop instead of a Hash Join.
What does it mean when a query is running in PostgreSQL?
If a query (or set of queries) in question has the status of ‘active’, then it’s actually running. If the whole query isn’t available in pg_stat_activity, fetch it from the developers or the postgresql log and start exploring the query planner.
Is it possible to join two tables in PostgreSQL?
This was a surprise for us, as both tables have indexes on the joined column. The PostgreSQL execution plan for this query was unexpected. Even though both tables have Indexes, PostgreSQL decided to do a Hash Join with a sequential scan on the large table. The sequential scan on a large table contributed to most of the query time.
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.
Is there a way to make Postgres database faster?
And the database looks much happier. 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. Update: this change need only be applied on 9.0.
This was a surprise for us, as both tables have indexes on the joined column. The PostgreSQL execution plan for this query was unexpected. Even though both tables have Indexes, PostgreSQL decided to do a Hash Join with a sequential scan on the large table. The sequential scan on a large table contributed to most of the query time.