Re: partitioning question 1 - Mailing list pgsql-performance

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