Обсуждение: Query Performance Problem
<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>
2014-10-21 10:57 GMT-02:00 <john@jpm-cola.com>:
Hi all,I'm experimenting with table partitioning though inheritance. I'm testing a query as follows:explain (analyze, buffers)select response.idfrom claim.responsewhere response.account_id = 4766and response.expire_timestamp is nulland 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 msSELECT count(*) from claim_response.response_201409;count---------3523370(1 row)Time: 4341.116 msSELECT count(*) from claim_response.response_201410;count-------154(1 row)Time: 0.258 msThe 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 msThe 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_64CPU op-mode(s): 32-bit, 64-bitByte Order: Little EndianCPU(s): 2On-line CPU(s) list: 0,1Thread(s) per core: 1Core(s) per socket: 2CPU socket(s): 1NUMA node(s): 1Vendor ID: GenuineIntelCPU family: 6Model: 44Stepping: 2CPU MHz: 2660.000BogoMIPS: 5320.00L1d cache: 32KL1i cache: 32KL2 cache: 256KL3 cache: 12288KNUMA node0 CPU(s): 0,12 users, load average: 0.00, 0.12, 0.37Please 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
Hi John,
Dont know about the colors, but the Stats tab looks fine. You've got yourself 5 Index Scans, which are a very fast way to dig data.
I noticed you've also cast your filter field "(create_timestamp >= '2014-08-01'::date)". As far as I know, Postgresql doesn't need this kind of explicit conversion. You would be fine with just "(create_timestamp >= '2014-08-01')".
Regards,
Felipe