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

[Patrick Dung <patrick_dkt@xxxxxxxxxxxx>]

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.

On Saturday, August 30, 2014 12:52 PM, John R Pierce <pierce@xxxxxxxxxxxx> wrote:

On 8/29/2014 9:38 PM, Patrick Dung wrote:

Suppose the table 'attendance' is very large:

id bigint

student_name varchar

late boolean

record_timestamp timestamp

The table is already partitioned by year (attendance_2012p, attendance_2013p, ...).

I would like to count the number of lates by year.

Instead of specifying the partition tables name:

select count(*) from attendance_2012p where student_name="Student A" and late='true';

select count(*) from attendance_2013p where student_name="Student A" and late='true';
select count(*) from attendance_2014p where student_name="Student A" and late='true';
...

Is it possible to query the master table attendance), and the query could make use of the partitioned table for faster query?

select student_name as student,extract(year from record_timestamp) as year, count(*) as count_lates from attendance where late group by 1,2;

now, if your partitioning is by school year, that will be somewhat trickier.   what are your partitioning _expression_ ?

as far as faster, well, your query has to read from all of the tables.   there won't be any speedup from partition pruning...

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