catmaid / CATMAID / 5428 / 1

45%

master: 45%

push

travis-ci

<a href="https://github.com/catmaid/CATMAID/commit/<a class=hub.com/catmaid/CATMAID/commit/0c7eaf81357e109c8d388adb1896f7c0533fd432">0c7eaf813<a href="https://github.com/catmaid/CATMAID/commit/0c7eaf81357e109c8d388adb1896f7c0533fd432">&quot;&gt;Add experimental node 2d/3d index node provider variants

These node providers only provide the extra nodes as a CTE and do all
other computation in a single query with subqueries. This seems to yield
better query plans for 2d and about the same performance using 3d
indices. Only the treenode part of the providers are updated.

This is an example query using the regular 2d index in the current
FAFBv14 dataset yielding about 23k nodes:

 Nested Loop  (cost=44681.89..45647.86 rows=210 width=50) (actual time=182.031..313.229 rows=23320 loops=1)
   Buffers: shared hit=204109
   CTE z_filtered_edge
     -&amp;gt;  Bitmap Heap Scan on treenode_edge te  (cost=609.20..38661.97 rows=20579 width=72) (actual time=23.497..58.141 rows=20803 loops=1)
           Recheck Cond: (floatrange(st_zmin((edge)::box3d), st_zmax((edge)::box3d), &amp;#39;[]&amp;#39;::text) &amp;amp;&amp;amp; &amp;#39;[73480,73520)&amp;#39;::floatrange)
           Filter: (project_id = 1)
           Rows Removed by Filter: 59
           Heap Blocks: exact=19117
           Buffers: shared hit=20060
           -&amp;gt;  Bitmap Index Scan on treenode_edge_z_range_gist  (cost=0.00..604.05 rows=20751 width=0) (actual time=20.601..20.601 rows=22188 loops=1)
                 Index Cond: (floatrange(st_zmin((edge)::box3d), st_zmax((edge)::box3d), &amp;#39;[]&amp;#39;::text) &amp;amp;&amp;amp; &amp;#39;[73480,73520)&amp;#39;::floatrange)
                 Buffers: shared hit=943
   CTE bb_edge
     -&amp;gt;  CTE Scan on z_filtered_edge e_1  (cost=0.00..5762.12 rows=55 width=8) (actual time=23.514..96.820 rows=13464 loops=1)
           Filter: ((edge &amp;&amp; &#39;</a><a class="double-link" href="https://github.com/catmaid/CATMAID/commit/<a class="double-link" href="https://github.com/catmaid/CATMAID/commit/0103000000010000000500000000000000207019">010300000</a>">010300000</a><a href="https://github.com/catmaid/CATMAID/commit/0c7eaf81357e109c8d388adb1896f7c0533fd432">41000000007CECFF400000000020701941000000001F8D12410000000010772241000000001F8D124100000... (continued)

7675 of 16839 relevant lines covered (45.58%)

0.46 hits per line