Skip site navigation (1) Skip section navigation (2)

Re: 7.3 vs 7.2 - different query plan, bad performance

From: Ryszard Lach <siaco(at)autograf(dot)pl>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: 7.3 vs 7.2 - different query plan, bad performance
Date: 2003-06-16 11:41:47
Message-ID: 20030616114147.GJ1734@siaco.id.pl (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Jun 16, 2003 at 12:31:08PM +0200, Manfred Koizar wrote:
> 
> Ok, now we have something to work on.
> 
> .)  I guess you are not really interested in
> 
> 	SELECT count(*) FROM v_c;
> 
> If you were, you would simply
> 
> 	SELECT count(*) from b;
> 

That's right.

> Try
> 
> 	EXPLAIN ANALYSE SELECT * FROM v_c;
> 
> and you will see that 7.2 produces a plan that is almost equal to that
> produced by 7.3.

That is not.

I'm, pasting query plan from 7.2 once again (after vacuum analyze):

siaco=# explain analyze select count(*) from v_c;
NOTICE:  QUERY PLAN:
Aggregate  (cost=213.83..213.83 rows=1 width=8) (actual time=90.43..90.43 rows=1 loops=1)
  ->  Hash Join  (cost=29.40..200.63 rows=5281 width=8) (actual time=11.14..78.48 rows=5281 loops=1)
        ->  Seq Scan on b t1  (cost=0.00..78.81 rows=5281 width=4) (actual time=0.01..26.40 rows=5281 loops=1)
        ->  Hash  (cost=25.32..25.32 rows=1632 width=4) (actual time=10.99..10.99 rows=0 loops=1)
              ->  Seq Scan on a t2  (cost=0.00..25.32 rows=1632 width=4) (actual time=0.02..6.30 rows=1632 loops=1)
Total runtime: 90.74 msec
EXPLAIN

> might work.  But I think I don't understand your requirements.  Why
> are you not interested in the children_count for an id that doesn't
> have a parent itself?

The point is, that my tables (and queries) are a 'little' bit more complicated
and I wanted to give as simple example as I could. I think that problem is that
subselects are _much_slower_ executed in 7.3 than in 7.2, just as someone
already wrote here. 


> .)  To answer your original question:  The difference seems to be that
> 7.2 does not evaluate the subselect in the SELECT list, when you are
> only asking for count(*).

That looks reasonably.

Thanks for all your help,

Richard.

-- 
"First they ignore you. Then they laugh at you. Then they
fight you. Then you win." - Mohandas Gandhi.

In response to

Responses

pgsql-performance by date

Next:From: Manfred KoizarDate: 2003-06-16 13:24:52
Subject: Re: 7.3 vs 7.2 - different query plan, bad performance
Previous:From: Manfred KoizarDate: 2003-06-16 10:31:08
Subject: Re: 7.3 vs 7.2 - different query plan, bad performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group