Date: Thu, 24 Apr 2008 18:24:50 +0200
To: "Vlad Arkhipov" <arhipov@dc.baikal.ru>, pgsql-performance@postgresql.org
Subject: Re: Optimizer's issue
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <480FDF0E.3000909@dc.baikal.ru>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.t94ijok4cigqcu@apollo13.peufeu.com>
In-Reply-To: <480FDF0E.3000909@dc.baikal.ru>
User-Agent: Opera Mail/9.24 (Linux)

On Thu, 24 Apr 2008 03:14:54 +0200, Vlad Arkhipov <arhipov@dc.baikal.ru>=
  =

wrote:

> I found strange issue in very simple query. Statistics for all columns=

> is on the level 1000 but I also tried other levels.
>
> create table g (
>   id bigint primary key,
>   isgroup boolean not null);
>
> create table a (
>   groupid bigint references g(id),
>   id bigint,
>   unique(id, groupid));
>
> analyze g;
> analyze a;
>
> select count(*) from a
> 294
>
> select count(*) from g
> 320
>
> explain analyze
> select *
> from g
>   join a on a.groupid =3D g.id
> where g.isgroup
>
> Hash Join  (cost=3D5.35..11.50 rows=3D11 width=3D25) (actual time=3D0.=
261..1.755
> rows=3D294 loops=3D1)
>   Hash Cond: (a.groupid =3D g.id)
>   ->  Seq Scan on a  (cost=3D0.00..4.94 rows=3D294 width=3D16) (actual=

> time=3D0.047..0.482 rows=3D294 loops=3D1)
>   ->  Hash  (cost=3D5.20..5.20 rows=3D12 width=3D9) (actual time=3D0.1=
64..0.164
> rows=3D12 loops=3D1)
>         ->  Seq Scan on g  (cost=3D0.00..5.20 rows=3D12 width=3D9) (ac=
tual
> time=3D0.042..0.136 rows=3D12 loops=3D1)
>               Filter: isgroup
> Total runtime: 2.225 ms

	You should really put an EXPLAIN ANALYZE of your big query.

	This little query plan seems OK to me.
	Two very small tables, ok, hash'em, it's the best.
	Now, of course if it is repeated for every row in your JOIN, you have a=
  =

problem.
	The question is, why is it repeated for every row ?
	This cannot be answered without seeing the whole query.

	Another question would be, is there a way to structure the tables  =

differently ?
	Again, this cannot be answered without seeing the whole query, and some=
  =

explanation about what the data & fields mean.

	Please provide more information...