{"action":"created","issue":{"url":"https://api.github.com/repos/inspirehep/invenio/issues/231","labels_url":"https://api.github.com/repos/inspirehep/invenio/issues/231/labels{/name}","comments_url":"https://api.github.com/repos/inspirehep/invenio/issues/231/comments","events_url":"https://api.github.com/repos/inspirehep/invenio/issues/231/events","html_url":"https://github.com/inspirehep/invenio/issues/231","id":87329518,"number":231,"title":"Benchmark: Elasticsearch vs PostgreSQL JSONB for relations handling","user":{"login":"MSusik","id":6649290,"avatar_url":"https://avatars.githubusercontent.com/u/6649290?v=3","gravatar_id":"","url":"https://api.github.com/users/MSusik","html_url":"https://github.com/MSusik","followers_url":"https://api.github.com/users/MSusik/followers","following_url":"https://api.github.com/users/MSusik/following{/other_user}","gists_url":"https://api.github.com/users/MSusik/gists{/gist_id}","starred_url":"https://api.github.com/users/MSusik/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/MSusik/subscriptions","organizations_url":"https://api.github.com/users/MSusik/orgs","repos_url":"https://api.github.com/users/MSusik/repos","events_url":"https://api.github.com/users/MSusik/events{/privacy}","received_events_url":"https://api.github.com/users/MSusik/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/inspirehep/invenio/labels/enhancement","name":"enhancement","color":"84b6eb"},{"url":"https://api.github.com/repos/inspirehep/invenio/labels/help%20wanted","name":"help wanted","color":"159818"},{"url":"https://api.github.com/repos/inspirehep/invenio/labels/next","name":"next","color":"fbca04"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2015-06-11T12:30:16Z","updated_at":"2015-06-11T12:51:59Z","closed_at":null,"body":"In this issue the results of benchmarking will be announced.\r\n\r\n# Benchmark\r\n\r\nThe benchmark will consist of a set of queries which should be run on databases. The database will contain simplified records. The structure of the records is as follows:\r\n\r\n### Paper record\r\n\r\n``` json\r\n{\"id\": \"<id of paper>\",\r\n \"authors\":[\r\n   {\r\n      \"id\": \"<id of related author>\",\r\n      \"full_name\": \"<name as it appears on the paper>\",\r\n      \"affiliation_id\": \"<id of related affiliation>\"\r\n   },\r\n   ...\r\n ],\r\n \"title\": \"<title of the paper>\",\r\n \"types\": [\"<one of the types of paper without duplicates>\", ...],\r\n \"keywords\": [\"<keyword\", ...],\r\n \"citations\": [\"<id of paper cited by this paper>\", ...]\r\n}\r\n```\r\n### Author record\r\n``` json\r\n{\"id\": \"<id of authot>\"} \r\n```\r\n\r\n### Affiliation record\r\n``` json\r\n{\"id\": \"<id of affiliation>\",\r\n \"country\": \"<where the affiliation is location, ex. France>\",\r\n \"name\": \"<name of the affiliation>\"\r\n}\r\n```\r\n## Queries\r\n\r\n1). Get names and ids of coauthors and for every of them a number of paper written by them and a given author (in other words, render the coauthors box on an author profile).\r\n\r\n2). Get name variants and number of their appearances on the papers for a given author (in other words, render the name variants box on an author profile).\r\n\r\n3). Get affiliations, their ids, and number of papers written by a given author where the author was associated with this affiliation (in other words, render the affiliations box on an author profile)\r\n\r\n4). Get keywords and number of their appearances on the papers for given author (in other words, render the keywords box on an author profile).\r\n\r\n5). Get number of papers and number of single authored papers for every category of papers for a given author (in other words, render the papers box on an author profile without sums over all categories).\r\n\r\n6). Get number of all papers and number of all single authored papers for a given author.\r\n\r\n7).  Get all citations which cite a given author's paper (both ids of paper).\r\n\r\n8). Get number of papers that cite papers written by authors who are affiliated with an institution from a given country. (one author from an institution from a given country is enough in order to have all the references counted in the result).\r\n\r\n## Dataset\r\n\r\nTBA\r\n\r\n## Machine used\r\n\r\nTBA\r\n\r\n## PostgreSQL setup\r\n\r\nTBA\r\n\r\n## PostgreSQL JSONB queries\r\n\r\nThere is a `records` table with a `record` JSONB column.\r\n\r\nThe input for the queries is author with id 13 and France for affiliations (query nr 8)\r\n\r\n1).\r\n```sql\r\nSELECT r.record->>'name', pid, number FROM records r, LATERAL (\r\n    SELECT o->>'id' as pid, count(o->>'id') as number FROM records r,\r\n    jsonb_array_elements(r.record->'authors') o\r\n    WHERE record->'authors' @> '[{\"id\": 13}]' group by o->>'id') c\r\nWHERE r.record->>'id' = pid and pid != '13';\r\n```\r\n2).\r\n``` sql\r\nSELECT r->>'full_name', count(r->>'full_name')\r\nFROM records, jsonb_array_elements(record->'authors') r\r\nWHERE r->'id' = '13' group by r->>'full_name';\r\n```\r\n3).\r\n``` sql\r\nSELECT r.record->>'name', aid, number FROM  records r, LATERAL (\r\n\tSELECT o->>'affiliation_id' as aid, count(o->>'affiliation_id') as number\r\n\tFROM records r, jsonb_array_elements(r.record->'authors') o WHERE\r\n\to->>'id' = '13' group by o->>'affiliation_id') c\r\nWHERE r.record->>'id' = aid;\r\n```\r\n\r\n4).\r\n``` sql\r\nSELECT keyword, count(keyword)\r\nFROM records r, jsonb_array_elements_text(r.record->'keywords') keyword\r\nWHERE r.record->'authors' @> '[{\"id\": 13}]' group by keyword;\r\n```\r\n\r\n5).\r\n``` sql\r\nSELECT type, count(type) as all,\r\ncount(case when jsonb_array_length(r.record->'authors') != 1 then 1 end) as\r\nsingle_authored\r\nFROM records r, jsonb_array_elements_text(r.record->'types') type\r\nWHERE r.record->'authors' @> '[{\"id\": 13}]' group by type;\r\n```\r\n\r\n6).\r\n``` sql\r\nSELECT count(*) as all,\r\ncount(case when jsonb_array_length(record->'authors') != 1 then 1 end)\r\nas single_authored\r\nFROM records where record->'authors' @> '[{\"id\": 13}]';\r\n```\r\n\r\n7).\r\n``` sql\r\nSELECT r.record->>'id' as cites, cited\r\nFROM records r, LATERAL(\r\n    SELECT s.record->>'id' own FROM records s\r\n    WHERE s.record->'authors' @> '[{\"id\": 13}]'\r\n) subquery, jsonb_array_elements_text(r.record->'citations') cited\r\nWHERE cited = own;\r\n```\r\n\r\n8). (needs improvements)\r\n``` sql\r\nSELECT count(distinct(b)) FROM records b,\r\nLATERAL (\r\n    SELECT a.record->>'id' ids FROM records a where a.record->>'country' = 'France'\r\n    ) subquery,\r\nLATERAL (\r\n    SELECT c.record->>'id' idsc FROM records c where ids in\r\n    (SELECTjsonb_array_elements(c.record->'authors')->>'affiliation_id')\r\n) subquery2\r\nWHERE b.record->'citations' ? idsc;\r\n```\r\nPlease note that some of those queries might be improved. I confirm that queries above work on my machine and give expected results.\r\n\r\n## Elasticsearch setup\r\n\r\nTBA (relations will be resolved before pushing to db!)\r\n\r\n## Elasticsearch queries\r\n\r\nTBA\r\n\r\n### Other things\r\n\r\nThanks to @dset0x for helping me with writing some of JSONB queries."},"comment":{"url":"https://api.github.com/repos/inspirehep/invenio/issues/comments/111122238","html_url":"https://github.com/inspirehep/invenio/issues/231#issuecomment-111122238","issue_url":"https://api.github.com/repos/inspirehep/invenio/issues/231","id":111122238,"user":{"login":"MSusik","id":6649290,"avatar_url":"https://avatars.githubusercontent.com/u/6649290?v=3","gravatar_id":"","url":"https://api.github.com/users/MSusik","html_url":"https://github.com/MSusik","followers_url":"https://api.github.com/users/MSusik/followers","following_url":"https://api.github.com/users/MSusik/following{/other_user}","gists_url":"https://api.github.com/users/MSusik/gists{/gist_id}","starred_url":"https://api.github.com/users/MSusik/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/MSusik/subscriptions","organizations_url":"https://api.github.com/users/MSusik/orgs","repos_url":"https://api.github.com/users/MSusik/repos","events_url":"https://api.github.com/users/MSusik/events{/privacy}","received_events_url":"https://api.github.com/users/MSusik/received_events","type":"User","site_admin":false},"created_at":"2015-06-11T12:51:59Z","updated_at":"2015-06-11T12:51:59Z","body":"@eamonnmag As already mentioned in discussions: It would imply using three different DBs: Postgre, Orient and Elasticsearch. And, what's more important, IT will never agree with using Orient for records."}}