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

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