Skip site navigation (1) Skip section navigation (2)

Re: Optimizer's issue

From: PFC <lists(at)peufeu(dot)com>
To: "Vlad Arkhipov" <arhipov(at)dc(dot)baikal(dot)ru>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimizer's issue
Date: 2008-04-24 16:24:50
Message-ID: op.t94ijok4cigqcu@apollo13.peufeu.com (view raw or flat)
Thread:
Lists: pgsql-performance
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...



In response to

Responses

pgsql-performance by date

Next:From: PFCDate: 2008-04-24 16:56:34
Subject: Re: Question about disk IO an index use and seeking advice
Previous:From: Bruce MomjianDate: 2008-04-24 15:46:49
Subject: Re: Sun Talks about MySQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group