Re: query speed depends on lifetime of frozen db?

On Fri, Sep 27, 2002 at 01:28:13PM +0300, Andriy Tkachuk wrote:
> On Fri, 27 Sep 2002, Martijn van Oosterhout wrote:
> > What is the output of EXPLAIN ANALYSE <query>;
>
> There is EXPLAIN ANALYSE when query is heavy:

Oookaaay. Your query is *evil*. 14 subqueries executed for *each* row of
output!?! I reackon you could improve your query just by rewriting it into a
better form. How can you have 10 subqueries to the same table?

Anyway, the only thing that seems to change is the statistics, which leads
me to beleive that all that is happening is that the planner is reordering some
of your clauses causing it to execute expensive ones it may otherwise be
able to avoid. In your case the default statistics do better than the real
ones.

I think I need to understand your query to help any further.

Snipped plans follow:

> NOTICE: QUERY PLAN:
>
> Sort (cost=26.09..26.09 rows=123 width=89) (actual time=168091.22..168091.31 rows=119 loops=1)
> -> Hash Join (cost=1.27..21.81 rows=123 width=89) (actual time=1404.81..168090.21 rows=119 loops=1)
> -> Seq Scan on users u (cost=0.00..18.07 rows=123 width=81) (actual time=0.14..5.67 rows=119 loops=1)
> SubPlan
> -> Aggregate (cost=215.61..215.61 rows=1 width=4) (actual time=0.43..0.43 rows=1 loops=119)
> -> Index Scan using bill_uid on bills (cost=0.00..215.61 rows=1 width=4) (actual time=0.28..0.42 rows=1 loops=119)
> Total runtime: 168092.92 msec
>
> EXPLAIN
>
>
> and there is, when query is light:
>
> NOTICE: QUERY PLAN:
>
> Sort (cost=28.90..28.90 rows=1 width=136) (actual time=3863.35..3863.43 rows=119 loops=1)
> -> Hash Join (cost=1.27..28.89 rows=1 width=136) (actual time=74.98..3861.69 rows=119 loops=1)
> -> Seq Scan on users u (cost=0.00..27.50 rows=10 width=128) (actual time=0.17..5.26 rows=119 loops=1)
> -> Hash (cost=1.22..1.22 rows=22 width=8) (actual time=0.16..0.16 rows=0 loops=1)
> -> Seq Scan on plans p (cost=0.00..1.22 rows=22 width=8) (actual time=0.03..0.11 rows=22 loops=1)
> SubPlan
> -> Aggregate (cost=12.37..12.37 rows=1 width=4) (actual time=0.69..0.69 rows=1 loops=119)
> -> Index Scan using bill_uid on bills (cost=0.00..12.37 rows=1 width=4) (actual time=0.06..0.62 rows=32 loops=119)
> Total runtime: 3865.89 msec
>
> EXPLAIN
--
Martijn van Oosterhout <kleptog(at)svana(dot)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.