Query Performance Problem - Mailing list pgsql-performance

<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
asfollows:</div><div><br /></div><div><div><font face="courier new, monospace">explain (analyze,
buffers)</font></div><div><fontface="courier new, monospace">select <a href="http://response.id/"
target="_blank">response.id</a></font></div><div><fontface="courier new, monospace">from
claim.response</font></div><div><fontface="courier new, monospace">where response.account_id = 4766<br
/></font></div><div><fontface="courier new, monospace">and response.expire_timestamp is null</font></div><div><font
face="couriernew, 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><fontface="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><fontface="courier new, monospace">"expire_timestamp";"timestamp
withouttime zone"</font></div><div><font face="courier new, monospace">"last_mod_timestamp";"<wbr />timestamp without
timezone"</font></div><div><font face="courier new, monospace">"create_timestamp";"timestamp without time
zone"</font></div><div><fontface="courier new, monospace">"response_trace_nbr";"<wbr />character
varying"</font></div><div><fontface="courier new, monospace">"posted_timestamp";"timestamp without time
zone"</font></div><div><fontface="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><fontface="courier new,
monospace">"response_status_id";"integer"</font></div><div><fontface="courier new,
monospace">"response_type_id";"integer"</font></div><div><fontface="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><fontface="courier new, monospace">SELECT count(*) from claim_response.response_<wbr
/>201408;</font></div><div><fontface="courier new, monospace">  count</font></div><div><font face="courier new,
monospace">---------</font></div><div><fontface="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><fontface="courier new, monospace">---------</font></div><div><font face="courier new,
monospace"> 3523370</font></div><div><fontface="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="couriernew, monospace">SELECT count(*) from claim_response.response_<wbr />201410;</font></div><div><font
face="couriernew, monospace"> count</font></div><div><font face="courier new, monospace">-------</font></div><div><font
face="couriernew, monospace">   154</font></div><div><font face="courier new, monospace">(1 row)</font></div><div><font
face="couriernew, 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
thumbis that benefits of partitioning occur starting around 100 million rows.</div><div><br /></div><div><div><font
face="couriernew, 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="couriernew, monospace"> 225665512</font></div><div><font face="courier new, monospace">(1
row)</font></div><div><fontface="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_timestampfield.</div><div><br /></div><div>The server is Red Hat Enterprise Linux Server release 6.2 (Santiago)
ona 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><fontface="courier new, monospace">CPU(s):                2</font></div><div><font
face="couriernew, monospace">On-line CPU(s) list:   0,1</font></div><div><font face="courier new, monospace">Thread(s)
percore:    1</font></div><div><font face="courier new, monospace">Core(s) per socket:    2</font></div><div><font
face="couriernew, 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><fontface="courier new, monospace">CPU family:            6</font></div><div><font
face="couriernew, 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="couriernew, 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><fontface="courier new, monospace">L2 cache:              256K</font></div><div><font
face="couriernew, monospace">L3 cache:              12288K</font></div><div><font face="courier new, monospace">NUMA
node0CPU(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'mrelatively new to PostgreSQL, but I've been an Oracle
DBAfor some time. I suspect the I/O may be dragging but I don't know how to dig that information out from here. Please
pointout anything else you can decipher from this. </div><div><br /></div><div>Thanks,</div><div><br /></div><div
style="">John</div></span>