Hi all,
I'm experimenting with table partitioning though inheritance. I'm testing a query as follows:
explain (analyze, buffers)
select response.id
from claim.response
where response.account_id = 4766
and response.expire_timestamp is null
and response.create_timestamp >= DATE '2014-08-01'
order by create_timestamp;
The response table looks like this:
"account_id";"integer"
"file_type_id";"integer"
"receiver_inbound_detail_id";"integer"
"processing_status_id";"integer"
"processing";"boolean"
"expire_timestamp";"timestamp without time zone"
"last_mod_timestamp";"timestamp without time zone"
"create_timestamp";"timestamp without time zone"
"response_trace_nbr";"character varying"
"posted_timestamp";"timestamp without time zone"
"need_to_post";"boolean"
"response_message";"text"
"worked";"boolean"
"response_status_id";"integer"
"response_type_id";"integer"
"outbound_claim_detail_id";"bigint"
"id";"bigint"
Here are some rowcounts:
SELECT count(*) from claim_response.response_201408;
count
---------
4585746
(1 row)
Time: 7271.054 ms
SELECT count(*) from claim_response.response_201409;
count
---------
3523370
(1 row)
Time: 4341.116 ms
SELECT count(*) from claim_response.response_201410;
count
-------
154
(1 row)
Time: 0.258 ms
The entire table has 225,665,512 rows. I read that a partitioning rule of thumb is that benefits of partitioning occur starting around 100 million rows.
SELECT count(*) from claim.response;
count
-----------
225665512
(1 row)
Time: 685064.637 ms
The partitioning is on the create_timestamp field.
The server is Red Hat Enterprise Linux Server release 6.2 (Santiago) on a VM machine - 8 GB RAM with 2 CPUs:
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 2
On-line CPU(s) list: 0,1
Thread(s) per core: 1
Core(s) per socket: 2
CPU socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 44
Stepping: 2
CPU MHz: 2660.000
BogoMIPS: 5320.00
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 12288K
NUMA node0 CPU(s): 0,1
2 users, load average: 0.00, 0.12, 0.37
Please see the following for the explain analysis :
I'm trying to understand why I'm getting the yellow, orange, and red on the inclusive, and the yellow on the exclusive. (referring to the explain.depesz.com/s/I3SL page.)
I'm relatively new to PostgreSQL, but I've been an Oracle DBA for some time. I suspect the I/O may be dragging but I don't know how to dig that information out from here. Please point out anything else you can decipher from this.
Thanks,
John
