Re: partitioning question 1

On Oct 28, 2010, at 12:44 PM, Joshua D. Drake wrote:
>
> My tests show you are incorrect:
>
>
> part_test=# explain analyze select * from foo join bar using (i) where
> i=9;
> QUERY
> PLAN
> ------------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=34.26..106.76 rows=200 width=20) (actual
> time=0.004..0.004 rows=0 loops=1)
> -> Append (cost=0.00..68.50 rows=20 width=12) (actual
> time=0.004..0.004 rows=0 loops=1)
> -> Seq Scan on foo (cost=0.00..34.25 rows=10 width=12)
> (actual time=0.001..0.001 rows=0 loops=1)
> Filter: (i = 9)
> -> Seq Scan on foo_1 foo (cost=0.00..34.25 rows=10 width=12)
> (actual time=0.000..0.000 rows=0 loops=1)
> Filter: (i = 9)
> -> Materialize (cost=34.26..34.36 rows=10 width=12) (never
> executed)
> -> Seq Scan on bar (cost=0.00..34.25 rows=10 width=12) (never
> executed)
> Filter: (i = 9)
> Total runtime: 0.032 ms
> (10 rows)

strange. my tests don't agree with your tests :

create table foo (i integer not null, j float not null);
create table foo_1 ( check (i >= 0 and i < 10) ) inherits (foo);
create table foo_2 ( check (i >= 10 and i < 20) ) inherits (foo);
create table foo_3 ( check (i >= 20 and i < 30) ) inherits (foo);
create index foo_1_idx on foo_1 (i);
create index foo_2_idx on foo_2 (i);
create index foo_3_idx on foo_3 (i);
insert into foo_1 select generate_series, generate_series from generate_series(0,9);
insert into foo_2 select generate_series, generate_series from generate_series(10,19);
insert into foo_3 select generate_series, generate_series from generate_series(20,29);
create table bar (i integer not null, k float not null);
create index bar_idx on bar (i);
insert into bar select generate_series, -generate_series from generate_series(0,9);
vacuum analyze;
explain analyze select * from foo join bar using (i);

QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.23..42.29 rows=98 width=20) (actual time=0.056..0.118 rows=10 loops=1)
Hash Cond: (public.foo.i = bar.i)
-> Append (cost=0.00..32.70 rows=1970 width=12) (actual time=0.008..0.043 rows=30 loops=1)
-> Seq Scan on foo (cost=0.00..29.40 rows=1940 width=12) (actual time=0.001..0.001 rows=0 loops=1)
-> Seq Scan on foo_1 foo (cost=0.00..1.10 rows=10 width=12) (actual time=0.005..0.008 rows=10 loops=1)
-> Seq Scan on foo_2 foo (cost=0.00..1.10 rows=10 width=12) (actual time=0.003..0.006 rows=10 loops=1)
-> Seq Scan on foo_3 foo (cost=0.00..1.10 rows=10 width=12) (actual time=0.003..0.006 rows=10 loops=1)
-> Hash (cost=1.10..1.10 rows=10 width=12) (actual time=0.025..0.025 rows=10 loops=1)
-> Seq Scan on bar (cost=0.00..1.10 rows=10 width=12) (actual time=0.005..0.013 rows=10 loops=1)
Total runtime: 0.205 ms
(10 rows)

i'm running pg 8.4.3 with constraint_exclusion=on (just to be safe.)

best, b