How to make use of partitioned table for faster query?

From: Patrick Dung <patrick_dkt(at)yahoo(dot)com(dot)hk>
To: Pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: How to make use of partitioned table for faster query?
Date: 2014-08-30 04:38:23
Message-ID: 1409373503.85066.YahooMailNeo@web193506.mail.sg3.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello Postgresql users,

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?

Thanks and regards,
Patrick

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2014-08-30 04:51:19 Re: How to make use of partitioned table for faster query?
Previous Message Patrick Dung 2014-08-30 04:00:56 Re: Is there a function to save schema history internally?