StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If clustering and partitioning isn't working, I start to suspect you have some serious issue with your storage system. 10s of seconds to heap scan 35K rows is several orders of magnitude too slow. What version of Postgres are you using? What does your storage look like? Check your iostats.</p> <p>I set up an experiment on a smallish VM (1 fractional cpu, 1GB mem, NFS disk mount) with Pg 9.0.4 making your table and index and injecting 1000 batches of 36000 records each.</p> <pre><code>insert into r(loop,value,xfrom,xto,material,transport,pk) select 0,0,0,0,0,0,i from generate_series(0,35999) i; insert into r(loop,value,xfrom,xto,material,transport,pk) select 1,0,0,0,0,0,i from generate_series(36000,71999) i; ... </code></pre> <p>Running your select on any batch is consistently under 40ms:</p> <pre><code>explain analyze select exp(least(709,a.value)), a.xfrom, a.xto, a.material,a.transport from r a where a.loop=31; Index Scan using "resultTable_idx_mo" on r a (cost=0.00..1596.35 rows=37680 width=21) (actual time=0.087..34.038 rows=36000 loops=1) Index Cond: (loop = 31) Total runtime: 36.332 ms explain analyze select exp(least(709,a.value)), a.xfrom, a.xto,a.material,a.transport from r a where a.loop=87; Index Scan using "resultTable_idx_mo" on r a (cost=0.00..1421.35 rows=33480 width=21) (actual time=0.105..37.357 rows=36000 loops=1) Index Cond: (loop = 87) Total runtime: 39.365 ms </code></pre> <p>Note my plans come up with ordinary IndexScan's instead of BitmapScans followed by HeapScans. Have you tweaked with plan optimization config to influence the plan?</p> <p>In your explains, I note the estimated row count is far less than the actual row count (1649 vs 34640). This indicates you have inaccurate statistics on the table. You should run <code>ANAYLZE resultTable;</code></p> <p>I believe there's something badly busted in your postgres config or your storage system when these simple index scan selects take more than a few tens of ms. Is there other activity on this db when you run your queries? Perhaps your results are competition for resources with other queries?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload