Differential D3662

pg: Rewrite _origin_query to force the query planner to filter on URLs before filtering on visits.
ClosedPublic
Actions

Authored by vlorentz on Jul 31 2020, 10:12 AM.

Details

Reviewers

ardumont

Group Reviewers

Reviewers

Commits

rDSTOdf943ec25cf9: pg: Rewrite _origin_query to force the query planner to filter on URLs before…

Summary

URL filters usually have a few matches and use the index; whereas filtering
on visits requires to scan the entire origin table first.

This makes the query considerably faster.

Credit for the idea goes to @olasd.

Diff Detail

Repository

rDSTO Storage manager

Lint

Automatic diff as part of commit; lint not applicable.

Unit

Automatic diff as part of commit; unit tests not applicable.

Event Timeline

vlorentz created this revision.Jul 31 2020, 10:12 AM

Herald added a reviewer: Reviewers. · View Herald TranscriptJul 31 2020, 10:12 AM

add credit

vlorentz edited the summary of this revision. (Show Details)Jul 31 2020, 10:13 AM

vlorentz added a subscriber: olasd.

Build has FAILED

Patch application report for D3662 (id=12888)

Could not rebase; Attempt merge onto cf9f44e805...

Updating cf9f44e8..8c8b0b83
Fast-forward
 swh/storage/backfill.py                | 43 +++++++++++++++++++++++++++++++++-
 swh/storage/converters.py              | 38 ++++++++++++++++++++++++++++++
 swh/storage/db.py                      | 42 +++++++++++++++++++--------------
 swh/storage/replay.py                  |  8 ++++++-
 swh/storage/storage.py                 | 31 ++----------------------
 swh/storage/tests/test_backfill.py     |  5 +++-
 swh/storage/tests/test_kafka_writer.py |  9 +++++++
 swh/storage/tests/test_replay.py       |  9 ++++---
 8 files changed, 131 insertions(+), 54 deletions(-)

Changes applied before test

commit 8c8b0b83d7831c77143f62dfa83c142c8d112b1f
Author: Valentin Lorentz <vlorentz@softwareheritage.org>
Date:   Fri Jul 31 10:11:57 2020 +0200

    pg: Rewrite _origin_query to force the query planner to filter on URLs before filtering on visits.
    
    URL filters usually have a few matches and use the index; whereas filtering
    on visits requires to scan the entire origin table first.
    
    This makes the query considerably faster.

commit 0c5a8e274af2aae42cdaedf3b462a9db0fdbf177
Author: Valentin Lorentz <vlorentz@softwareheritage.org>
Date:   Thu Jul 30 19:39:41 2020 +0200

    Add support for metadata-related object types to the backfiller and replayer.
    
    Existing tests automatically test them, using data from swh.journal.tests.

commit 24bc51dfff6c2fd825534a6dac23ff6a7e02faa0
Author: Valentin Lorentz <vlorentz@softwareheritage.org>
Date:   Thu Jul 30 19:33:14 2020 +0200

    test_replay: update for swh.journal 0.4.1.
    
    DUPLICATE_CONTENTS now contains BaseModel objects.

Link to build: https://jenkins.softwareheritage.org/job/DSTO/job/tests-on-diff/631/
See console output for more information: https://jenkins.softwareheritage.org/job/DSTO/job/tests-on-diff/631/console

Harbormaster failed remote builds in B14125: Diff 12888!Jul 31 2020, 10:20 AM

Build has FAILED

Patch application report for D3662 (id=12889)

Could not rebase; Attempt merge onto cf9f44e805...

Updating cf9f44e8..df943ec2
Fast-forward
 swh/storage/backfill.py                | 43 +++++++++++++++++++++++++++++++++-
 swh/storage/converters.py              | 38 ++++++++++++++++++++++++++++++
 swh/storage/db.py                      | 42 +++++++++++++++++++--------------
 swh/storage/replay.py                  |  8 ++++++-
 swh/storage/storage.py                 | 31 ++----------------------
 swh/storage/tests/test_backfill.py     |  5 +++-
 swh/storage/tests/test_kafka_writer.py |  9 +++++++
 swh/storage/tests/test_replay.py       |  9 ++++---
 8 files changed, 131 insertions(+), 54 deletions(-)

Changes applied before test

