<!--

$Header: /cvsroot/pgsql/doc/src/sgml/perform.sgml,v 1.19 2002/03/2404:31:05 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/perform.sgml,v 1.20 2002/03/2417:11:37 tgl Exp $

-->

<chapter id="performance-tips">

</para>

<para>

Now let's modify the query to add aqualification clause:

Now let's modify the query to add aWHERE condition:

<programlisting>

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 &lt; 1000;

</para>

<para>

Modify the query to restrict thequalification even more:

Modify the query to restrict thecondition even more:

<programlisting>

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 &lt; 50;

QUERY PLAN

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

Index Scan using tenk1_unique1 on tenk1 (cost=0.00..179.33 rows=49 width=148)

IndexFilter: (unique1 &lt; 50)

IndexCond: (unique1 &lt; 50)

</programlisting>

and you will see that if we make the WHERE condition selective

</para>

<para>

Add anothercondition to thequalification:

Add anotherclause to theWHERE condition:

<programlisting>

regression=# EXPLAIN SELECT * FROM tenk1 WHERE unique1 &lt; 50 AND

regression-# stringu1 = 'xxx';

QUERY PLAN

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

Index Scan using tenk1_unique1 on tenk1 (cost=0.00..179.45 rows=1 width=148)

IndexFilter: (unique1 &lt; 50)

IndexCond: (unique1 &lt; 50)

Filter: (stringu1 = 'xxx'::name)

</programlisting>

Nested Loop (cost=0.00..327.02 rows=49 width=296)

-&gt; Index Scan using tenk1_unique1 on tenk1 t1

(cost=0.00..179.33 rows=49 width=148)

IndexFilter: (unique1 &lt; 50)

IndexCond: (unique1 &lt; 50)

-&gt; Index Scan using tenk2_unique2 on tenk2 t2

(cost=0.00..3.01 rows=1 width=148)

IndexFilter: ("outer".unique2 = t2.unique2)

IndexCond: ("outer".unique2 = t2.unique2)

</programlisting>

</para>

affect row count of the outer scan. For the inner scan, the unique2 value of the

current

outer-scan tuple is plugged into the inner index scan

to produce an indexqualification like

to produce an indexcondition like

<literal>t2.unique2 = <replaceable>constant</replaceable></literal>. So we get the

same inner-scan plan and costs that we'd get from, say, <literal>explain select

* from tenk2 where unique2 = 42</literal>. The costs of the loop node are then set

-&gt; Hash (cost=179.33..179.33 rows=49 width=148)

-&gt; Index Scan using tenk1_unique1 on tenk1 t1

(cost=0.00..179.33 rows=49 width=148)

IndexFilter: (unique1 &lt; 50)

IndexCond: (unique1 &lt; 50)

</programlisting>

This plan proposes to extract the 50 interesting rows of <classname>tenk1</classname>

-&gt; Index Scan using tenk1_unique1 on tenk1 t1

(cost=0.00..179.33 rows=49 width=148)

(actual time=0.63..8.91 rows=50 loops=1)

IndexFilter: (unique1 &lt; 50)

IndexCond: (unique1 &lt; 50)

-&gt; Index Scan using tenk2_unique2 on tenk2 t2

(cost=0.00..3.01 rows=1 width=148)

(actual time=0.29..0.32 rows=1 loops=50)

IndexFilter: ("outer".unique2 = t2.unique2)

IndexCond: ("outer".unique2 = t2.unique2)

Total runtime: 31.60 msec

</screen>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.18 2002/03/2416:57:29 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v 1.19 2002/03/2417:11:37 tgl Exp $

PostgreSQL documentation

-->

QUERY PLAN

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

Index Scan using fi on foo (cost=0.00..5.98 rows=1 width=4)

IndexFilter: (i = 4)

IndexCond: (i = 4)

(2 rows)

</computeroutput>

</programlisting>

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

Aggregate (cost=23.93..23.93 rows=1 width=4)

-&gt; Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4)

IndexFilter: (i &lt; 10)

IndexCond: (i &lt; 10)

(3 rows)

</computeroutput>

</programlisting>

* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group

* Portions Copyright (c) 1994-5, Regents of the University of California

*

* $Header: /cvsroot/pgsql/src/backend/commands/explain.c,v 1.74 2002/03/2404:31:07 tgl Exp $

* $Header: /cvsroot/pgsql/src/backend/commands/explain.c,v 1.75 2002/03/2417:11:36 tgl Exp $

*

*/

{

case T_IndexScan:

show_scan_qual(((IndexScan *) plan)->indxqualorig, true,

"IndexFilter",

"IndexCond",

((Scan *) plan)->scanrelid,

outer_plan,

str, indent, es);

break;

case T_NestLoop:

show_upper_qual(((NestLoop *) plan)->join.joinqual,

"JoinCond",

"JoinFilter",

"outer", OUTER, outerPlan(plan),

"inner", INNER, innerPlan(plan),

str, indent, es);

"inner", INNER, innerPlan(plan),

str, indent, es);

show_upper_qual(((MergeJoin *) plan)->join.joinqual,

"JoinCond",

"JoinFilter",

"outer", OUTER, outerPlan(plan),

"inner", INNER, innerPlan(plan),

str, indent, es);

"inner", INNER, innerPlan(plan),

str, indent, es);

show_upper_qual(((HashJoin *) plan)->join.joinqual,

"JoinCond",

"JoinFilter",

"outer", OUTER, outerPlan(plan),

"inner", INNER, innerPlan(plan),

str, indent, es);