Re: Question on moving data to new partitions

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