How do we evaluate what makes a good query and what makes a bad query?
The process that the planner goes through is what the following videos focus on.
|EXPLAIN||Build a query plan and display info about it.|
|EXPLAIN ANALYZE||Build a query plan, run it and display info about it.|
These are used for benchmarking + evaluating queries. You will never use them in for fetching real data.
You can also use the
explain analyze dropdown option when running a query in PGAdmin.
In the EXPLAIN output: any line that has an arrow
-> is known as a query node.
Note that the top line is also technically a query node, although there is no arrow.
A breakdown of one line
Hash Join (cost=0.00..1.00 rows=1 width=4) is as follows:
Hash Joinis how the node is generating the data.
costis the amount of processing power required for this step.
rowsis a guess at how many rows the step will produce.
widthis a guess at the average number of bytes of each row.
How does Postgres make a guess at some of those numbers? It uses the
You can see this with:
SELECT * FROM pg_stats WHERE tablename = 'users'
The data here helps Postgres make an educated guess.