commit df943ec25cf91c0417c23a7376d40414a429db7d
Author: Valentin Lorentz <vlorentz@softwareheritage.org>
Date:   Fri Jul 31 10:11:57 2020 +0200

    pg: Rewrite _origin_query to force the query planner to filter on URLs before filtering on visits.
    
    URL filters usually have a few matches and use the index; whereas filtering
    on visits requires to scan the entire origin table first.
    
    This makes the query considerably faster.
    
    Credit for the idea goes to @olasd.

commit 0c5a8e274af2aae42cdaedf3b462a9db0fdbf177
Author: Valentin Lorentz <vlorentz@softwareheritage.org>
Date:   Thu Jul 30 19:39:41 2020 +0200

    Add support for metadata-related object types to the backfiller and replayer.
    
    Existing tests automatically test them, using data from swh.journal.tests.

commit 24bc51dfff6c2fd825534a6dac23ff6a7e02faa0
Author: Valentin Lorentz <vlorentz@softwareheritage.org>
Date:   Thu Jul 30 19:33:14 2020 +0200

    test_replay: update for swh.journal 0.4.1.
    
    DUPLICATE_CONTENTS now contains BaseModel objects.

Link to build: https://jenkins.softwareheritage.org/job/DSTO/job/tests-on-diff/632/
See console output for more information: https://jenkins.softwareheritage.org/job/DSTO/job/tests-on-diff/632/console

Harbormaster failed remote builds in B14126: Diff 12889!Jul 31 2020, 10:27 AM

Build is green

Patch application report for D3662 (id=12889)

Could not rebase; Attempt merge onto cf9f44e805...

Updating cf9f44e8..df943ec2
Fast-forward
 swh/storage/backfill.py                | 43 +++++++++++++++++++++++++++++++++-
 swh/storage/converters.py              | 38 ++++++++++++++++++++++++++++++
 swh/storage/db.py                      | 42 +++++++++++++++++++--------------
 swh/storage/replay.py                  |  8 ++++++-
 swh/storage/storage.py                 | 31 ++----------------------
 swh/storage/tests/test_backfill.py     |  5 +++-
 swh/storage/tests/test_kafka_writer.py |  9 +++++++
 swh/storage/tests/test_replay.py       |  9 ++++---
 8 files changed, 131 insertions(+), 54 deletions(-)

Changes applied before test

commit df943ec25cf91c0417c23a7376d40414a429db7d
Author: Valentin Lorentz <vlorentz@softwareheritage.org>
Date:   Fri Jul 31 10:11:57 2020 +0200

    pg: Rewrite _origin_query to force the query planner to filter on URLs before filtering on visits.
    
    URL filters usually have a few matches and use the index; whereas filtering
    on visits requires to scan the entire origin table first.
    
    This makes the query considerably faster.
    
    Credit for the idea goes to @olasd.

commit 0c5a8e274af2aae42cdaedf3b462a9db0fdbf177
Author: Valentin Lorentz <vlorentz@softwareheritage.org>
Date:   Thu Jul 30 19:39:41 2020 +0200

    Add support for metadata-related object types to the backfiller and replayer.
    
    Existing tests automatically test them, using data from swh.journal.tests.

commit 24bc51dfff6c2fd825534a6dac23ff6a7e02faa0
Author: Valentin Lorentz <vlorentz@softwareheritage.org>
Date:   Thu Jul 30 19:33:14 2020 +0200

    test_replay: update for swh.journal 0.4.1.
    
    DUPLICATE_CONTENTS now contains BaseModel objects.

See https://jenkins.softwareheritage.org/job/DSTO/job/tests-on-diff/635/ for more details.

Harbormaster completed remote builds in B14126: Diff 12889.Jul 31 2020, 1:04 PM

ardumont accepted this revision.Jul 31 2020, 1:22 PM

This revision is now accepted and ready to land.Jul 31 2020, 1:22 PM

Closed by commit rDSTOdf943ec25cf9: pg: Rewrite _origin_query to force the query planner to filter on URLs before… (authored by vlorentz). · Explain WhyJul 31 2020, 2:13 PM

This revision was automatically updated to reflect the committed changes.

vlorentz added a commit: rDSTOdf943ec25cf9: pg: Rewrite _origin_query to force the query planner to filter on URLs before….