Finding Bottlenecks and Avoiding Over-Optimization via Explain Plans

Performance optimization in a production database is important, but trying to over-optimize can make things more complicated without real improvements. In this post, I’ll share two very basic EXPLAIN ANALYZE outputs from a production system. A user asked us to help optimize these queries. I've changed the table and column names for privacy. We will look at how to spot slow parts of a query, improve performance the right way, and avoid unnecessary tuning. Plan A: Identifying and Resolving a Bottleneck Execution Plan A (Before Optimization) Nested Loop (cost=1000.42..25607.38 rows=1 width=129) (actual time=78.521..90.445 rows=0 loops=1) -> Gather (cost=1000.00..25598.95 rows=1 width=65) (actual time=78.520..90.443 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Seq Scan on <table_1> e (cost=0.00..24598.85 rows=1 width=65) (actual time=75.351..75.351 rows=0 loops=3) Filter: ((<column_1>) = '<date_value>'::date) AND ((<column_2>)::text = '<event_type>'::text) Rows Removed by Filter: <number_removed_rows> -> Index Scan using <index_name> on <table_2> a (cost=0.42..8.43 rows=1 width=41) (never executed) Index Cond: ((<column_3>)::text = (<column_4>)::text) Filter: ((<column_5>)::text = '<default_value>'::text) Planning Time: 0.466 ms Execution Time: 90.580 ms
Read More

SELECT FOR UPDATE – Reduce Contention and Avoid Deadlocks to Improve Performance in PostgreSQL

Relational databases are at the heart of countless applications around the world, from high-traffic e-commerce websites to enterprise resource planning (ERP) systems and financial services. Concurrency management—where multiple database transactions operate on the same data simultaneously—is critical to getting good performance and avoiding problems like deadlocks or data inconsistencies. When multiple transactions need to modify the same rows, ensuring data consistency can become tricky. A single wrong approach to locking can lead to suboptimal performance or even bring your application to a standstill as numerous transactions block one another. One tool in PostgreSQL’s arsenal to handle concurrency is SELECT FOR UPDATE. It allows you to lock specific rows before updating them, preventing other transactions from modifying those rows until your transaction completes. In this blog, we will dive deep into SELECT FOR UPDATE in PostgreSQL. We will explore how it helps in reducing contention, avoiding deadlocks, and ultimately boosting performance when dealing with highly concurrent applications.
Read More

Archives

Categories

Cart