On Wed, Jan 13, 2010 at 7:30 PM, Radhika Sambamurti <rs1@xxxxxxxxxxxxx> 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"... -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
