https://dev.arvados.org/https://dev.arvados.org/favicon.ico?15576888422022-03-29T15:59:27ZArvadosArvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1022202022-03-29T15:59:27ZWard Vandewegeward@curii.com
<ul><li><strong>Description</strong> updated (<a title="View differences" href="/journals/102220/diff?detail_id=98823">diff</a>)</li></ul> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1022232022-03-29T16:00:31ZWard Vandewegeward@curii.com
<ul><li><strong>Related to</strong> <i><a class="issue tracker-5 status-3 priority-4 priority-default closed child" href="/issues/18904">Task #18904</a>: review 18903-fix-activity-script</i> added</li></ul> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1022242022-03-29T16:21:21ZWard Vandewegeward@curii.com
<ul><li><strong>Description</strong> updated (<a title="View differences" href="/journals/102224/diff?detail_id=98828">diff</a>)</li><li><strong>Subject</strong> changed from <i>[db] add missing created_at,uuid index on the logs table</i> to <i>[db] add missing created_at,uuid indexes</i></li></ul> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1022282022-03-29T17:30:37ZWard Vandewegeward@curii.com
<ul><li><strong>Related to</strong> <i><a class="issue tracker-1 status-3 priority-4 priority-default closed parent" href="/issues/18903">Bug #18903</a>: [user activity script] does not always initialize user object and other fixes</i> added</li></ul> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1022302022-03-29T19:45:12ZWard Vandewegeward@curii.com
<ul><li><strong>Release</strong> set to <i>46</i></li></ul> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1023052022-03-30T15:15:37ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Assigned To</strong> set to <i>Lucas Di Pentima</i></li></ul> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1024022022-04-01T15:08:57ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Assigned To</strong> changed from <i>Lucas Di Pentima</i> to <i>Peter Amstutz</i></li><li><strong>Status</strong> changed from <i>New</i> to <i>In Progress</i></li></ul> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1024082022-04-01T18:52:51ZPeter Amstutzpeter.amstutz@curii.com
<ul></ul><p><a class="external" href="https://www.postgresql.org/docs/current/indexes-ordering.html">https://www.postgresql.org/docs/current/indexes-ordering.html</a></p>
<blockquote>
<p>By default, B-tree indexes store their entries in ascending order with nulls last. This means that a forward scan of an index on column x produces output satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS LAST). The index can also be scanned backward, producing output satisfying ORDER BY x DESC (or more verbosely, ORDER BY x DESC NULLS FIRST, since NULLS FIRST is the default for ORDER BY DESC).</p>
</blockquote> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1024092022-04-01T18:57:52ZPeter Amstutzpeter.amstutz@curii.com
<ul></ul><p>18943-created-at-index @ <a class="changeset" title="18943: Adjust keyset_list_all for efficient decending order Default is ascending (created_at asc..." href="https://dev.arvados.org/projects/arvados/repository/arvados/revisions/f988449749c047bbf100e94dd2bb9285b08fa3c1">f988449749c047bbf100e94dd2bb9285b08fa3c1</a></p>
<p><a class="external" href="https://ci.arvados.org/view/Developer/job/developer-run-tests/3009/"<a href="https://ci.arvados.org/view/Developer/job/developer-run-tests/3009/">developer-run-tests: #3009 <img src="https://ci.arvados.org/buildStatus/icon?job=developer-run-tests&build=3009" alt="" /></a></a></p>
<ul>
<li>Migrate indexes for collections, links, logs, groups, and users to two-column index (created_at, uuid)</li>
<li>Fix keyset_list_all() in python SDK so that "ascending=False" parameter results in query (created_at desc, uuid desc) which will use the index, instead of (created_at desc, uuid asc) which won't use the index.</li>
</ul> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1024132022-04-01T19:30:29ZTom Cleggtom@curii.com
<ul></ul><p>The modified_at indices are (modified_at DESC, uuid) so it looks like keyset_list_all will now perform better with the default order_key="created_at" but worse with order_key="modified_at".</p>
<p>I'm thinking it would be better (fewer special cases) if we make both sets of indexes work in the same direction, so we can use the same uuid-direction rule in keyset_list_all for both created_at and modified_at, and still get decent performance.</p> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1024162022-04-01T21:09:59ZPeter Amstutzpeter.amstutz@curii.com
<ul></ul><p>18943-created-at-index <a class="changeset" title="18943: Drop redundant indexes Arvados-DCO-1.1-Signed-off-by: Peter Amstutz <peter.amstutz@curii...." href="https://dev.arvados.org/projects/arvados/repository/arvados/revisions/06c2fb95a229a00a3a39ac84db60062c94fe8f32">06c2fb95a229a00a3a39ac84db60062c94fe8f32</a></p>
<p><a class="external" href="https://ci.arvados.org/view/Developer/job/developer-run-tests/3012/"<a href="https://ci.arvados.org/view/Developer/job/developer-run-tests/3012/">developer-run-tests: #3012 <img src="https://ci.arvados.org/buildStatus/icon?job=developer-run-tests&build=3012" alt="" /></a></a></p>
<p>Add <br />index_#{table}_on_created_at_and_uuid <br />and<br />index_#{table}_on_modified_at_and_uuid</p>
<p>Drop extraneous indexes (e.g. indexes on created_at or modified_at only)</p>
<p>Adjust the default query order to (modified_at desc, uuid desc) to match the adjusted modified_at_and_uuid index ordering.</p>
<p>This will have minor effect on ordering for default queries. If two records have the same modified_at, the tiebreaker will now order by descending uuid, instead of ascending uuid.</p> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1024182022-04-01T21:52:04ZTom Cleggtom@curii.com
<ul></ul><p>The down-migration probably shouldn't re-add the (created_at, uuid) index after deleting it.</p>
<p>Rest LGTM, thanks.</p> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1024272022-04-04T15:01:45ZPeter Amstutzpeter.amstutz@curii.com
<ul></ul><p>18943-created-at-index @ <a class="changeset" title="18943: Tweaking fixtures Arvados-DCO-1.1-Signed-off-by: Peter Amstutz <peter.amstutz@curii.com>" href="https://dev.arvados.org/projects/arvados/repository/arvados/revisions/58d86f7855278212a5c7b4dad2fea03f653740af">58d86f7855278212a5c7b4dad2fea03f653740af</a></p>
<ul>
<li>Fix tests that explicitly checked the sort order</li>
<li>Tweaked test fixtures which had the same created_at time (mostly because of copy&paste) to have different created_at times, because order by uuid changed</li>
</ul>
<p><a class="external" href="https://ci.arvados.org/view/Developer/job/developer-run-tests/3015/"<a href="https://ci.arvados.org/view/Developer/job/developer-run-tests/3015/">developer-run-tests: #3015 <img src="https://ci.arvados.org/buildStatus/icon?job=developer-run-tests&build=3015" alt="" /></a></a></p> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1024302022-04-04T15:09:44ZTom Cleggtom@curii.com
<ul></ul><p>Once remaining tests are fixed, LGTM (looks like there's still a workbench1 test that's sensitive to the fixture timestamps)</p> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1024522022-04-04T18:30:03ZPeter Amstutzpeter.amstutz@curii.com
<ul></ul><p>18943-created-at-index @ <a class="changeset" title="18943: Fix workbench bug Arvados-DCO-1.1-Signed-off-by: Peter Amstutz <peter.amstutz@curii.com>" href="https://dev.arvados.org/projects/arvados/repository/arvados/revisions/39cfbff065282869d8dadab2474dfaae4ba0b86a">39cfbff065282869d8dadab2474dfaae4ba0b86a</a></p>
<p><a class="external" href="https://ci.arvados.org/view/Developer/job/developer-run-tests/3019/"<a href="https://ci.arvados.org/view/Developer/job/developer-run-tests/3019/">developer-run-tests: #3019 <img src="https://ci.arvados.org/buildStatus/icon?job=developer-run-tests&build=3019" alt="" /></a></a></p> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1024552022-04-04T18:54:06ZTom Cleggtom@curii.com
<ul></ul><p>LGTM</p> Arvados - Bug #18943: [db] add missing created_at,uuid indexeshttps://dev.arvados.org/issues/18943?journal_id=1024752022-04-05T13:24:05ZPeter Amstutzpeter.amstutz@curii.com
<ul><li><strong>Status</strong> changed from <i>In Progress</i> to <i>Resolved</i></li></ul>