<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div><br></div><div><br></div><div><span>Hi all,</span></div><div><br></div><div>I'm experimenting with table partitioning though inheritance. I'm testing a query as follows:</div><div><br></div><div><div><font face="courier new, monospace">explain (analyze, buffers)</font></div><div><font face="courier new, monospace">select <a href="http://response.id/" target="_blank">response.id</a></font></div><div><font face="courier new, monospace">from claim.response</font></div><div><font face="courier new, monospace">where response.account_id = 4766<br></font></div><div><font face="courier new, monospace">and response.expire_timestamp is null</font></div><div><font face="courier new, monospace">and response.create_timestamp >= DATE '2014-08-01'</font></div><div><font face="courier new, monospace">order by create_timestamp;</font></div></div><div><br></div><div>The response table looks like this:</div><div><div><font face="courier new, monospace">"account_id";"integer"</font></div><div><font face="courier new, monospace">"file_type_id";"integer"</font></div><div><font face="courier new, monospace">"receiver_inbound_detail_id";"<wbr>integer"</font></div><div><font face="courier new, monospace">"processing_status_id";"<wbr>integer"</font></div><div><font face="courier new, monospace">"processing";"boolean"</font></div><div><font face="courier new, monospace">"expire_timestamp";"timestamp without time zone"</font></div><div><font face="courier new, monospace">"last_mod_timestamp";"<wbr>timestamp without time zone"</font></div><div><font face="courier new, monospace">"create_timestamp";"timestamp without time zone"</font></div><div><font face="courier new, monospace">"response_trace_nbr";"<wbr>character varying"</font></div><div><font face="courier new, monospace">"posted_timestamp";"timestamp without time zone"</font></div><div><font face="courier new, monospace">"need_to_post";"boolean"</font></div><div><font face="courier new, monospace">"response_message";"text"</font></div><div><font face="courier new, monospace">"worked";"boolean"</font></div><div><font face="courier new, monospace">"response_status_id";"integer"</font></div><div><font face="courier new, monospace">"response_type_id";"integer"</font></div><div><font face="courier new, monospace">"outbound_claim_detail_id";"<wbr>bigint"</font></div><div><font face="courier new, monospace">"id";"bigint"</font></div></div><div><br></div><div>Here are some rowcounts:</div><div><br></div><div><div><font face="courier new, monospace">SELECT count(*) from claim_response.response_<wbr>201408;</font></div><div><font face="courier new, monospace"> count</font></div><div><font face="courier new, monospace">---------</font></div><div><font face="courier new, monospace"> 4585746</font></div><div><font face="courier new, monospace">(1 row)</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace">Time: 7271.054 ms</font></div><div><font face="courier new, monospace">SELECT count(*) from claim_response.response_<wbr>201409;</font></div><div><font face="courier new, monospace"> count</font></div><div><font face="courier new, monospace">---------</font></div><div><font face="courier new, monospace"> 3523370</font></div><div><font face="courier new, monospace">(1 row)</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace">Time: 4341.116 ms</font></div><div><font face="courier new, monospace">SELECT count(*) from claim_response.response_<wbr>201410;</font></div><div><font face="courier new, monospace"> count</font></div><div><font face="courier new, monospace">-------</font></div><div><font face="courier new, monospace"> 154</font></div><div><font face="courier new, monospace">(1 row)</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace">Time: 0.258 ms</font></div></div><div><br></div><div>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.</div><div><br></div><div><div><font face="courier new, monospace">SELECT count(*) from claim.response;</font></div><div><font face="courier new, monospace"> count</font></div><div><font face="courier new, monospace">-----------</font></div><div><font face="courier new, monospace"> 225665512</font></div><div><font face="courier new, monospace">(1 row)</font></div><div><font face="courier new, monospace"><br></font></div><div><font face="courier new, monospace">Time: 685064.637 ms</font></div></div><div><br></div><div><br></div><div>The partitioning is on the create_timestamp field.</div><div><br></div><div>The server is Red Hat Enterprise Linux Server release 6.2 (Santiago) on a VM machine - 8 GB RAM with 2 CPUs:</div><div><br></div><div><div><font face="courier new, monospace">Architecture: x86_64</font></div><div><font face="courier new, monospace">CPU op-mode(s): 32-bit, 64-bit</font></div><div><font face="courier new, monospace">Byte Order: Little Endian</font></div><div><font face="courier new, monospace">CPU(s): 2</font></div><div><font face="courier new, monospace">On-line CPU(s) list: 0,1</font></div><div><font face="courier new, monospace">Thread(s) per core: 1</font></div><div><font face="courier new, monospace">Core(s) per socket: 2</font></div><div><font face="courier new, monospace">CPU socket(s): 1</font></div><div><font face="courier new, monospace">NUMA node(s): 1</font></div><div><font face="courier new, monospace">Vendor ID: GenuineIntel</font></div><div><font face="courier new, monospace">CPU family: 6</font></div><div><font face="courier new, monospace">Model: 44</font></div><div><font face="courier new, monospace">Stepping: 2</font></div><div><font face="courier new, monospace">CPU MHz: 2660.000</font></div><div><font face="courier new, monospace">BogoMIPS: 5320.00</font></div><div><font face="courier new, monospace">L1d cache: 32K</font></div><div><font face="courier new, monospace">L1i cache: 32K</font></div><div><font face="courier new, monospace">L2 cache: 256K</font></div><div><font face="courier new, monospace">L3 cache: 12288K</font></div><div><font face="courier new, monospace">NUMA node0 CPU(s): 0,1</font></div></div><div><br></div><div><br></div><div><br></div><div>2 users, load average: 0.00, 0.12, 0.37</div><div><br></div><div><br></div><div>Please see the following for the explain analysis :</div><div><br></div><div><a href="http://explain.depesz.com/s/I3SL" target="_blank">http://explain.depesz.com/s/<wbr>I3SL</a><br></div><div><br></div><div>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 <a href="http://explain.depesz.com/s/I3SL" target="_blank">explain.depesz.com/s/I3SL</a> page.)</div><div>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. </div><div><br></div><div>Thanks,</div><div><br></div><div style="">John</div></span></body></html>