There is a database in Postgres, now there are about 3 000 000 lines (there will be much more), here are a few of them:
pub text data 68264400 А Вот теперь точно пятница!)) Наслаждаемся!)) {"attachments": [{"video": {"photo_640": "https://pp.userapi.com/c845216/v845216380/da477/4oBhrXMZu78.jpg", "comments": 0, "views": 307, "owner_id": -169532875, "platform": "YouTube", "duration": 611, "photo_130": "https://pp.userapi.com/c845216/v845216380/da474/7u2HeQg5m_s.jpg", "photo_320": "https://pp.userapi.com/c845216/v845216380/da476/4U1VesXEhRs.jpg", "id": 456239037, "date": 1535127680, "description": "\u0421\u043c\u0435\u0448\u043d\u044b\u0435 \u0432\u0438\u0434\u0435\u043e \u043f\u0440\u0438\u043a\u043e\u043b\u044b Best COUB # 15 | \u041a\u043e\u0443\u0431 | Cube | \u0410\u0432\u0433\u0443\u0441\u0442 2018 | \u0410\u043d\u0438\u043c\u0435/\u0416\u0438\u0432\u043e\u0442\u043d\u044b\u0435/Game/Wins - CoubHUB\n\n\u2605 \u041f\u043e\u0441\u0442\u0430\u0432\u044c \u043b\u0430\u0439\u043a \u2764 \u042d\u0442\u043e \u043e\u0447\u0435\u043d\u044c \u043d\u0430\u0441 \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0442!\n...", "title": "\u0421\u043c\u0435\u0448\u043d\u044b\u0435 \u0432\u0438\u0434\u0435\u043e \u043f\u0440\u0438\u043a\u043e\u043b\u044b Best COUB # 15 | \u041a\u043e\u0443\u0431 | Cube | \u0410\u0432\u0433\u0443\u0441\u0442 2018 | \u0410\u043d\u0438\u043c\u0435/\u0416\u0438\u0432\u043e\u0442\u043d\u044b\u0435/Game/Wins -CoubHUB", "access_key": "0d46fb35e0aa617fb7", "can_add": 1, "photo_800": "https://pp.userapi.com/c845216/v845216380/da477/4oBhrXMZu78.jpg"}, "type": "video"}], "post_source": {"platform": "iphone", "type": "api"}, "views": {"count": 3085}, "likes": {"can_publish": 1, "user_likes": 0, "can_like": 1, "count": 3}, "text": "\u0410 \u0412\u043e\u0442 \u0442\u0435\u043f\u0435\u0440\u044c \u0442\u043e\u0447\u043d\u043e \u043f\u044f\u0442\u043d\u0438\u0446\u0430!)) \u041d\u0430\u0441\u043b\u0430\u0436\u0434\u0430\u0435\u043c\u0441\u044f!))", "from_id": -68264400, "signer_id": 499274352, "comments": {"can_post": 1, "groups_can_post": true, "count": 3}, "id": 80487, "date": 1535193628, "marked_as_ads": 0, "reposts": {"user_reposted": 0, "count": 1}, "post_type": "post", "owner_id": -68264400} So far, all variables of type TEXT , I know that this is not rational, but so far I can not alter, because:
1st value can be not only a number, but not more than 20 characters long
2nd value text with unknown length
3rd value is also TEXT with json
Now select * from база where pub=id type queries select * from база where pub=id works well for sooo long, about 8 seconds per request. Can I somehow significantly increase the time? Does it really help to remake the first column under the varchar or something numerical? And how much will it help to remove the third column?
datafield. Because there is JSON, then you need to parse it and decompose all the data from it into different tables. And make a connection through an intermediate table with records from the current one. So in the current there will be only 2 fields -pubandtext- everything should speed up significantly. - Yuriy SPb ♦