EXPLAIN ANALYZE EXECUTE query(100, 200);

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------

HashAggregate (cost=39.53..39.53 rows=1 width=8) (actual time=0.661..0.672 rows=7 loops=1)

-> Index Scan using test_pkey on test (cost=0.00..32.97 rows=1311 width=8) (actual time=0.050..0.395 rows=99 loops=1)

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------

HashAggregate (cost=9.54..9.54 rows=1 width=8) (actual time=0.156..0.161 rows=11 loops=1)

Group Key: foo

-> Index Scan using test_pkey on test (cost=0.29..9.29 rows=50 width=8) (actual time=0.039..0.091 rows=99 loops=1)

Index Cond: ((id > $1) AND (id < $2))

Total runtime: 0.851 ms

(4 rows)

Total runtime: 0.225 ms

(5 rows)

</programlisting>

</para>

ExplainState*es);

staticvoidshow_merge_append_keys(MergeAppendState*mstate,List*ancestors,

ExplainState*es);

staticvoidshow_sort_keys_common(PlanState*planstate,

intnkeys,AttrNumber*keycols,

List*ancestors,ExplainState*es);

staticvoidshow_agg_keys(AggState*astate,List*ancestors,

ExplainState*es);

staticvoidshow_group_keys(GroupState*gstate,List*ancestors,

ExplainState*es);

staticvoidshow_sort_group_keys(PlanState*planstate,constchar*qlabel,

intnkeys,AttrNumber*keycols,

List*ancestors,ExplainState*es);

staticvoidshow_sort_info(SortState*sortstate,ExplainState*es);

staticvoidshow_hash_info(HashState*hashstate,ExplainState*es);

staticvoidshow_instrumentation_count(constchar*qlabel,intwhich,

planstate,es);

break;

caseT_Agg:

show_agg_keys((AggState*)planstate,ancestors,es);

show_upper_qual(plan->qual,"Filter",planstate,ancestors,es);

if (plan->qual)

show_instrumentation_count("Rows Removed by Filter",1,

planstate,es);

break;

caseT_Group:

show_group_keys((GroupState*)planstate,ancestors,es);

show_upper_qual(plan->qual,"Filter",planstate,ancestors,es);

if (plan->qual)

show_instrumentation_count("Rows Removed by Filter",1,

{

Sort*plan= (Sort*)sortstate->ss.ps.plan;

show_sort_keys_common((PlanState*)sortstate,

plan->numCols,plan->sortColIdx,

ancestors,es);

show_sort_group_keys((PlanState*)sortstate,"Sort Key",

plan->numCols,plan->sortColIdx,

ancestors,es);

}

{

MergeAppend*plan= (MergeAppend*)mstate->ps.plan;

show_sort_keys_common((PlanState*)mstate,

plan->numCols,plan->sortColIdx,

ancestors,es);

show_sort_group_keys((PlanState*)mstate,"Sort Key",

plan->numCols,plan->sortColIdx,

ancestors,es);

}

show_agg_keys(AggState*astate,List*ancestors,

ExplainState*es)

{

Agg*plan= (Agg*)astate->ss.ps.plan;

if (plan->numCols>0)

{

ancestors=lcons(astate,ancestors);

show_sort_group_keys(outerPlanState(astate),"Group Key",

plan->numCols,plan->grpColIdx,

ancestors,es);

ancestors=list_delete_first(ancestors);

}

}

show_group_keys(GroupState*gstate,List*ancestors,

ExplainState*es)

{

Group*plan= (Group*)gstate->ss.ps.plan;

ancestors=lcons(gstate,ancestors);

show_sort_group_keys(outerPlanState(gstate),"Group Key",

plan->numCols,plan->grpColIdx,

ancestors,es);

ancestors=list_delete_first(ancestors);

}

show_sort_keys_common(PlanState*planstate,intnkeys,AttrNumber*keycols,

List*ancestors,ExplainState*es)

show_sort_group_keys(PlanState*planstate,constchar*qlabel,

intnkeys,AttrNumber*keycols,

List*ancestors,ExplainState*es)

{

Plan*plan=planstate->plan;

List*context;

result=lappend(result,exprstr);

}

ExplainPropertyList("Sort Key",result,es);

ExplainPropertyList(qlabel,result,es);

}

QUERY PLAN

---------------------------------------------------------------------

HashAggregate

Group Key: $0

InitPlan 1 (returns $0)

-> Limit

-> Index Only Scan Backward using tenk1_unique2 on tenk1

Index Cond: (unique2 IS NOT NULL)

-> Result

(6 rows)

(7 rows)

select distinct max(unique2) from tenk1;

max

QUERY PLAN

----------------------------------------------------------------------------------------------

HashAggregate

Group Key: $0, $1

InitPlan 1 (returns $0)

-> Limit

-> Merge Append

-> Index Only Scan Backward using minmaxtest3i on minmaxtest3 minmaxtest3_1

Index Cond: (f1 IS NOT NULL)

-> Result

(26 rows)

(27 rows)

select distinct min(f1), max(f1) from minmaxtest;

min | max

QUERY PLAN

---------------------

HashAggregate

Group Key: type

-> Seq Scan on t

(2 rows)

(3 rows)

CREATE MATERIALIZED VIEW tm AS SELECT type, sum(amt) AS totamt FROM t GROUP BY type WITH NO DATA;

SELECT relispopulated FROM pg_class WHERE oid = 'tm'::regclass;

Sort

Sort Key: t.type

-> HashAggregate

Group Key: t.type

-> Seq Scan on t

(4 rows)

(5 rows)

CREATE MATERIALIZED VIEW tvm AS SELECT * FROM tv ORDER BY type;

SELECT * FROM tvm;

---------------------------

Aggregate

-> HashAggregate

Group Key: t.type

-> Seq Scan on t

(3 rows)

(4 rows)

CREATE MATERIALIZED VIEW tvvm AS SELECT * FROM tvv;

CREATE VIEW tvvmv AS SELECT * FROM tvvm;

QUERY PLAN

---------------------------------------------------

HashAggregate

Group Key: ((t1.a || t1.b))

-> Append

-> Index Scan using t1_ab_idx on t1

Index Cond: ((a || b) = 'ab'::text)

-> Index Only Scan using t2_pkey on t2

Index Cond: (ab = 'ab'::text)

(6 rows)

(7 rows)

reset enable_seqscan;

reset enable_indexscan;

SELECT 2 AS t, 4 AS x) ss

WHERE x < 4

ORDER BY x;

QUERY PLAN

--------------------------------

QUERY PLAN

--------------------------------------------------------

Sort

Sort Key: ss.x

-> Subquery Scan on ss

Filter: (ss.x < 4)

-> HashAggregate

Group Key: (1), (generate_series(1, 10))

-> Append

-> Result

-> Result

(8 rows)

(9 rows)

SELECT * FROM

(SELECT 1 AS t, generate_series(1,10) AS x

select first_value(max(x)) over (), y

from (select unique1 as x, ten+four as y from tenk1) ss

group by y;

QUERY PLAN

-------------------------------

QUERY PLAN

---------------------------------------------

WindowAgg

-> HashAggregate

Group Key: (tenk1.ten + tenk1.four)

-> Seq Scan on tenk1

(3 rows)

(4 rows)

-- test non-default frame specifications

SELECT four, ten,