executor - Query Execution Techniques in PostgreSQL, KSIĄŻKI Informatyka
[ Pobierz całość w formacie PDF ]
Query Execution Techniques in PostgreSQL
Neil Conway
<nconway@truviso.com>
Truviso,Inc.
October 20, 2007
NeilConway(Truviso) QueryExecutioninPostgreSQL
October20,2007 1/42
Introduction
Goals
Describe how Postgres works internally
Shed some light on the black art of
EXPLAIN
reading
Provide context to help you when tuning queries for performance
NeilConway(Truviso) QueryExecutioninPostgreSQL
October20,2007 2/42
Introduction
Goals
Describe how Postgres works internally
Shed some light on the black art of
EXPLAIN
reading
Provide context to help you when tuning queries for performance
Outline
1
The big picture: the roles of the planner and executor
2
Plan trees and the Iterator model
3
Scan evaluation: table, index, and bitmap scans
4
Join evaluation: nested loops, sort-merge join, and hash join
5
Aggregate evaluation: grouping via sorting, grouping via hashing
6
Reading
EXPLAIN
NeilConway(Truviso) QueryExecutioninPostgreSQL
October20,2007 2/42
Division of Responsibilities
Typical Query Lifecycle
Parser:
analyze syntax of query
query string )
Query
(AST)
Rewriter:
apply rewrite rules (incl. view denitions)
Query
) zero or more
Query
Planner:
determine the best way to evaluate the query
Query
)
Plan
Executor:
evaluate the query
Plan
)
PlanState
PlanState
) query results
NeilConway(Truviso) QueryExecutioninPostgreSQL
October20,2007 3/42
Query Planner
Why Do We Need A Query Planner?
Queries are
expressed
in a
logical
algebra (e.g. SQL)
\Return the records that satisfy . . . "
Queries are
executed
from a
physical
algebra (query plan)
\Index scan table x with key y , sort on key z , . . . "
For a given SQL query, there are many equivalent query plans
Join order, join methods, scan methods, grouping methods, order of
predicate evaluation, semantic rewrites, . . .
Dierence in runtime cost among equivalent plans can be enormous
NeilConway(Truviso) QueryExecutioninPostgreSQL
October20,2007 4/42
[ Pobierz całość w formacie PDF ]