{"action":"created","issue":{"url":"https://api.github.com/repos/CartoDB/bigmetadata/issues/541","repository_url":"https://api.github.com/repos/CartoDB/bigmetadata","labels_url":"https://api.github.com/repos/CartoDB/bigmetadata/issues/541/labels{/name}","comments_url":"https://api.github.com/repos/CartoDB/bigmetadata/issues/541/comments","events_url":"https://api.github.com/repos/CartoDB/bigmetadata/issues/541/events","html_url":"https://github.com/CartoDB/bigmetadata/issues/541","id":347377645,"node_id":"MDU6SXNzdWUzNDczNzc2NDU=","number":541,"title":"MVT slowness and fixes","user":{"login":"juanignaciosl","id":932968,"node_id":"MDQ6VXNlcjkzMjk2OA==","avatar_url":"https://avatars2.githubusercontent.com/u/932968?v=4","gravatar_id":"","url":"https://api.github.com/users/juanignaciosl","html_url":"https://github.com/juanignaciosl","followers_url":"https://api.github.com/users/juanignaciosl/followers","following_url":"https://api.github.com/users/juanignaciosl/following{/other_user}","gists_url":"https://api.github.com/users/juanignaciosl/gists{/gist_id}","starred_url":"https://api.github.com/users/juanignaciosl/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/juanignaciosl/subscriptions","organizations_url":"https://api.github.com/users/juanignaciosl/orgs","repos_url":"https://api.github.com/users/juanignaciosl/repos","events_url":"https://api.github.com/users/juanignaciosl/events{/privacy}","received_events_url":"https://api.github.com/users/juanignaciosl/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"juanignaciosl","id":932968,"node_id":"MDQ6VXNlcjkzMjk2OA==","avatar_url":"https://avatars2.githubusercontent.com/u/932968?v=4","gravatar_id":"","url":"https://api.github.com/users/juanignaciosl","html_url":"https://github.com/juanignaciosl","followers_url":"https://api.github.com/users/juanignaciosl/followers","following_url":"https://api.github.com/users/juanignaciosl/following{/other_user}","gists_url":"https://api.github.com/users/juanignaciosl/gists{/gist_id}","starred_url":"https://api.github.com/users/juanignaciosl/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/juanignaciosl/subscriptions","organizations_url":"https://api.github.com/users/juanignaciosl/orgs","repos_url":"https://api.github.com/users/juanignaciosl/repos","events_url":"https://api.github.com/users/juanignaciosl/events{/privacy}","received_events_url":"https://api.github.com/users/juanignaciosl/received_events","type":"User","site_admin":false},"assignees":[{"login":"juanignaciosl","id":932968,"node_id":"MDQ6VXNlcjkzMjk2OA==","avatar_url":"https://avatars2.githubusercontent.com/u/932968?v=4","gravatar_id":"","url":"https://api.github.com/users/juanignaciosl","html_url":"https://github.com/juanignaciosl","followers_url":"https://api.github.com/users/juanignaciosl/followers","following_url":"https://api.github.com/users/juanignaciosl/following{/other_user}","gists_url":"https://api.github.com/users/juanignaciosl/gists{/gist_id}","starred_url":"https://api.github.com/users/juanignaciosl/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/juanignaciosl/subscriptions","organizations_url":"https://api.github.com/users/juanignaciosl/orgs","repos_url":"https://api.github.com/users/juanignaciosl/repos","events_url":"https://api.github.com/users/juanignaciosl/events{/privacy}","received_events_url":"https://api.github.com/users/juanignaciosl/received_events","type":"User","site_admin":false},{"login":"antoniocarlon","id":6155203,"node_id":"MDQ6VXNlcjYxNTUyMDM=","avatar_url":"https://avatars0.githubusercontent.com/u/6155203?v=4","gravatar_id":"","url":"https://api.github.com/users/antoniocarlon","html_url":"https://github.com/antoniocarlon","followers_url":"https://api.github.com/users/antoniocarlon/followers","following_url":"https://api.github.com/users/antoniocarlon/following{/other_user}","gists_url":"https://api.github.com/users/antoniocarlon/gists{/gist_id}","starred_url":"https://api.github.com/users/antoniocarlon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/antoniocarlon/subscriptions","organizations_url":"https://api.github.com/users/antoniocarlon/orgs","repos_url":"https://api.github.com/users/antoniocarlon/repos","events_url":"https://api.github.com/users/antoniocarlon/events{/privacy}","received_events_url":"https://api.github.com/users/antoniocarlon/received_events","type":"User","site_admin":false}],"milestone":null,"comments":18,"created_at":"2018-08-03T12:29:37Z","updated_at":"2018-08-30T13:47:39Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"_Extract from an email thread_\r\n\r\nWe hit a performance wall at Tilesets API. While [queries that fetch the geometries and data are fast, the generation of the MVT is slow](https://gist.github.com/antoniocarlon/61f9b244e2ffb499681d80e9ffbba1f2) ([simpler example](https://gist.github.com/antoniocarlon/684cadd071ec08bf6c9697b5f96bee9e)). It looks like json generation and string concatenation inside PG is costly. We asked for help from Ramsey, and he just came back (thanks!) with some insights (direct copy from a Slack group):\r\n\r\n> This is the worst possible way to report out on this, but started doing the profiling and as expected we burn a lot of time in attribute processing… some of it is I think recoverable: the call to get_key_index might be something we can work around. That still leaves the calls to numeric_out, which are turning some numbers into strings. There’s an interesting question: do we maybe have some data types in this query that the MVT system is not handling directly as numbers, but passing out via a string representation. That could be another place to fix things up. Will continue.\r\n\r\n![screenshot_200](https://user-images.githubusercontent.com/932968/43642644-b1ffd72c-9728-11e8-97ac-99b8266eac54.png)\r\n\r\n> Avoiding the JSONB code path is still inefficient (4x slower than the pre-made JSONB, but 2x faster than a code path that first constructs the JSONB) and also spends most of its time in value handling, though in a different code path than the JSONB one.\r\n\r\n![screenshot_201](https://user-images.githubusercontent.com/932968/43642655-bc43ff7e-9728-11e8-8f80-73bacd1dcf58.png)\r\n\r\n> well, just removing one redundant strlen() from the key lookup routine made the jsonb path 20% faster\r\nsince there’s one in the value handler as well, there might be a big more to squeeze all that numeric data is expensive to handle … the hash table gets very large and each incoming value has to be checked to see if it’s in the dictionary\r\n> this kind of thing really shows up what MVT was specified for, which was string-tagged OSM data\r\nnot numeric data\r\n> once again, goes back to Jaak’s suggestion\r\n> MVT with ids, efficient attribute API and join on the client side\r\n> I can squeeze a bit more out of value handling, but it’s a more invasive change than the key handling, unfortunately\r\n\r\n> I take it back, since most of your time is in numeric handling my change in the string value handling won’t make much difference\r\n\r\n\r\n\r\nAs you can see, there's a redesign suggestion: MVT with ids, efficient attribute API and join on the client side.\r\n\r\n\r\nWe'll take a look at this this week (I'm at the Response Team, but Mario is back from vacations). We can also try to move MVT generation from PostgreSQL to Node.\r\n\r\n\r\n---\r\n\r\n\r\nDiscussion summary: although we all agree that the current approach is a perverse usage of an MVT, which are not designed to cope with hundred of attributes, there are time constraints that push us towards fixing this issue and being able to serve this kind of MVTs.\r\n\r\n\r\n---\r\n\r\nSecond, into the numbers. I found the results quite surprising. This led me to do a bit of research about encoding properties in MVT tiles. I created [a gist with my developments and some numbers](https://gist.github.com/rochoa/899994ccf39f3aed5076556c61760ec3#file-readme-md), the results are even more surprising: it's faster to do two separate queries, one for the tile with no-properties and another for the properties, and do the merging and encoding of the final MVT tile with all the properties in JavaScript than doing all the operation in PostgreSQL/PostGIS. In my machine, the JavaScript approach takes 1/4 of the time of the PostGIS one.\r\n\r\n\r\n\r\n----\r\n\r\n\r\nSo, we need to generate MVT faster than we do now. Alternatives:\r\n\r\na) Find a way through current implementation at PG and PGIS that is performant enough.\r\nb) It looks like there's some issue at PostGIS, so fixing it there might be the best way.\r\nc) Generate the MVT at Node."},"comment":{"url":"https://api.github.com/repos/CartoDB/bigmetadata/issues/comments/417325525","html_url":"https://github.com/CartoDB/bigmetadata/issues/541#issuecomment-417325525","issue_url":"https://api.github.com/repos/CartoDB/bigmetadata/issues/541","id":417325525,"node_id":"MDEyOklzc3VlQ29tbWVudDQxNzMyNTUyNQ==","user":{"login":"Algunenano","id":664253,"node_id":"MDQ6VXNlcjY2NDI1Mw==","avatar_url":"https://avatars2.githubusercontent.com/u/664253?v=4","gravatar_id":"","url":"https://api.github.com/users/Algunenano","html_url":"https://github.com/Algunenano","followers_url":"https://api.github.com/users/Algunenano/followers","following_url":"https://api.github.com/users/Algunenano/following{/other_user}","gists_url":"https://api.github.com/users/Algunenano/gists{/gist_id}","starred_url":"https://api.github.com/users/Algunenano/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Algunenano/subscriptions","organizations_url":"https://api.github.com/users/Algunenano/orgs","repos_url":"https://api.github.com/users/Algunenano/repos","events_url":"https://api.github.com/users/Algunenano/events{/privacy}","received_events_url":"https://api.github.com/users/Algunenano/received_events","type":"User","site_admin":false},"created_at":"2018-08-30T13:47:39Z","updated_at":"2018-08-30T13:47:39Z","author_association":"MEMBER","body":"The minor release is the same, so you can either check the package itself (`apt list postgis` I think) or the build time:\r\n```sql\r\n# Select postgis_lib_build_date(); postgis_lib_build_date \r\n------------------------\r\n 2018-08-29 13:57:37\r\n(1 row)\r\n```"}}
{"actor":{"display_login":"Algunenano"}}