StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POReally slow performance in postgres
text
Body
copied!<p>I have a table where i only insert rows, never delete. On each loop i insert around 36k rows.</p> <p>I need to get rows from this table to perfom operations. The problem is on every loop the query perfomance is really really poor.</p> <p>For example, on the loop 31:</p> <pre><code>explain analyze select exp(least(709,a.value)), a.from, a.to,a.material,a.transport from resultTable a where a.loop=31; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on resultTable a (cost=36.58..4431.79 rows=2425 width=48) (actual time=7.351..33894.217 rows=34640 loops=1) Recheck Cond: (loop = 31) -> Bitmap Index Scan on "resultTable_idx_mo" (cost=0.00..35.97 rows=2425 width=0) (actual time=4.880..4.880 rows=34640 loops=1) Index Cond: (loop = 31) Total runtime: 33897.070 ms (5 rows) </code></pre> <p>For the loop 43:</p> <pre><code>explain analyze select exp(least(709,a.value)), a.from, a.to,a.material,a.transport from resultTable a where a.loop=43; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on resultTable a (cost=36.58..4431.79 rows=2425 width=48) (actual time=10.129..125460.445 rows=34640 loops=1) Recheck Cond: (loop = 43) -> Bitmap Index Scan on "resultTable_idx_mo" (cost=0.00..35.97 rows=2425 width=0) (actual time=4.618..4.618 rows=34640 loops=1) Index Cond: (loop 43) Total runtime: 125463.516 ms (5 rows) </code></pre> <p>The time is growing as exponential. I am doing VACUUM and REINDEX in every loop (I have tried also without, but results are same).</p> <p>Any idea how to improve the time?</p> <p>Thanks in advance.</p> <p>After partition:</p> <pre><code> QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Result (cost=14.47..2686.29 rows=1649 width=48) (actual time=18.562..220124.597 rows=34640 loops=1) -> Append (cost=14.47..2682.17 rows=1649 width=48) (actual time=5.189..32.743 rows=34640 loops=1) -> Bitmap Heap Scan on resultTable a (cost=14.47..1655.44 rows=866 width=48) (actual time=0.008..0.008 rows=0 loops=1) Recheck Cond: (loop = 60) -> Bitmap Index Scan on "resultTable_idx_mo" (cost=0.00..14.26 rows=866 width=0) (actual time=0.006..0.006 rows=0 loops=1) Index Cond: (loop = 60) -> Bitmap Heap Scan on result_table_child_70 a (cost=8.82..1026.73 rows=783 width=48) (actual time=5.181..29.068 rows=34640 loops=1) Recheck Cond: (loop = 60) -> Bitmap Index Scan on resultTable_child_70_idx (cost=0.00..8.63 rows=783 width=0) (actual time=4.843..4.843 rows=34640 loops=1) Index Cond: (loop = 60) Total runtime: 220128.290 ms </code></pre> <p>After analyze the table and setting enable_bitmapscan=off (still using partitioning): </p> <pre><code> Result (cost=0.00..2761.06 rows=33652 width=389) (actual time=9.714..378389.177 rows=34640 loops=1) -> Append (cost=0.00..2676.93 rows=33652 width=389) (actual time=0.119..34.065 rows=34640 loops=1) -> Index Scan using "resultTable_idx_mo" on resultTable a (cost=0.00..12.84 rows=5 width=48) (actual time=0.058..0.058 rows=0 loops=1) Index Cond: (loop= 79) -> Index Scan using resultTable_child_80_idx on resultTable_child_80 a (cost=0.00..2664.10 rows=33647 width=389) (actual time=0.061..30.303 rows=34640 loops=1) Index Cond: (loop = 79) Total runtime: 378393.671 ms (7 rows) </code></pre>

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