Date: Tue, 13 Dec 2005 18:18:19 +0300
From: =?utf-8?B?0JrQu9GO0YfQvdC40LrQvtCyINCQLtChLg==?= <alexs@analytic.mv.ru>
To: pgsql-performance@postgresql.org
Subject: query from partitions
Message-ID: <20051213151818.GA10021@mail.analytic.mv.ru>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.4.2.1i

Hi.

create table device(id int);

insert into device values(1);
insert into device values(2);
.....
insert into device values(250);

create table base (
					id int,
					data float,
					datatime timestamp,
					mode int,
					status int);

create table base_1 (
    				check ( id = 1 and datatime >= DATE '2005-01-01' 
					and datatime < DATE '2006-01-01' )
					) INHERITS (base);

create table base_2 (
                    check ( id = 2 and datatime >= DATE '2005-01-01'
                    and datatime < DATE '2006-01-01' )
                    ) INHERITS (base);
....
create table base_250


And
select * from base 
	where id in (1,2) and datatime between '2005-05-15' and '2005-05-17';
10 seconds

select * from base
	where id in (select id from device where id = 1 or id = 2) and
	datatime between '2005-05-15' and '2005-05-17';
10 minits

Why?

-- 
mailto: alexs@analytic.mv.ru