title: PostgreSQL - long running SELECT on Big XML - Data in TOAST
body: <p>I am currently analyzing why the application installed on top of PostgreSQL we are using is sometimes soo slow. The logfiles are showing that queries to a specific table have extremely long execution times.</p>
  
  <p>I further found out, that it is one column on the table, which contains XML documents (ranging from a few bytes to one entry with ~7MB XML data), which is the cause of the slow query.</p>
  
  <p>There are 1100 Rows in the table and a </p>
  
  <pre><code>SELECT * FROM mytable
  </code></pre>
  
  <p>has the same query execution time of 5 Seconds as</p>
  
  <pre><code>SELECT [XML-column-only] FROM mytable
  </code></pre>
  
  <p>But in contrast, a</p>
  
  <pre><code>SELECT [id-only] FROM mytable
  </code></pre>
  
  <p>has a query execution time of only 0.2s!</p>
  
  <p>I couldn't produce any noticeable differences depending on the settings (the usual ones, work_mem, shared_buffers,...), there is even almost no difference in comparison between our production server (PostgreSQL 9.3) and running it in a VM on PostgreSQL 9.4 on my workstation PC.</p>
  
  <p>Disk monitoring shows almost no I/O activity for the query.</p>
  
  <p>So the last thing I went to analyze was the Network I/O.</p>
  
  <p>Of course, as mentioned before, it's a lot of data in these XML Column. Total size for the 1100 rows (XML column only) is 36 MB. Divided for the 5 seconds running time, this are a mere 7.2MB/s Network Transfer, which equal around 60MBit/s. Which is a little bit slow, as we all are on Gbit Ethernet, aren't we? :D Windows Taskmanger also show a utilization of 6% for the Networking during the runtime of the query, which is in concordance with the manual calculation from before.</p>
  
  <p>Furthermore, the query execution time is almost linear to the amount of XML data in the table. For testing I deleted the top 10% rows with the largest amount of data on the XML column, and the execution time (now ~18 instead of 36MB to transfer) dropped to 2.5s instead of 5s.</p>
  
  <p>So, to get to the point: What are my options on the database administration side (we cannot touch or change the application itself), to make the simple SELECT for this XML-Data noticeable faster? Is there any bottleneck I didn't take into account yet? Or is this the normal PostgreSQL behaviour?</p>
  
  <p>EDIT: I use pgAdmin III. The Execution plan (explain (analyze, verbose) select * from  gdb_items) shows a much shorter total runtime, than the actual query and the statement duration entry in the log:</p>
  
  <pre><code>Seq Scan on sde.gdb_items  (cost=0.00..181.51 rows=1151 width=1399) (actual time=0.005..0.193 rows=1151 loops=1)
  Output: objectid, uuid, type, name, physicalname, path, url, properties, defaults, datasetsubtype1, datasetsubtype2, datasetinfo1, datasetinfo2, definition, documentation, iteminfo, shape
  Total runtime: 0.243 ms
  </code></pre>