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