Page MenuHomeSoftware Heritage
Differential D1082

Use the index when doing metadata search.
ClosedPublic
Actions

Authored by vlorentz on Feb 5 2019, 3:55 PM.

Details

Reviewers
ardumont
Group Reviewers
Reviewers
Maniphest Tasks
T1517: Metadata search is too slow
Commits
rDCIDXa02da7261f11: Use the index when doing metadata search.
Summary

Resolves T1517.

Diff Detail

Repository
rDCIDX Metadata indexer
Branch
fulltext-index
Lint
No Linters Available
Unit
No Unit Test Coverage
Build Status
Buildable 4016
Build 5270: tox-on-jenkinsJenkins
Build 5269: arc lint + arc unit

Event Timeline

vlorentz created this revision.Feb 5 2019, 3:55 PM
Herald added a reviewer: Reviewers. · View Herald TranscriptFeb 5 2019, 3:55 PM
swh-public-ci added a subscriber: swh-public-ci.Feb 5 2019, 3:58 PM

Build is green
See https://jenkins.softwareheritage.org/job/DCIDX/job/tox/318/ for more details.

Harbormaster completed remote builds in B4016: Diff 3396.Feb 5 2019, 3:58 PM
ardumont accepted this revision.Feb 6 2019, 11:22 AM
ardumont added a subscriber: ardumont.

I'm not so good at guessing that this change actually makes it use the index.
Checking the current schema, i see the column metadata_tsvector has an index so from afar, the change seems to do as promised ;)

create index origin_intrinsic_metadata_fulltext_idx on origin_intrinsic_metadata using gin (metadata_tsvector);

ps: I think proposing an explain plan of before and after might be good (i know it's boring ;)

This revision is now accepted and ready to land.Feb 6 2019, 11:22 AM
vlorentz added a comment.Feb 6 2019, 12:10 PM
In D1082#22880, @ardumont wrote:

Checking the current schema, i see the column metadata_tsvector has an index so from afar, the change seems to do as promised ;)

indeed

ps: I think proposing an explain plan of before and after might be good (i know it's boring ;)

Old query:

softwareheritage-indexer=> explain SELECT origin_id, metadata, from_revision, mappings, i.id as tool_id, tool_name, tool_version, tool_configuration FROM origin_intrinsic_metadata AS oim INNER JOIN indexer_configuration AS i ON oim.indexer_configuration_id=i.id JOIN LATERAL (SELECT plainto_tsquery('pg_catalog.simple', 'parmap')) AS s(tsq) ON true WHERE to_tsvector('pg_catalog.simple', metadata) @@ tsq ORDER BY ts_rank(oim.metadata_tsvector, tsq, 1) DESC LIMIT 10;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3547638.84..3547640.01 rows=10 width=299)
   ->  Gather Merge  (cost=3547638.84..3558902.16 rows=96536 width=299)
         Workers Planned: 2
         ->  Sort  (cost=3546638.82..3546759.49 rows=48268 width=299)
               Sort Key: (ts_rank(oim.metadata_tsvector, '''parmap'''::tsquery, 1)) DESC
               ->  Hash Join  (cost=5.03..3542883.86 rows=48268 width=299)
                     Hash Cond: (oim.indexer_configuration_id = i.id)
                     ->  Parallel Seq Scan on origin_intrinsic_metadata oim  (cost=0.00..3542625.50 rows=48268 width=300)
                           Filter: (to_tsvector('simple'::regconfig, metadata) @@ '''parmap'''::tsquery)
                     ->  Hash  (cost=3.90..3.90 rows=90 width=149)
                           ->  Seq Scan on indexer_configuration i  (cost=0.00..3.90 rows=90 width=149)
(11 rows)

new one:

