1

Topic: Help to optimize request.

How to accelerate Select query * FROM user_message_entity ume inner join message_entity me on me.id = ume.message_entity_id WHERE to_tsvector (' russian ', message) @@ to_tsquery (' russian ', ' greetings ') and user_entity_id = 2;
The repeated request works faster as completely reading goes from the buffer. It is necessary to change user_entity_id and the request is expanded on tens seconds because of disk reading. How that it is possible to tire out all in the buffer or any other method of the decision?

Nested Loop (cost=246.83. 105916.36 rows=431 width=325) (actual time=50.694. 28348.201 rows=1365 loops=1)
Buffers: shared hit=514783 read=33840
I/O Timings: read=25783.602
-> Bitmap Heap Scan on user_message_entity ume (cost=246.27. 29349.37 rows=20671 width=46) (actual time=43.804. 11569.066 rows=95182 loops=1)
Recheck Cond: (user_entity_id = 2)
Rows Removed by Index Recheck: 3484133
Heap Blocks: exact=43145 lossy=28665
Buffers: shared hit=56368 read=15706
I/O Timings: read=10709.177
-> Bitmap Index Scan on user_message_entity_user_peer_idx (cost=0.00. 241.10 rows=20671 width=0) (actual time=31.045. 31.045 rows=95182 loops=1)
Index Cond: (user_entity_id = 2)
Buffers: shared hit=264
-> Index Scan using message_entity_pkey on message_entity me (cost=0.56. 3.69 rows=1 width=279) (actual time=0.176. 0.176 rows=0 loops=95182)
Index Cond: (id = ume.message_entity_id)
Filter: (to_tsvector (' russian ':: regconfig, message) @@ ' "greetings '":: tsquery)
Rows Removed by Filter: 1
Buffers: shared hit=458415 read=18134
I/O Timings: read=15074.425
Planning time: 0.933 ms
Execution time: 28348.765 ms

2

Re: Help to optimize request.

Rum11;
1) to deliver normal disks 25s on reading of 35000 buffers it is very sad
And
2) to deliver is enough storage that the main working part of basis there was located
I with 1 would begin.
Question not to basis and to the used equipment.
Deliver Intel optane a disk and  digits in 10000 times less (on time with a disk) smile).
PS: I still work_mem would lift that was not Heap Blocks: exact=43145 lossy=28665
But it is fundamental to you a question does not solve.
--
Maxim Boguk
the best support PostgreSQL: dataegret.ru

3

Re: Help to optimize request.

Rum11;
To try to cluster on user_entity_id. Periodically.
?

4

Re: Help to optimize request.

qwwq;
Data grows very quickly. It is necessary to do permanently clustering if I correctly understood you.

5

Re: Help to optimize request.

Rum11;
Whether and there is a full-text index on message and an index on user_message_entity.message_entity_id?

6

Re: Help to optimize request.

Alexius;
Is.

7

Re: Help to optimize request.

Rum11;
And what such request shows?

explain (analyze, buffers) SELECT * FROM user_message_entity ume inner join message_entity me on me.id = ume.message_entity_id WHERE to_tsvector (' russian ', message) @@ to_tsquery (' russian ', ' greetings ') and user_entity_id+0 = 2;

8

Re: Help to optimize request.

Alexius;

Hash Join (cost=966626.01. 3020560.22 rows=9020 width=325) (actual time=290974.776. 314592.306 rows=1365 loops=1)
Hash Cond: (ume.message_entity_id = me.id)
Buffers: shared hit=348957 read=829516, temp read=47173 written=46919
I/O Timings: read=34802.834
-> Seq Scan on user_message_entity ume (cost=0.00. 2013197.44 rows=432952 width=46) (actual time=6.309. 21550.896 rows=95182 loops=1)
Filter: ((user_entity_id + 0) = 2)
Rows Removed by Filter: 86495275
Buffers: shared hit=199188 read=515152
I/O Timings: read=9284.150
-> Hash (cost=924634.63. 924634.63 rows=846430 width=279) (actual time=290954.017. 290954.017 rows=859809 loops=1)
Buckets: 16384 Batches: 128 Memory Usage: 3136kB
Buffers: shared hit=149769 read=314364, temp written=46088
I/O Timings: read=25518.684
-> Bitmap Heap Scan on message_entity me (cost=7035.33. 924634.63 rows=846430 width=279) (actual time=233.156. 289431.548 rows=859809 loops=1)
Recheck Cond: (to_tsvector (' russian ':: regconfig, message) @@ ' "greetings '":: tsquery)
Rows Removed by Index Recheck: 14220793
Heap Blocks: exact=34213 lossy=423716
Buffers: shared hit=149769 read=314364
I/O Timings: read=25518.684
-> Bitmap Index Scan on msgs_text_idx (cost=0.00. 6823.73 rows=846430 width=0) (actual time=224.986. 224.986 rows=859809 loops=1)
Index Cond: (to_tsvector (' russian ':: regconfig, message) @@ ' "greetings '":: tsquery)
Buffers: shared hit=179
Planning time: 0.472 ms
Execution time: 314592.660 ms

9

Re: Help to optimize request.

Rum11;
, the initial plan optimal means was for request with such parameters.
To already offered councils I will add that it is possible still to try to check up bloat in tables and indexes and to eliminate it if is. All fields from both tables are probably necessary not and it is possible index only scan somewhere to make.

10

Re: Help to optimize request.

Rum11 wrote:

qwwq;
Data grows very quickly. It is necessary to do permanently clustering if I correctly understood you.

It is possible still  on (ranges) user_entity_id.
Hits on page will be much more.
it is possible to filter at first not * but only  (if I correctly understand, the data not  almost) ios th (to include 2 fields in an index) + the filter on  but only then to tighten fields (if  cuts strongly that can benefit it to turn out) first label on .
And generally to leave on a muscle from it  - clusters (it is pure  - for was not, was not attracted)

11

Re: Help to optimize request.

qwwq;
Forgot that   not to get. Does not grow together. It is necessary still id-niki 1 -  in an index . The third field.