1

Topic: SQL requests became

I use DBMS Postgresql, rather not so long ago, SQL requests began to be fulfilled much longer on time. Some requests are fulfilled in some tens times longer.
Carried out the analysis of operation of a DB (compared dataful monthly prescription), problems to storage or  is not present. But noted that access time to section from a DB differs on the order what it is possible to make with these, except banal VACUUM FULL ANALYZE?

2

Re: SQL requests became

polin11 wrote:

I Use DBMS Postgresql, rather not so long ago, SQL requests began to be fulfilled much longer on time. Some requests are fulfilled in some tens times longer.
Carried out the analysis of operation of a DB (compared dataful monthly prescription), problems to storage or  is not present. But noted that access time to section from a DB differs on the order what it is possible to make with these, except banal VACUUM FULL ANALYZE?

To understand with loading and productivity of disk system on the one hand.
And to understand with requests  most of all time occupy (taking into account a disk) on the other hand.
--
Maxim Boguk
the best support PostgreSQL: dataegret.ru

3

Re: SQL requests became

reindex most likely it is necessary

4

Re: SQL requests became

polin11;
If "banal VACUUM FULL ANALYZE" helps, something can not that with adjustments autovacuum.
That here such request returns:
select name, setting, unit from pg_settings where name ~ ' autovacuum ';

5

Re: SQL requests became

In request other index began to be used, whether correctly I understand, after
VACUUM FULL ANALYZE statistics who is used by the scheduler for a choice optimal should be updated
Method of performance of request, also should help REINDEX?
Whether there is still a method except VACUUM FULL ANALYZE to deliver "on a way true" the scheduler?

6

Re: SQL requests became

polin11 wrote:

In request other index began to be used, whether correctly I understand, after
VACUUM FULL ANALYZE statistics who is used by the scheduler for a choice optimal should be updated
Method of performance of request, also should help REINDEX?
Whether there is still a method except VACUUM FULL ANALYZE to deliver "on a way true" the scheduler?

VACUUM ANALYZE

7

Re: SQL requests became

Whether

polin11 wrote:

There is still a method except VACUUM FULL ANALYZE to deliver "on a way true" the scheduler?

VACUUM not is too most, as VACUUM FULL.
VACUUM marks records which any transaction in basis cannot see any more as an empty seat. VACUUM FULL rebuilds the table (and all indexes) entirely.
ANALYZE - separate operation which is carried out last and updates statistics of allocation of the data in the table for planning of requests.

8

Re: SQL requests became

Unfortunately VACUUM and REINDEX did not help.
As scheduler POSTGRES, for me a riddle works.
There is a same DB on other server, in similar requests,
There the necessary index which was used on problem basis earlier is used.
I will result the elementary request and execution plans

SELECT Filed1
FROM Table1
WHERE Field2 like ' 01 % ' AND Fileld3 = 123

The request plan for a problem DB:

"Unique (cost=0.43. 6.21 rows=1 width=8) (actual time=1.323. 11.502 rows=184 loops=1)"
"Buffers: shared hit=4806 read=416"
"-> Index Scan using" Index1 "on" Table1 "(cost=0.43. 6.20 rows=1 width=8) (actual time=1.320. 11.473 rows=184 loops=1)"
"Index Cond: (" Filed3 "= 123)"
"Filter: (" Field2 "~~ ' 01 % ':: text)"
"Rows Removed by Filter: 37833"
"Buffers: shared hit=4806 read=416"
"Planning time: 26.205 ms"
"Execution time: 11.571 ms"

It is used Index1 across the field Field2 with a class of the operator varchar_pattern_ops, the request was fulfilled 25 seconds
From a similar DB on other server

"Index Scan using" Index2 "on" Table1 "(cost=0.69. 8.71 rows=1 width=8) (actual time=0.052. 0.426 rows=184 loops=1)"
"Index Cond: ((" Filed3 "= 123) AND (" Field2 "~> = ~ ' 01 ':: text) AND (" Field2 "~ <~ ' 02 ':: text))"
"Filter: (" Filed2 "~~ ' 01 % ':: text)"
"Buffers: shared hit=189"
"Planning time: 0.558 ms"
"Execution time: 0.546 ms"

It is used Index2 on fields (Field2 with a class of the operator varchar_pattern_ops, Field3), the request was fulfilled 50 milliseconds
Somehow to optimize request it is impossible, where is easier. I still had last thought, to delete Index1;
Whether but will be in all places where it is really necessary Index1, to be used Index2 and whether it will brake these requests?
I will be glad to any constructive ideas

9

Re: SQL requests became

polin11 wrote:

It is used Index2 on fields (Field2 with a class of the operator varchar_pattern_ops, Field3), the request was fulfilled 50 milliseconds
I will be glad to any constructive ideas

