From: Szymon Guz <mabewlun@gmail.com>
To: Tadipathri Raghu <traghu.dba@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizer showing wrong rows in plan
Date: Sun, 28 Mar 2010 09:02:14 +0200
Message-ID: <e4edc9361003280002o627b710dhe89c98c18b5d1db3@mail.gmail.com> (raw)
In-Reply-To: <645d9d71003272351x766707c7sadde9aa77bc1c0d3@mail.gmail.com>
References: <645d9d71003272351x766707c7sadde9aa77bc1c0d3@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mabewlun@gmail.com, traghu.dba@gmail.com
Subject: Re: Optimizer showing wrong rows in plan
In-Reply-To: <e4edc9361003280002o627b710dhe89c98c18b5d1db3@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox