pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.



view thread (9+ messages)  latest in thread

Message-ID: <20030616114147.GJ1734@siaco.id.pl>
Permalink:  ../20030616114147.GJ1734@siaco.id.pl/
Also on:    postgresql.org/message-id/20030616114147.GJ1734@siaco.id.pl

 · 

reply

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