Re: query speed depends on lifetime of frozen db? - Mailing list pgsql-general

Ok. Please send stuff like this to the list, that's what it's there for.

Secondly, I have no idea why this is happening, you'll have to find someone
else who knows...

On Sat, Sep 28, 2002 at 02:02:19PM +0300, Andriy Tkachuk wrote:
> > what does:
> >
> > EXPLAIN ANALYZE calc_account (u.uid, 1030827600);
> >
> > do when the big query is both fast and when it's slow. I reackon that
> > function is where the time is taken.
>
> Guys, there are many interesting things:
>
> 1: (db is clean: just restored from dump)
>
> calc_account have such query:
>
> bb=# EXPLAIN ANALYZE select sum(cost) as cost from bills b,users u where u.parent= 12608 and b.dat between 0 and
1030827600and b.uid = u.uid; 
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=84.54..84.54 rows=1 width=12) (actual time=0.96..0.96 rows=1 loops=1)
>   ->  Nested Loop  (cost=0.00..84.51 rows=13 width=12) (actual time=0.94..0.94 rows=0 loops=1)
>         ->  Seq Scan on users u  (cost=0.00..22.50 rows=5 width=4) (actual time=0.93..0.93 rows=0 loops=1)
>         ->  Index Scan using bill_uid on bills b  (cost=0.00..12.37 rows=3 width=8)
> Total runtime: 1.13 msec
>
> EXPLAIN
> bb=# ANALYZE bills;
> ANALYZE
> bb=# EXPLAIN ANALYZE select sum(cost) as cost from bills b,users u where u.parent= 12608 and b.dat between 0 and
1030827600and b.uid = u.uid; 
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=3391.67..3391.67 rows=1 width=12) (actual time=2033.08..2033.09 rows=1 loops=1)
>   ->  Hash Join  (cost=22.51..3386.47 rows=2079 width=12) (actual time=2033.06..2033.06 rows=0 loops=1)
>         ->  Seq Scan on bills b  (cost=0.00..2922.07 rows=83177 width=8) (actual time=0.10..1446.82 rows=87125
loops=1)
>         ->  Hash  (cost=22.50..22.50 rows=5 width=4) (actual time=1.87..1.87 rows=0 loops=1)
>               ->  Seq Scan on users u  (cost=0.00..22.50 rows=5 width=4) (actual time=1.85..1.85 rows=0 loops=1)
> Total runtime: 2033.25 msec
>
> EXPLAIN
>
> WOW!!!
>
> BUT!
> 2. (again after dropping & restoring db)
>
> bb=# EXPLAIN ANALYZE SELECT calc_account(12608, 1030827600);
> NOTICE:  QUERY PLAN:
>
> Result  (cost=0.00..0.01 rows=1 width=0) (actual time=251.83..251.83 rows=1 loops=1)
> Total runtime: 251.89 msec
>
> EXPLAIN
> bb=# ANALYZE bills;
> ANALYZE
> bb=# EXPLAIN ANALYZE SELECT calc_account(12608, 1030827600);
> NOTICE:  QUERY PLAN:
>
> Result  (cost=0.00..0.01 rows=1 width=0) (actual time=284.00..284.01 rows=1 loops=1)
> Total runtime: 284.14 msec
>
> EXPLAIN
>
>
> and just after ANALYZING bills the main query still performs fast.
>
> bb=# ANALYZE users;
> ANALYZE
> bb=# EXPLAIN ANALYZE SELECT calc_account(12608, 1030827600);
> NOTICE:  QUERY PLAN:
>
> Result  (cost=0.00..0.01 rows=1 width=0) (actual time=199.52..199.53 rows=1 loops=1)
> Total runtime: 199.58 msec
>
> EXPLAIN
>
>
> and now the main query overloads.
>

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.