Table Inheritance, Analyze and Seq Scans

From: Terrance Louden <tlouden(at)did-it(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Table Inheritance, Analyze and Seq Scans
Date: 2005-10-06 15:51:53
Message-ID: 1128613913.634.28.camel@terrance.did
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


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 ).

Browse pgsql-novice by date

  From Date Subject
Next Message Frances Collier 2005-10-06 16:12:44 Re: contains function
Previous Message Michael Fuhr 2005-10-06 14:58:55 Re: stupid SQL question, how reach different rows of two almost same tables