From: Vlad Arkhipov <arhipov@dc.baikal.ru>
To: pgsql-performance@postgresql.org
Subject: Re: Optimization idea
Date: Fri, 23 Apr 2010 13:13:49 +0900
Message-ID: <4BD11E7D.50302@dc.baikal.ru> (raw)
In-Reply-To: <4BD0430C.1050308@2ndquadrant.com>
References: <4BD0161A.4010908@dc.baikal.ru>
<4BD0430C.1050308@2ndquadrant.com>
Greg Smith пишет:
> I can't replicate your problem on the current development 9.0; all
> three plans come back with results quickly when I just tried it:
>
> Nested Loop (cost=0.00..50.76 rows=204 width=32) (actual
> time=0.049..0.959 rows=200 loops=1)
> -> Seq Scan on t1 (cost=0.00..1.06 rows=1 width=16) (actual
> time=0.013..0.016 rows=1 loops=1)
> Filter: (t = 2)
> -> Index Scan using t_idx on t2 (cost=0.00..47.66 rows=204
> width=16) (actual time=0.029..0.352 rows=200 loops=1)
> Index Cond: (t2.t = 2)
> Total runtime: 1.295 ms
>
> Nested Loop (cost=0.00..1042.77 rows=20020 width=32) (actual
> time=0.042..0.437 rows=100 loops=1)
> -> Seq Scan on t1 (cost=0.00..1.06 rows=1 width=16) (actual
> time=0.013..0.015 rows=1 loops=1)
> Filter: (id = 3)
> -> Index Scan using t_idx on t2 (cost=0.00..791.45 rows=20020
> width=16) (actual time=0.022..0.164 rows=100 loops=1)
> Index Cond: (t2.t = t1.t)
> Total runtime: 0.608 ms
>
> Bitmap Heap Scan on t2 (cost=16.11..558.73 rows=433 width=16) (actual
> time=0.095..0.674 rows=400 loops=1)
> Recheck Cond: (t = ANY ('{2,3,4}'::bigint[]))
> -> Bitmap Index Scan on t_idx (cost=0.00..16.00 rows=433 width=0)
> (actual time=0.075..0.075 rows=400 loops=1)
> Index Cond: (t = ANY ('{2,3,4}'::bigint[]))
> Total runtime: 1.213 ms
>
Just noticed a mistype in the first query. Here are the correct queries:
create temp table t1 (id bigint, t bigint);
insert into t1 values (1, 1);
insert into t1 values (2, 2);
insert into t1 values (2, 3);
insert into t1 values (2, 4);
insert into t1 values (3, 5);
create temp table t2 (id bigint, t bigint);
insert into t2 (id, t)
select g, 2
from generate_series(1, 200) g;
insert into t2 (id, t)
select g, 3
from generate_series(201, 300) g;
insert into t2 (id, t)
select g, 4
from generate_series(301, 400) g;
insert into t2 (id, t)
select g, 1
from generate_series(401, 100000) g;
insert into t2 (id, t)
select g, 5
from generate_series(100001, 100100) g;
create index t_idx on t2(t);
analyze t1;
analyze t2;
explain analyze
select *
from t2
join t1 on t1.t = t2.t
where t1.id = 2;
explain analyze
select *
from t2
join t1 on t1.t = t2.t
where t1.id = 3;
explain analyze
select *
from t2
where t2.t in (2, 3, 4);
I've just tried these queries on PostgreSQL 9.0alpha4, nothing differs
from PostgreSQL 8.4.
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
Subject: Re: Optimization idea
In-Reply-To: <4BD11E7D.50302@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