Because  another is selected. posix - works varchar.
And on the first server you specified en_US.UTF-8 or ru_RU.UTF-8, in general, not kosher  - and varchar_pattern_ops is not used.
Check...

10

Re: SQL requests became

Andy_OLAP;
Inattentively looked. Not in  business.
"It is used Index2 on fields (Field2 with a class of the operator varchar_pattern_ops, Field3" on the second, and on the first it is not used.
If on the first server a heap of the out-of-date lines which have been not cleaned by vacuum, thus field Field3 permanently changed - the scheduler will try to use index Index1 which does not include Field3, and on the second server the same index more kosher, here its scheduler and involves.

11

Re: SQL requests became

12

Re: SQL requests became

vyegorov;
You are right, the foreground for request together with DISTINCT, but the essence from it does not change.
Runtime "Execution time: 11.571 ms" instead of 25 seconds because it is the plan of request which already fulfilled earlier and POSTGRES  result and it is fulfilled 11 ms, the initiating plan

"Index Scan using" Index1 "on" Table1 "(cost=0.43. 6.20 rows=1 width=8) (actual time=0.184. 8.611 rows=184 loops=1)"
"Index Cond: (" Filed3 "= 123)"
"Filter: (" Field2 "~~ ' 01 % ':: text)"
"Rows Removed by Filter: 37833"
"Buffers: shared hit=5219"
' Planning time: 2.804 ms'
' Execution time: 24963.109 ms'

Structures of tables and indexes completely the identical. The data identical if also there are differences, they minimum.
How look at deleting Index1?
Whether will be in all places where it is really necessary Index1, to be used Index2 and whether it will brake these requests?

13

Re: SQL requests became

polin11;
1. Include ` track_io_timing ` in a config and result an output ` EXPLAIN (analyze, buffers) ` once again. There is a big suspicion that disks at you at all do not pull
2. If there is a problem with swelling of tables and indexes:
- It is necessary to make aggressive autovacuum
- It will be convinced that suffices autovacuum_max_workers
- To analyze loading about presence  tables (bicycle queue what) and long reports in basis simultaneously. If is such to remove long requests about a remark with ungeared hot_standby_feedback and sufficient max_standby_streaming_delay
- After that to get rid from bloat - pgcompacttable / pg_repack / VACUUM FULL (though I repak th would transit is better)
- Rigidly to supervise duration of transactions (no more than 10 minutes) through shooting in a crone
The aggressive vacuum can rest against disks that says that it is necessary to think of gland better besides.

14

Re: SQL requests became

polin11 wrote:

because it is the plan of request which already fulfilled earlier and POSTGRES  result

Is not present at postgresql any cache of results.

polin11 wrote:

the initiating plan

"Index Scan using" Index1 "on" Table1 "(cost=0.43. 6.20 rows=1 width=8) (actual time=0.184. 8.611 rows=184 loops=1)"
"Index Cond: (" Filed3 "= 123)"
"Filter: (" Field2 "~~ ' 01 % ':: text)"
"Rows Removed by Filter: 37833"
"Buffers: shared hit=5219"
' Planning time: 2.804 ms'
' Execution time: 24963.109 ms'

Doubtfully. It is all plan? Attention on actual time and 100 % shared hit

15

Re: SQL requests became

The difference between plans and accordingly begins runtime even at request

select "Field1"
from "Table1"
where "Field2" like ' 01 %'

The plan for problem basis

"Index Scan using" Index1 "on" Table1 "(cost=0.69. 8.71 rows=1384 width=8) (actual time=0.150. 35919.512 rows=68445 loops=1)"
"Index Cond: ((" Field2 "~> = ~ ' 01 ':: text) AND (" Field2 "~ <~ ' 02 ':: text))"
"Filter: (" Field2 "~~ ' 01 % ':: text)"
"Buffers: shared hit=27768 read=22839"
"Planning time: 2.604 ms"
"Execution time: 35938.358 ms"

The plan for test basis

"Bitmap Heap Scan on" Table1 "(cost=87.97. 7136.05 rows=1178 width=8) (actual time=19.847. 110.466 rows=64985 loops=1)"
"Filter: (" Field2 "~~ ' 01 % ':: text)"
"Heap Blocks: exact=13466"
"Buffers: shared hit=13853"
"-> Bitmap Index Scan on" Index1 "(cost=0.00. 87.68 rows=1899 width=0) (actual time=17.049. 17.049 rows=64985 loops=1)"
"Index Cond: ((" Field2 "~> = ~ ' 01 ':: text) AND (" Field2 "~ <~ ' 02 ':: text))"
"Buffers: shared hit=387"
"Planning time: 1.151 ms"
"Execution time: 116.189 ms"

16

Re: SQL requests became

polin11;
And if to make ` REINDEX INDEX "Index1" ` in problem basis?.