Thread: performance question
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? :-)
Reinoud (not that this is a real problem, just wondering)
Reinoud van Leeuwen writes:
> > 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").
This is only an estimate which is only updated by VACUUM. Presumably you
didn't add or remove any rows since your last VACUUM.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
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
> 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.
Yes, I just found out that somebody else is running a script on our test
server that vacuums all databases each night. That explains a lot.
Thanx for thinking with me
Reinoud