PostgreSQL: Lessons Learned While Optimising Query Performance | by Ashutosh Narang | Mar, 2022

To get your outcomes many instances quicker

Database Efficiency == Software Efficiency
SELECT * FROM Observe WHERE Identify='Levitating';
Tables are a sequence of pages
Web page Composition

Easy methods to enhance the efficiency of the above question? Enter Index.

CREATE INDEX CONCURRENTLY ON Observe Utilizing btree (Identify);
B-Tree WIth Branching Issue 3
  1. The primary pointer factors to a baby node with values lower than 10
  2. The second pointer factors to a baby node with values between (10,20)
  3. The third pointer factors to a baby node with values higher than 20
B-Tree WIth Branching Issue 4
  1. Operations resembling search, insert and delete have logarithmic time complexity.
  2. B-Bushes are extremely shallow knowledge constructions. A B-Tree with a branching issue within the order of hundreds means they’ll retailer tens of millions of parts in solely two to 3 layers.
  1. A question will use a composite index if and provided that the WHERE clause of the question has not less than the left-most columns of the index in it.
  2. An index doesn’t essentially need to be created for all of the rows of a desk, it may be created for a subset of rows. This has actual advantages by way of house and time complexity.
  3. CREATE INDEX CONCURRENTLY ON Observe Utilizing btree (Identify) WHERE ArtistId In (ArtistId1, ArtistId2, ...);
    Utilizing the above command we’re telling PostgreSQL to create an index just for the rows the place ArtistId is certainly one of (ArtistId1, ArtistId2, ...)

1. Discover Costly Queries

SELECT queryid, calls, mean_time, substring(question for 100)
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 3;
| query_id   | calls | mean_time | substring |
| 1819595255 | 18000 | 500.12 | Question 1 |
| 10013512 | 100 | 273.25 | Question 2 |
| 50123753 | 3000 | 252.37 | Question 3 |

2. Analyze Costly Queries

EXPLAIN SELECT * FROM Album WHERE Identify = 'Favorite Worst Nightmare';
Seq Scan on Album (price=0.00..169375.85 rows=10000000 width=32) Filter: (Identify = 'Favorite Worst Nightmare'::textual content)
EXPLAIN ANALYZE SELECT * FROM Observe WHERE Identify='Levitating';                         QUERY PLAN                                          
Index Scan utilizing idx_name on Observe (price=0.00..32.97 rows=1 width=64) (precise time=0.352..0.357 rows=1 loops=1)
Index Cond: (Identify='Levitating')
Planning Time: 3.011 ms
Execution Time: 0.379 ms
  1. Optimizing PostgreSQL queries involving JOINS
  2. How the question optimizer works and totally different levels of question execution
  3. Why caching is just not all the time the reply and, usually, individuals underestimate the database by straight leaping to a caching answer
  4. Database design do’s and don’ts
  5. Rather more

More Posts