From: Vlad Arkhipov <arhipov@dc.baikal.ru>
To: PFC <lists@peufeu.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizer's issue
Date: Mon, 28 Apr 2008 10:51:38 +0900
Message-ID: <48152DAA.8080206@dc.baikal.ru> (raw)
In-Reply-To: <op.t94ijok4cigqcu@apollo13.peufeu.com>
References: <480FDF0E.3000909@dc.baikal.ru>
<op.t94ijok4cigqcu@apollo13.peufeu.com>
PFC пишет:
> 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 = 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...
>
>
>
I redesigned tables structure and the query seems to be become faster.
You was right, the problem was not in this query.
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: arhipov@dc.baikal.ru, lists@peufeu.com
Subject: Re: Optimizer's issue
In-Reply-To: <48152DAA.8080206@dc.baikal.ru>
* 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