SQL says what rows you want. The planner, a part of PostgreSQL, chooses how to find them. The result is a .
Put EXPLAIN in front of a query to see its plan. The query does not run.
sql
EXPLAIN SELECT * FROM members WHERE email = 'user77@example.com';text
Seq Scan on members (cost=0.00..1093.00 rows=1 width=44)
Filter: (email = 'user77@example.com'::text)Read the plan
Seq Scan on membershow it reads, and whatcost=0.00..1093.00cost to the first row .. to the last rowrows=1rows it expects to returnwidth=44bytes per row
- Seq Scan (sequential scan): read every row of the table, test the
Filteron each, keep the rows that pass. In the filter,::textshows the type that PostgreSQL gives the value. - cost is an estimate in the planner's own units, not in milliseconds. Use it to compare plans: a lower total cost is a cheaper plan.
- rows is an estimate too. The planner keeps statistics about each column. The
ANALYZEcommand collects them, and PostgreSQL also collects them by itself from time to time.
The table members has 50 000 rows. To find one row, this plan reads all 50 000.
Run it: EXPLAIN ANALYZE
EXPLAIN ANALYZE runs the query and adds what really happened:
text
Seq Scan on members (cost=0.00..1093.00 rows=1 width=44) (actual time=0.040..4.399 rows=1.00 loops=1)
Filter: (email = 'user77@example.com'::text)
Rows Removed by Filter: 49999
Buffers: shared hit=468
Planning Time: 0.087 ms
Execution Time: 4.461 ms| Part | Meaning |
|---|---|
actual time=0.040..4.399 | milliseconds to the first row .. to the last row |
rows=1.00 | rows it really returned (compare with the estimate) |
Rows Removed by Filter: 49999 | rows it read and threw away |
Execution Time | total time to run the query |
Buffers counts the pages of data that the step read. You can ignore it in this chapter. The times change a little from run to run.