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