DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=AselDw0uU290uKoy+R7LTmZDf9LY5Mwi+t3c4n08J7AYMFyecbDymP6HKujFs71hGd
	O4Ae6rCg7mqarZTlrpvfuTDe7oHe5xk9hIjsgXK0QopBT2po3pX7Qww5PjyESkEkrL0Q
	F5Q94jGAXP16g7v9mMJKwKcb/LZdCZL69wA9s=
MIME-Version: 1.0
In-Reply-To: <645d9d71003272351x766707c7sadde9aa77bc1c0d3@mail.gmail.com>
References: <645d9d71003272351x766707c7sadde9aa77bc1c0d3@mail.gmail.com>
Date: Sun, 28 Mar 2010 09:02:14 +0200
Message-ID: <e4edc9361003280002o627b710dhe89c98c18b5d1db3@mail.gmail.com>
Subject: Re: Optimizer showing wrong rows in plan
From: Szymon Guz <mabewlun@gmail.com>
To: Tadipathri Raghu <traghu.dba@gmail.com>
Cc: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=0016e6d46d7df112830482d6f945

--0016e6d46d7df112830482d6f945
Content-Type: text/plain; charset=UTF-8

2010/3/28 Tadipathri Raghu <traghu.dba@gmail.com>

> Hi All,
>
> Example on optimizer
> ===============
> postgres=# create table test(id int);
> CREATE TABLE
> postgres=# insert into test VALUES (1);
> INSERT 0 1
> postgres=# select * from test;
>  id
> ----
>   1
> (1 row)
> postgres=# explain select * from test;
>                        QUERY PLAN
> --------------------------------------------------------
>  Seq Scan on test  (cost=0.00..34.00 *rows=2400* width=4)
> (1 row)
> In the above, example the optimizer is retreiving those many rows where
> there is only one row in that table. If i analyze am geting one row.
>

No, the optimizer is not retrieving anything, it just assumes that there are
2400 rows because that is the number of rows that exists in the statictics
for this table. The optimizer just tries to find the best plan and to
optimize the query plan for execution taking into consideration all
information that can be found for this table (it also looks in the
statistics information about rows from this table).


>
> postgres=# ANALYZE test;
> ANALYZE
> postgres=# explain select * from test;
>                      QUERY PLAN
> ----------------------------------------------------
>  Seq Scan on test  (cost=0.00..1.01 *rows=1* width=4)
> (1 row)
>
> My question here is, what it retreiving as rows when there is no such. One
> more thing, if i wont do analyze and run the explain plan for three or more
> times, then catalogs getting updated automatically and resulting the correct
> row as 1.
>
>

Now ANALYZE changed the statistics for this table and now the planner knows
that there is just one row. In the background there can work autovacuum so
it changes rows automatically (the autovacuum work characteristic depends on
the settings for the database).


> Q2. Does explain , will update the catalogs automatically.
>
>

No, explain doesn't update table's statistics.


regards
Szymon Guz

--0016e6d46d7df112830482d6f945
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div class=3D"gmail_quote">2010/3/28 Tadipathri Raghu <span dir=3D"ltr">&lt=
;<a href=3D"mailto:traghu.dba@gmail.com">traghu.dba@gmail.com</a>&gt;</span=
><br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-le=
ft:1px #ccc solid;padding-left:1ex;">
<div>Hi All,</div>
<div>=C2=A0</div>
<div>Example on optimizer</div>
<div>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D</div>
<div>postgres=3D# create table test(id int);<br>CREATE TABLE<br>postgres=3D=
# insert into test VALUES (1);<br>INSERT 0 1<br>postgres=3D# select * from =
test;<br>=C2=A0id<br>----<br>=C2=A0 1<br>(1 row)</div>
<div>postgres=3D# explain select * from test;<br>=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 QUERY PLAN<br>---------------------------=
-----------------------------<br>=C2=A0Seq Scan on test=C2=A0 (cost=3D0.00.=
.34.00 <strong>rows=3D2400</strong> width=3D4)<br>

(1 row)<br></div>
<div>In the above, example the optimizer is retreiving those many rows wher=
e there is only one row in that table. If i analyze am geting one row.</div=
></blockquote><div><br></div><div>No, the optimizer is not retrieving anyth=
ing, it just assumes that there are 2400 rows because that is the number of=
 rows that exists in the statictics for this table. The optimizer just trie=
s to find the best plan and to optimize the query plan for execution taking=
 into consideration all information that can be found for this table (it al=
so looks in the statistics information about rows from this table).</div>
<div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8=
ex;border-left:1px #ccc solid;padding-left:1ex;">
<div>=C2=A0</div>
<div>postgres=3D# ANALYZE test;<br>ANALYZE<br>postgres=3D# explain select *=
 from test;<br>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 QUERY PLAN<br>=
----------------------------------------------------<br>=C2=A0Seq Scan on t=
est=C2=A0 (cost=3D0.00..1.01 <strong>rows=3D1</strong> width=3D4)<br>

(1 row)</div>
<div>=C2=A0</div>
<div>My question here is, what it retreiving as rows when there is no such.=
 One more thing, if i wont do analyze and run the explain plan for three or=
 more times, then catalogs getting updated automatically and resulting the =
correct row as 1. </div>


<div>=C2=A0</div></blockquote><div><br></div><div>Now ANALYZE changed the s=
tatistics for this table and now the planner knows that there is just one r=
ow. In the background there can work autovacuum so it changes rows automati=
cally (the autovacuum work characteristic depends on the settings for the d=
atabase).</div>
<div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8=
ex;border-left:1px #ccc solid;padding-left:1ex;">
<div>Q2. Does explain , will update the catalogs automatically.</div>
<div>=C2=A0</div></blockquote><div><br></div><div>No, explain doesn&#39;t u=
pdate table&#39;s statistics.</div><div><br></div><div><br></div><div>regar=
ds</div><div>Szymon Guz</div></div>

--0016e6d46d7df112830482d6f945--