Concepts: Query planning and optimisation

  • Planning: CPUs are faster than disk
  • Generate all possible paths to data: each possible access method for each target table; all possible join methods (planner)
  • Cost each plan using statistics and select plan with lowest cost (optimiser)
  • Basic query:
select a.data from a, b, c where a.i=b.i and b.i=c.i;
  • Plans and optimisation

RELOPTINFO (2 3): rows=5000 width=6
	joininfo (1): a.i = b.i
	path list:
	Nestloop(2 3) rows=5000 cost=0.00..35020.00
	  clauses: an expr = c.i
		SeqScan(2) rows=1000 cost=0.00..20.00
		SeqScan(3) rows=1000 cost=0.00..20.00

	Nestloop(2 3) rows=5000 cost=0.00..35052.00
	  pathkeys: ((a.i, b.i))
	  clauses: an expr = c.i
		IdxScan(2) rows=1000 cost=0.00..52.00
		  pathkeys: ((a.i, b.i))
		SeqScan(3) rows=1000 cost=0.00..20.00

	cheapest startup path:
	Nestloop(2 3) rows=5000 cost=0.00..35020.00
	  clauses: an expr = c.i
		SeqScan(2) rows=1000 cost=0.00..20.00
		SeqScan(3) rows=1000 cost=0.00..20.00

	cheapest total path:
	Nestloop(2 3) rows=5000 cost=0.00..35020.00
	  clauses: an expr = c.i
		SeqScan(2) rows=1000 cost=0.00..20.00
		SeqScan(3) rows=1000 cost=0.00..20.00

RELOPTINFO (1 2): rows=60 width=8
	joininfo (3): an expr = c.i
	path list:
	HashJoin(1 2) rows=60 cost=1.15..54.40
	  clauses: a.i = b.i
		SeqScan(2) rows=1000 cost=0.00..20.00
		SeqScan(1) rows=12 cost=0.00..1.12
	MergeJoin(1 2) rows=60 cost=0.00..58.41
	  clauses: a.i = b.i
		IdxScan(1) rows=12 cost=0.00..3.13
		  pathkeys: ((a.i, b.i))
		IdxScan(2) rows=1000 cost=0.00..52.00
		  pathkeys: ((a.i, b.i))

	cheapest startup path:
	MergeJoin(1 2) rows=60 cost=0.00..58.41
	  clauses: a.i = b.i
		IdxScan(1) rows=12 cost=0.00..3.13
		  pathkeys: ((a.i, b.i))
		IdxScan(2) rows=1000 cost=0.00..52.00
		  pathkeys: ((a.i, b.i))

	cheapest total path:
	HashJoin(1 2) rows=60 cost=1.15..54.40
	  clauses: a.i = b.i
		SeqScan(2) rows=1000 cost=0.00..20.00
		SeqScan(1) rows=12 cost=0.00..1.12

RELOPTINFO (2 3 1): rows=300 width=10
	path list:
	Nestloop(2 3 1) rows=300 cost=1.15..2154.40
	  clauses: an expr = c.i
		HashJoin(1 2) rows=60 cost=1.15..54.40
		  clauses: a.i = b.i
			SeqScan(2) rows=1000 cost=0.00..20.00
			SeqScan(1) rows=12 cost=0.00..1.12
		SeqScan(3) rows=1000 cost=0.00..20.00
	Nestloop(2 3 1) rows=300 cost=0.00..2158.41
	  clauses: an expr = c.i
		MergeJoin(1 2) rows=60 cost=0.00..58.41
		  clauses: a.i = b.i
			IdxScan(1) rows=12 cost=0.00..3.13
			  pathkeys: ((a.i, b.i))
			IdxScan(2) rows=1000 cost=0.00..52.00
			  pathkeys: ((a.i, b.i))
		SeqScan(3) rows=1000 cost=0.00..20.00

	cheapest startup path:
	Nestloop(2 3 1) rows=300 cost=0.00..2158.41
	  clauses: an expr = c.i
		MergeJoin(1 2) rows=60 cost=0.00..58.41
		  clauses: a.i = b.i
			IdxScan(1) rows=12 cost=0.00..3.13
			  pathkeys: ((a.i, b.i))
			IdxScan(2) rows=1000 cost=0.00..52.00
			  pathkeys: ((a.i, b.i))
		SeqScan(3) rows=1000 cost=0.00..20.00

	cheapest total path:
	Nestloop(2 3 1) rows=300 cost=1.15..2154.40
	  clauses: an expr = c.i
		HashJoin(1 2) rows=60 cost=1.15..54.40
		  clauses: a.i = b.i
			SeqScan(2) rows=1000 cost=0.00..20.00
			SeqScan(1) rows=12 cost=0.00..1.12
		SeqScan(3) rows=1000 cost=0.00..20.00
Prev

Next

Page 13