Re: How to make use of partitioned table for faster query?

[John R Pierce <pierce@xxxxxxxxxxxx>]

On 8/29/2014 10:44 PM, Patrick Dung wrote:

Thanks for reply.

The constraint is like:

  ADD CONSTRAINT attandence_2014p_record_timestamp_check CHECK (record_timestamp >= '2014-01-01 00:00:00'::timestamp without time zone AND record_timestamp < '2015-01-01 00:00:00'::timestamp without time zone);

Let us assume it is a complete year (Jan-Dec) instead of school year.

I thought the data in table partition 2014 can check with the table partition 2014. It do not need to check with other partitions. Same for other partitions.

my query, of course, summarized tardies per calendar year for all students, in (student,year) order.     it probably doesn't take much longer to do that then it does to query for a single student, hah! 

As long as you want all years, you're going to be reading all partitions, so I'm not sure what you're trying to optimize.

if you need to do that query for specific students a lot and it is too slow (test its performance), then you could create a materialized table of of all students,years, tardies, and periodically update it.   you can query rows this materialized view quite fast and efficiently given the right indexes..

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast