Re: That EXPLAIN ANALYZE patch still needs work

On Thu, Jun 08, 2006 at 05:32:36PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > The powerbook tests were not very repeatable at 100,000 rows, so I
> > bumped up to 1M. The results still aren't very repeatable...
>
> Hmm, I suspect you pushed it into the realm where it's doing actual
> I/O ... which we don't want for this particular test. (Although it's
> worth noting that if your query *is* doing actual I/O, the EXPLAIN
> overhead certainly ought to be down in the noise.)

Hmm... I guess it depends on how smart the OS cache is; the table is
36MB; 4406 pages. But shared buffers is 3000...

Bumping shared_buffers up to 5k, there is a bigger difference, but these
numbers are still all over the place, so I don't know that they're very
trust-worthy.

decibel=# explain analyze select count(*) from i;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Aggregate (cost=16905.05..16905.06 rows=1 width=0) (actual time=5398.625..5398.626 rows=1 loops=1)
-> Seq Scan on i (cost=0.00..14405.24 rows=999924 width=0) (actual time=0.034..3967.927 rows=1000000 loops=1)
Total runtime: 5398.871 ms
(3 rows)

Time: 5400.900 ms
decibel=# explain analyze select count(*) from i;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Aggregate (cost=16905.05..16905.06 rows=1 width=0) (actual time=5626.671..5626.671 rows=1 loops=1)
-> Seq Scan on i (cost=0.00..14405.24 rows=999924 width=0) (actual time=0.035..3875.641 rows=1000000 loops=1)
Total runtime: 5626.904 ms
(3 rows)

Time: 5628.896 ms
decibel=# explain analyze select count(*) from i;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Aggregate (cost=16905.05..16905.06 rows=1 width=0) (actual time=5137.549..5137.549 rows=1 loops=1)
-> Seq Scan on i (cost=0.00..14405.24 rows=999924 width=0) (actual time=0.020..3440.034 rows=1000000 loops=1)
Total runtime: 5137.707 ms
(3 rows)

Time: 5139.178 ms
decibel=# select count(*) from i;
count
---------
1000000
(1 row)

Time: 4357.443 ms
decibel=# select count(*) from i;
count
---------
1000000
(1 row)

Time: 4251.208 ms
decibel=# select count(*) from i;
count
---------
1000000
(1 row)

Time: 4712.912 ms
decibel=# select count(*) from i;
count
---------
1000000
(1 row)

Time: 4479.278 ms
decibel=#
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461