DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:mime-version:to:subject:content-type:content-transfer-encoding;
	b=s/c4l6LwhYBeEBzc6p1ctuzbb+WmccuaY3D9NRKwHK/amC5ox8xvG7ukssJGYAwK5Jdh7TjEY/1OaCkkE9Psv5EqPG70g+FsHq4p7/Xwywdf5cZZzMCW2uZUntT+ECGcM6o2A7AcbDTKTO7I4txyLV3buwtCiusn+GZ8D54Y44Q=
Message-ID: <43CDB628.3030007@gmail.com>
Date: Tue, 17 Jan 2006 19:29:44 -0800
From: Hari Warrier <hbhaskaran@gmail.com>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Getting pg to use index on an inherited table (8.1.1)
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I have two tables foobar and foobar2 (which inherits from foobar, no 
extra columns).
foobar2 has all the data (574,576 rows), foobar is empty.
Both foobar and foobar2 have an index on the only column 'id'.  Now I 
have a list of ids in a tmp_ids tables.
A query on foobar2 (child table) uses the index, whereas the same query 
via foobar (parent) doesn't.
Even if I set seq_scan off, it still doesn't use the index on the child 
table while queried via the parent table.

Details are given below. Any help is appreciated.

# analyze foobar;
ANALYZE
# analyze foobar2;
ANALYZE
# explain analyze select * from foobar2 join tmp_ids using (id);
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..3013.69 rows=85856 width=4) (actual 
time=0.038..234.864 rows=44097 loops=1)
   ->  Seq Scan on tmp_ids  (cost=0.00..1.52 rows=52 width=4) (actual 
time=0.008..0.102 rows=52 loops=1)
   ->  Index Scan using foobar2_idx1 on foobar2  (cost=0.00..37.29 
rows=1651 width=4) (actual time=0.007..1.785 rows=848 loops=52)
         Index Cond: (foobar2.id = "outer".id)
 Total runtime: 302.963 ms
(5 rows)

# explain analyze select * from foobar join tmp_ids using (id);
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=1.65..13267.85 rows=149946 width=4) (actual 
time=7.338..3837.060 rows=44097 loops=1)
   Hash Cond: ("outer".id = "inner".id)
   ->  Append  (cost=0.00..8883.16 rows=576716 width=4) (actual 
time=0.012..2797.555 rows=574576 loops=1)
         ->  Seq Scan on foobar  (cost=0.00..31.40 rows=2140 width=4) 
(actual time=0.002..0.002 rows=0 loops=1)
         ->  Seq Scan on foobar2 foobar  (cost=0.00..8851.76 rows=574576 
width=4) (actual time=0.004..1027.422 rows=574576 loops=1)
   ->  Hash  (cost=1.52..1.52 rows=52 width=4) (actual time=0.194..0.194 
rows=52 loops=1)
         ->  Seq Scan on tmp_ids  (cost=0.00..1.52 rows=52 width=4) 
(actual time=0.003..0.094 rows=52 loops=1)
 Total runtime: 3905.074 ms
(8 rows)

# select version();
                                          version
--------------------------------------------------------------------------------------------
 PostgreSQL 8.1.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 
3.3.3 (SuSE Linux)
(1 row)

# \d foobar
    Table "public.foobar"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
Indexes:
    "foobar_idx1" btree (id)

# \d foobar2
   Table "public.foobar2"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
Indexes:
    "foobar2_idx1" btree (id)
Inherits: foobar

# \d tmp_ids
   Table "public.tmp_ids"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |


# set enable_seqscan=off;
SET
# explain analyze select * from foobar join tmp_ids using (id);
                                                                   QUERY 
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=200000001.65..300013267.85 rows=149946 width=4) 
(actual time=7.352..3841.221 rows=44097 loops=1)
   Hash Cond: ("outer".id = "inner".id)
   ->  Append  (cost=100000000.00..200008883.16 rows=576716 width=4) 
(actual time=0.012..2803.547 rows=574576 loops=1)
         ->  Seq Scan on foobar  (cost=100000000.00..100000031.40 
rows=2140 width=4) (actual time=0.003..0.003 rows=0 loops=1)
         ->  Seq Scan on foobar2 foobar  
(cost=100000000.00..100008851.76 rows=574576 width=4) (actual 
time=0.005..1032.148 rows=574576 loops=1)
   ->  Hash  (cost=100000001.52..100000001.52 rows=52 width=4) (actual 
time=0.194..0.194 rows=52 loops=1)
         ->  Seq Scan on tmp_ids  (cost=100000000.00..100000001.52 
rows=52 width=4) (actual time=0.004..0.098 rows=52 loops=1)
 Total runtime: 3909.332 ms
(8 rows)

Output of  "show all" (remember I just turned off seq_scan above)

 enable_bitmapscan               | 
on                                        | Enables the planner's use of 
bitmap-scan plans.
 enable_hashagg                  | 
on                                        | Enables the planner's use of 
hashed aggregation plans.
 enable_hashjoin                 | 
on                                        | Enables the planner's use of 
hash join plans.
 enable_indexscan                | 
on                                        | Enables the planner's use of 
index-scan plans.
 enable_mergejoin                | 
on                                        | Enables the planner's use of 
merge join plans.
 enable_nestloop                 | 
on                                        | Enables the planner's use of 
nested-loop join plans.
 enable_seqscan                  | 
off                                       | Enables the planner's use of 
sequential-scan plans.
 enable_sort                     | 
on                                        | Enables the planner's use of 
explicit sort steps.
 enable_tidscan                  | 
on                                        | Enables the planner's use of 
TID scan plans.