On Wed, Jan 13, 2010 at 7:30 PM, Radhika Sambamurti <rs1@speakeasy.net> wrote:
>
> Hi,
> I am currently looking into partitioning a table of which 90% of the lookups
> are for the prior week. It has about 9 million rows and selects are a bit
> slow, since the table is joined to two other tables. I am planning on
> doing a range partition ie each year starting from 2005 will be its own
> partition. So the check constraints will be year based. I have run tests and
> what I see is that the optimizer can find the correct table when I search by
> year, but when I search by say recid (PK), it does a seq scan on every
> single child table.
Do you have an index on each of the tables on recid?
> To have the optimizer recognize the recid, do I need to include that in the
> check constraint?
Not sure. I'd have to test it. I thought the query planner was smart
enough to tell if an index would be useful even if it had to hit it
for each table.
> 2. When you say you wrote a trigger, was it instead of the insert rule?
Yes. using rules results in much worse insert performance than a
trigger. Generally. However, since a rule re-writes queries, if a
single query were to insert many thousands of rows, a rule might be
faster than a trigger, which fires for each row even if they all come
from the same query.
> This is pretty new stuff to me and any insight into this would be helpful.
As Cole Porter would say, "Experiment"...