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 :

http://explain.depesz.com/s/I3SL

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