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

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