SET track_io_timing = ON; explain (analyze,buffers) SELECT title, title <-> 'г. Санкт-Петербург' AS dist
FROM addresses
ORDER BY dist LIMIT 5;Limit (cost=21431.01..21431.02 rows=5 width=108) (actual time=4501.424..4501.425 rows=5 loops=1)
Buffers: shared hit=10289
-> Sort (cost=21431.01..22387.91 rows=382760 width=108) (actual time=4501.423..4501.423 rows=5 loops=1)
Sort Key: ((title <-> 'г. Санкт-Петербург'::text))
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=10289
-> Seq Scan on addresses (cost=0.00..15073.50 rows=382760 width=108) (actual time=0.025..4439.799 rows=382544 loops=1)
Buffers: shared hit=10289
Planning time: 0.081 ms
Execution time: 4501.442 msLimit (cost=3114.58..3114.59 rows=5 width=189) (actual time=2774.437..2774.438 rows=5 loops=1)
Buffers: shared hit=5708
-> Sort (cost=3114.58..3115.54 rows=383 width=189) (actual time=2774.436..2774.437 rows=5 loops=1)
" Sort Key: (similarity(title, 'г. Санкт-Петербург'::text)) DESC"
Sort Method: top-N heapsort Memory: 26kB
Buffers: shared hit=5708
-> Bitmap Heap Scan on addresses (cost=1810.97..3108.22 rows=383 width=189) (actual time=81.239..2738.971 rows=130950 loops=1)
Recheck Cond: (title % 'г. Санкт-Петербург'::text)
Rows Removed by Index Recheck: 7285
Heap Blocks: exact=4599
Buffers: shared hit=5708
-> Bitmap Index Scan on title_trgm_idx (cost=0.00..1810.87 rows=383 width=0) (actual time=80.601..80.601 rows=138235 loops=1)
Index Cond: (title % 'г. Санкт-Петербург'::text)
Buffers: shared hit=1109
Planning time: 0.118 ms
Execution time: 2774.468 msasync exchange() {
await axios.get("http://127.0.0.1:5555/information",
{params:{status: {data:this.state.process}},
headers: {
Authorization: this.state.session_id
}
})
.then(response => {
this.setState({process:response.data.data});
this.exchange()
});
};class WSHandler(tornado.websocket.WebSocketHandler):
def open(self):
print("websocket opened")
def check_origin(self, origin):
return True
async def on_message(self, evt):
data = json.loads(evt)
print(data)
await self.setup(data['id'])
async def setup(self, id):
self.conn = await aioredis.create_redis("redis://127.0.0.1")
self.thread = "thread:{!s}".format(id)
channel = await self.conn.psubscribe(self.thread)
await asyncio.ensure_future(self.send_message(channel, self))
async def send_message(self, channel, obj):
print("this")
# redis = await aioredis.create_redis("redis://127.0.0.1")
# channel_pattern = await redis.psubscribe("thread:*",)
while await channel[0].wait_message():
msg = await channel[0].get(encoding="utf-8")
print(msg)
# await obj.write_message(msg)
async def on_close(self):
print("WS close")
await self.conn.unsubscribe("thread:{!s}".format(id))
await self.conn.wait_closed()
a GIN index is faster to search than a GiST index, but slower to build or update; so GIN is better suited for static data and GiST for often-updated data.
www.postgresql.cn/docs/9.5/pgtrgm.html