Re: That EXPLAIN ANALYZE patch still needs work - Mailing list pgsql-hackers

On Thu, Jun 08, 2006 at 05:32:36PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.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.24rows=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.24rows=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.24rows=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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461