Re: performance question

On Tue, 28 Aug 2001, Reinoud van Leeuwen wrote:

> Can somebody explain to me:
> 
> > radius=# explain select count (radiuspk) from radius ;
> > NOTICE:  QUERY PLAN:
> >
> > Aggregate  (cost=12839.79..12839.79 rows=1 width=8)
> >   ->  Seq Scan on radius  (cost=0.00..11843.43 rows=398543 width=8)
> >
> > EXPLAIN
> 
> 
> This query answers me *instantly* after hitting return
> 
> > radius=# select count (radiuspk) from radius ;
> >  count
> >  --------
> >   398543
> >  (1 row)
> 
> This query takes about 3 seconds. But the query plan *already* knows the 
> number of rows ("rows=398543"). So why does it take 3 seconds. Is my 
> assumption correct that the optimiser still can be optimized a little? :-)

Not in this case.  The row numbers from explain are just estimates 
from the last vacuum.  As you modify the table, the estimated rows
will be off.

For example:
sszabo=> create table a (a int);
CREATE
sszabo=> insert into a values (100);
INSERT 808899 1
sszabo=> insert into a values (101);
INSERT 808900 1
sszabo=> explain select count(a) from a;
NOTICE:  QUERY PLAN:

Aggregate  (cost=22.50..22.50 rows=1 width=4) ->  Seq Scan on a  (cost=0.00..20.00 rows=1000 width=4)

EXPLAIN
sszabo=> vacuum analyze a;
VACUUM
sszabo=> explain select count(a) from a;
NOTICE:  QUERY PLAN:

Aggregate (cost=1.02..1.02 rows=1 width=4) ->  Seq Scan on a  (cost=0.00..1.02 rows=2 width=4)

EXPLAIN
sszabo=> insert into a values (102);
INSERT 808902 1
sszabo=> explain select count(a) from a;
NOTICE:  QUERY PLAN:

Aggregate  (cost=1.02..1.02 rows=1 width=4) ->  Seq Scan on a  (cost=0.00..1.02 rows=2 width=4)

EXPLAIN
sszabo=> vacuum analyze a;
VACUUM
sszabo=> explain select count(a) from a;
NOTICE:  QUERY PLAN:

Aggregate  (cost=1.04..1.04 rows=1 width=4) ->  Seq Scan on a  (cost=0.00..1.03 rows=3 width=4)

EXPLAIN