source
The structure of a query plan is a tree of plan nodes. Nodes at the bottom level of the tree are scan nodes: they return raw nodes from a table.
Different types of scan nodes
- sequential scans
- index scans
- bitmap index scans
If the query requires joining, aggregation, sorting or other operation on the raw rows, there will be additional nodes above the scan nodes to perform these operations.
The output of EXPLAIN has one line for each node in the plan tree, showing the basic node type plus the cost estimates that the planner made for the execution of that plan node.
An example of a simple EXPLAIN output:
EXPLAIN SELECT * FROM tenk1;
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
Content of the output:
- sequential scan node
- the first cost before '..' is estimated start-up cost.
- estimated total cost
- estimated number of rows returned
- estimated average row width returned in bytes
Get number of pages and rows in a table
SELECT relpages, reltuples from pg_class where relanme = 'collection';
estimated_cost = disk_pages_read * seq_page_cost + rows_scanned * cpu_tuple_cost