softwareheritage-indexer=> explain SELECT origin_id, metadata, from_revision, mappings, i.id as tool_id, tool_name, tool_version, tool_configuration FROM origin_intrinsic_metadata AS oim INNER JOIN indexer_configuration AS i ON oim.indexer_configuration_id=i.id JOIN LATERAL (SELECT plainto_tsquery('pg_catalog.simple', 'parmap')) AS s(tsq) ON true WHERE metadata_tsvector @@ tsq ORDER BY ts_rank(oim.metadata_tsvector, tsq, 1) DESC LIMIT 10;                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=4215.95..4215.98 rows=10 width=299)
   ->  Sort  (cost=4215.95..4225.61 rows=3862 width=299)
         Sort Key: (ts_rank(oim.metadata_tsvector, '''parmap'''::tsquery, 1)) DESC
         ->  Hash Join  (cost=208.95..4132.50 rows=3862 width=299)
               Hash Cond: (oim.indexer_configuration_id = i.id)
               ->  Bitmap Heap Scan on origin_intrinsic_metadata oim  (cost=203.93..4107.20 rows=3862 width=300)
                     Recheck Cond: (metadata_tsvector @@ '''parmap'''::tsquery)
                     ->  Bitmap Index Scan on origin_intrinsic_metadata_fulltext_idx  (cost=0.00..202.96 rows=3862 width=0)
                           Index Cond: (metadata_tsvector @@ '''parmap'''::tsquery)
               ->  Hash  (cost=3.90..3.90 rows=90 width=149)
                     ->  Seq Scan on indexer_configuration i  (cost=0.00..3.90 rows=90 width=149)
(11 rows)

In practice, that's several minutes vs less than a second.

vlorentz updated this revision to Diff 3450.Feb 7 2019, 3:25 PM
  • rebase
Closed by commit rDCIDXa02da7261f11: Use the index when doing metadata search. (authored by vlorentz). · Explain WhyFeb 7 2019, 3:25 PM
This revision was automatically updated to reflect the committed changes.
Harbormaster failed remote builds in B4078: Diff 3450!Feb 7 2019, 3:26 PM
swh-public-ci added a comment.Feb 7 2019, 3:26 PM

Build has FAILED

Link to build: https://jenkins.softwareheritage.org/job/DCIDX/job/tox/370/
See console output for more information: https://jenkins.softwareheritage.org/job/DCIDX/job/tox/370/console

Revision Contents
Changeset List

PathSize
swh/
indexer/
storage/
2 lines

Diff 3396

View Options

swh/indexer/storage/db.py

  • This file was added.
{
"name": "swh-search-query-language-parser",
"version": "1.0.0",
"description": "Parser for Software Heritage archive search query language",
"scripts": {
"generate": "cd query_language && tree-sitter generate --no-bindings",
"dev": "yarn generate && cd query_language && tree-sitter parse sample_query",
"test": "yarn generate && cd query_language && tree-sitter test",
anlambertUnsubmitted
Not Done
Inline Actions

You should use the --no-bindings option of tree-sitter generate, it will not generate node and rust bindings we do not need here.

Also can you add the generation of the wasm module for the parser in the generate script ?
It will enable to use the parser in swh-web using web-tree-sitter.

anlambert: You should use the `--no-bindings` option of `tree-sitter generate`, it will not generate node…
"build-so": "yarn generate && cd query_language && python3 build.py",
"build-wasm": "yarn generate && cd query_language && tree-sitter build-wasm && mv tree-sitter-swh_search_query_language.wasm swh_ql.wasm",
"build": "yarn build-so && yarn build-wasm",
"repl": "yarn generate && cd query_language && tree-sitter build-wasm && tree-sitter playground"
},
"repository": {
"type": "git",
"url": "https://forge.softwareheritage.org/source/swh-search.git"
},
"keywords": [
"swh",
"Software Heritage",
"treesitter",
"parser",
"custom",
"search",
"query",
vlorentzUnsubmitted
Done
Inline Actions
"language"
],
- "author": "Software Heritage",
+ "author": "The Software Heritage developers",
"license": "GPL-2.0-only",
vlorentz:
"language"
KShivenduAuthorUnsubmitted
Done
Inline Actions

Are the values of name, description, repository.url, keywords, author and license correct?

KShivendu: Are the values of `name, description, repository.url, keywords, author and license` correct?
vlorentzUnsubmitted
Done
Inline Actions

yes, as far as I can tell

vlorentz: yes, as far as I can tell
vlorentzUnsubmitted
Done
Inline Actions
"author": "Software Heritage",
- "license": "GPL-2.0-only",
+ "license": "GPL-3.0-only",
"dependencies": {
vlorentz:
],
"author": "The Software Heritage developers",
"license": "GPL-3.0-only",
"dependencies": {
"nan": "^2.14.2"
},
"devDependencies": {
"tree-sitter-cli": "^0.20.0"
}
}
No newline at end of file
About · Developer information · Legal