PostgreSQL: Question Optimization for Mere People | by Eyal Trabelsi

How Relevance Fashions Foreshadowed Transformers for NLP

How Deep Characteristic Embeddings and Euclidean Similarity Energy Automated Plant Leaf Recognition

We are going to use it for instance of a easy question: we need to depend the variety of customers that don’t have Twitter handles.

EXPLAIN ANALYZE
SELECT COUNT(*) FROM customers WHERE twitter != '';

We will see the execution plan returned from the EXPLAIN ANALYZE clause

It seems cryptic at first, and It’s even longer than our question, and that on a small instance of real-world execution plans could be overwhelming if you happen to do not focus 😭.

But it surely does present helpful info. We will see that the question execution took 1.27 seconds, whereas the question planning took solely 0.4 milli-seconds (negligible time).

We will see the time the question planning and execution took

The execution plan is structured as an inverse tree. Within the subsequent determine, you’ll be able to see the execution plan is split into totally different nodes every considered one of which represents a distinct operation whether or not it is an Aggregation or a Scan.

There are a lot of sorts of nodes operations, from Scan associated (‘Seq Scan’, ‘Index Solely Scan’, and many others…), Be a part of associated( ‘Hash Be a part of’, ’Nested Loop’, and many others…), Aggregation associated (‘GroupAggregate’, ’Combination’, and many others…) and others ( ‘Restrict’, ‘Kind’, ‘materialize’, and many others..). Thankfully it is advisable keep in mind any of this.

Professional Tip #3 💃: Focus is essential, look solely on nodes which might be problematic.

Professional Tip #4 💃: Cheat ! on the problematic nodes search what they imply within the clarify glossary.

Now, let’s drill down into how we all know which node is the problematic one.

There’s quite a lot of info we are able to see on every node

Let’s drill right down to what these metrics truly imply.

Precise Loops: the variety of loops the identical node executed is 1. To get the whole time and rows, the precise time and rows have to be multiplied by loops values.
Precise Rows: the precise variety of produced rows of the Combination node is 1 (per-loop common and we’ve loops is 1).
Plan Rows: the estimated variety of produced rows of the Combination node is 1. The estimated variety of rows could be off relying on statistics.
Precise Startup Time: the time it took to return the primary row in milliseconds of the Combination node is 1271.157 (aggregated and contains earlier operations).
Startup Value: arbitrary items that signify the estimated time to return the primary row of the Combination node is 845110(aggregated and contains earlier operations).
Precise Complete Time: the time it took to return all of the rows in ms of the Combination node is 1271.158 (per-loop common and we’ve loops is 1 and aggregated and embody earlier operations).
Complete Value: arbitrary items that signify the estimated time to return all of the rows of Combination node is 845110 (aggregated).
Plan Width: the estimated common measurement of rows of the Combination node is 8 bytes.