Re: Optimizer's issue

On Thu, 24 Apr 2008 03:14:54 +0200, Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)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 = g.id
> where g.isgroup
>
> Hash Join (cost=5.35..11.50 rows=11 width=25) (actual time=0.261..1.755
> rows=294 loops=1)
> Hash Cond: (a.groupid = g.id)
> -> Seq Scan on a (cost=0.00..4.94 rows=294 width=16) (actual
> time=0.047..0.482 rows=294 loops=1)
> -> Hash (cost=5.20..5.20 rows=12 width=9) (actual time=0.164..0.164
> rows=12 loops=1)
> -> Seq Scan on g (cost=0.00..5.20 rows=12 width=9) (actual
> time=0.042..0.136 rows=12 loops=1)
> 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...