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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
