Re: Problem with inherited tables vs query planning

Dave Golombek wrote:
>
> create table base (file integer, data integer);
> create table child_0 () inherits (base);
> create table child_1 () inherits (base);
> create index child_0_file_index on child_0 using btree (file);
> create index child_1_file_index on child_1 using btree (file);
> create table other (file integer, stuff integer);
> analyze;
> <insert lots of data here>
>
> testing=> explain SELECT * from base join other using (file) where stuff =
> 1;
>                                  QUERY PLAN
> ----------------------------------------------------------------------------
>  Hash Join  (cost=34.27..285.26 rows=597 width=12)
>    Hash Cond: ("outer".file = "inner".file)
>    ->  Append  (cost=0.00..185.34 rows=11934 width=8)
>          ->  Seq Scan on base  (cost=0.00..29.40 rows=1940 width=8)
>          ->  Seq Scan on child_0 base  (cost=0.00..77.98 rows=4998 width=8)
>          ->  Seq Scan on child_1 base  (cost=0.00..77.96 rows=4996 width=8)
                                                            ^^^^^^^^^
Why does it think it's going to match almost 5000 rows here? You don't
say how many rows your test table has, but when I tried to reproduce it
with 10,000 rows (see sql below) it used the child_x indexes. Is
"stuff=1" particularly non-selective in your test?

SQL: INSERT INTO child_1 SELECT g, round(g/2) FROM (SELECT
generate_series(1,10000) as g) as foo;

>    ->  Hash  (cost=34.25..34.25 rows=10 width=8)
>          ->  Seq Scan on other  (cost=0.00..34.25 rows=10 width=8)
>                Filter: (stuff = 1)


--
   Richard Huxton
   Archonet Ltd