From: Ryszard Lach <siaco@autograf.pl>
To: pgsql-performance@postgresql.org
Subject: Re: 7.3 vs 7.2 - different query plan, bad performance
Date: Mon, 16 Jun 2003 13:41:47 +0200
Message-ID: <20030616114147.GJ1734@siaco.id.pl> (raw)
In-Reply-To: <373rev0fotgpq2osvlldf41b8flambulk9@4ax.com>
References: <20030613184506.GE21614@siaco.id.pl>
<ugipevc89eaqk90s23n8kd2iut5uvskpk4@4ax.com>
<20030616063850.GA31813@siaco.id.pl>
<373rev0fotgpq2osvlldf41b8flambulk9@4ax.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: siaco@autograf.pl
Subject: Re: 7.3 vs 7.2 - different query plan, bad performance
In-Reply-To: <20030616114147.GJ1734@siaco.id.pl>
References: <20030613184506.GE21614@siaco.id.pl> <ugipevc89eaqk90s23n8kd2iut5uvskpk4@4ax.com> <20030616063850.GA31813@siaco.id.pl> <373rev0fotgpq2osvlldf41b8flambulk9@4ax.com> <20030616114147.GJ1734@siaco.id.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox