Re: 7.3 vs 7.2 - different query plan, bad 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.