I have been looking around the archives and have not come across anyone
with this situation.
In my database schema I have a table that has N arbitrary child tables
which will increase as new information is added. The information is
separated by 7 day increments ( wk1, wk2, etc. ). The table contains 8
rows, 4 of which have indexes.
Let me preempt my question by saying that after uploading the
information I have run analyze on all of the tables to update the query
planner.
When I left join one of the child tables to itself using a where clause
on the first table, the query plan comes out as expected. It uses the
index to compare the id of the 2 table fields and the where clause
limits the fields returned.
however when I do the same query on the parent table it uses an index
scan on the first instance (ex1) of the table, but a seq scan on the
second instance (ex2).
The query that I am using that displays this behavior is:
parent table (returns seq scan on ex2 )
------------
explain select * from base_table_exposures ex1 left join
base_table_exposures ex2 on ex1.search_id = ex2.search_id where
ex1.sl_domain='cingular.com' limit 100;
child table ( returns index scan on ex2 )
-----------
explain select * from exposures_279 ex1 left join
exposures_279 ex2 on ex1.search_id = ex2.search_id where
ex1.sl_domain='cingular.com' limit 100;
each one of the child tables contains 20+ million rows making a seq scan
not very useful.
has anyone seen this behavior before? how can I 'fix' this problem
without removing seq scan in the config file.
( haven't tried that so i am not sure if it will work ).