X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id CCAA330D6D9 for ; Mon, 16 Jun 2003 11:41:59 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 06700-07 for ; Mon, 16 Jun 2003 08:41:49 -0300 (ADT) Received: from nitrogen.id.pl (smtp.id.pl [193.178.214.4]) by svr1.postgresql.org (Postfix) with SMTP id 46B6730EE7F for ; Mon, 16 Jun 2003 08:41:48 -0300 (ADT) Received: (qmail 23865 invoked by uid 0); 16 Jun 2003 11:41:47 -0000 Received: from siaco.id.pl (213.25.114.8) by smtp.id.pl with SMTP; 16 Jun 2003 11:41:47 -0000 Received: (qmail 28110 invoked by uid 1000); 16 Jun 2003 11:41:47 -0000 Date: Mon, 16 Jun 2003 13:41:47 +0200 From: Ryszard Lach To: pgsql-performance@postgresql.org Subject: Re: 7.3 vs 7.2 - different query plan, bad performance Message-ID: <20030616114147.GJ1734@siaco.id.pl> Reply-To: Ryszard Lach References: <20030613184506.GE21614@siaco.id.pl> <20030616063850.GA31813@siaco.id.pl> <373rev0fotgpq2osvlldf41b8flambulk9@4ax.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-2 Content-Disposition: inline In-Reply-To: <373rev0fotgpq2osvlldf41b8flambulk9@4ax.com> X-My-GPG-Key: echo | mail -s "send key pub" siaco@allegro.pl> User-Agent: Mutt/1.5.4i X-Archive-Number: 200306/86 X-Sequence-Number: 2170 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.