SELECT работает слишком медленно с ORDER BY / PostgreSQL

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / SELECT работает слишком медленно с ORDER BY

25 сообщений из 30, страница 1 из 2

все

SELECT работает слишком медленно с ORDER BY

#39238837

NewBie77

Гость

Доброго времени суток форумчане .

Имеется запрос который генерирует ORM OpenERP:

Код: plsql

1.
2.
3.
4.

SELECT "tbl".id 
FROM "tbl" 
WHERE (("tbl"."active" = 'True')  AND  ("tbl"."is_company" IS NULL or "tbl"."is_company" = false )) 
ORDER BY "tbl"."display_name"

Индексы :

Код: plsql

1.
2.
3.
4.

    "ix_tbl_pkey" PRIMARY KEY, btree (id)
    "ix_active" btree (active)
    "ix_displayname" btree (display_name)
    "ix_iscompany" btree (is_company)

Запрос с ORDER BY "tbl"."display_name" работает слишком медленно
План :

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.

 Sort  (cost=13031.73..13269.13 rows=94960 width=47) (actual time=57711.753..57725.079 rows=94967 loops=1)
   Sort Key: display_name
   Sort Method: quicksort  Memory: 12918kB
   ->  Seq Scan on tbl (cost=0.00..5180.90 rows=94960 width=47) (actual time=0.009..57.056 rows=94967 loops=1)
         Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
         Rows Removed by Filter: 623
 Total runtime: 57735.775 ms
(7 rows)

Без ORDER BY "tbl"."display_name"
План :

Код: plsql

1.
2.
3.
4.
5.

 Seq Scan on tbl (cost=0.00..5180.90 rows=94960 width=4) (actual time=0.026..60.782 rows=94967 loops=1)
   Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
   Rows Removed by Filter: 623
 Total runtime: 65.969 ms
(4 rows)

К сожалению запрос поменять нельзя никак (нету доступа к ORM-у) . Есть идеи как ускорить с ORDER BY?

...

Рейтинг:

0 / 0

19.05.2016, 10:39

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39238857

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

NewBie77,

сделайте для теста set enable_sort = off; и покажите какой будет план запроса с order by.

как-то подозрительно долго сортирует 100к записей, в этом поле какие-то длинные значения? или может процессор перегружен, что более вероятно?

...

Рейтинг:

0 / 0

19.05.2016, 10:52

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39238863

NewBie77

Гость

Alexius,

Код: plsql

1.
2.
3.
4.
5.

 Index Scan using ix_displayname on tbl(cost=0.00..21479.14 rows=94960 width=47) (actual time=29.912..1194.954 rows=94967 loops=1)
   Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
   Rows Removed by Filter: 623
 Total runtime: 1206.157 ms
(4 rows)

В display_name записаны имена и фамилии.

...

Рейтинг:

0 / 0

19.05.2016, 10:56

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39238865

NewBie77

Гость

Все тесты делались ночью когда система не была загружена, проблем с перегрузкой процессора не было.

...

Рейтинг:

0 / 0

19.05.2016, 10:58

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39238908

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

AlexiusNewBie77,
как-то подозрительно долго сортирует 100к записейдопустим то "ин мемори", в котором оно, якобы, "сорт" -- оказалось в свапе. это бы всё объяснило.

...

Рейтинг:

0 / 0

19.05.2016, 11:24

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39238910

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

NewBie77,

какой размер индекса ix_displayname (\di+ ix_displayname) и размер таблицы?

...

Рейтинг:

0 / 0

19.05.2016, 11:25

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39238916

NewBie77

Гость

Alexius,

Индекс сделал вчера когда заметил что запрос выполняется медленно.

Код: plsql

1.
2.
3.
4.
5.
6.

    \di+ ix_displayname
                                List of relations
 Schema |      Name      | Type  |  Owner  |    Table    |  Size   | Description 
--------+----------------+-------+---------+-------------+---------+-------------
 public | ix_displayname | index | test     | tbl          | 6304 kB | 
(1 row)

Таблица :

Код: plsql

public.tbl | 75 MB

...

Рейтинг:

0 / 0

19.05.2016, 11:31

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39238926

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

Alexius,
там ширина 47.

я вот смоделировал

Код: sql

1.
2.
3.
4.
5.
6.

'Sort  (cost=21292.25..21542.25 rows=100000 width=143) (actual time=5023.955..5540.206 rows=100000 loops=1)'
'  Output: foo.XX,fo.XXXXXXXXX'
'  Sort Key: foo.XXXXXXXXX'
'  Sort Method: external merge  Disk: 14512kB'
'  Buffers: shared hit=4 read=3782, temp read=1816 written=1816'
..............

кажется даже диск не объясняет то время для сорта [width=143], что случилось у автора[width=47].
главный вопрос теперь -- насколько оно воспроизводимо.

ТС, у вас там какой-нито хитровывернутый коллейт не используется ?

или, предполагаю в порядке бреда, вы , б.м., как--нито переопределили отношение порядка для варчара ? через жутко медленную ф--ю?

...

Рейтинг:

0 / 0

19.05.2016, 11:36

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39238988

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

qwwq,

со свопом вполне вариант, если в этот момент диски еще были перегружены.

NewBie77, если последовательно несколько раз запустить исходный запрос, время выполнения меняется как-то? какая ОС используется ? если linux - можно вывод команды top (шапку + процесс, который запрос выполняет, если видно) во время выполнения запроса? есть ли какая-то виртуализация?

...

Рейтинг:

0 / 0

19.05.2016, 12:34

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239001

NewBie77

Гость

qwwq,

Там поле типа

Код: plsql

 display_name  | character varying

все записи в уникоде

Alexius ,

Код: plsql

OS : CentOS release 6.4 (Final)

Запрос выполняется в одно и то же время если несколько раз запустить. Потестили на 2 серверах запрос и на обеих выполняется медленно.

top во время выполнения запроса:

Код: plsql

1.
2.
3.
4.
5.

23456 postgres  20   0  761m  88m  50m R 99.7  4.7   0:37.89 postmaster                                                             
 2105 jira      20   0 2268m 348m 2060 S  0.3 18.6 239:15.23 java                                                                   
    1 root      20   0 19356  308  136 S  0.0  0.0   0:01.28 init                                                                   
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd                                                               
    3 root      RT   0     0    0    0 S  0.0  0.0   0:00.55 migration/0

...

Рейтинг:

0 / 0

19.05.2016, 12:44

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239007

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

NewBie77,

интересно еще шапку top посмотреть во время выполнения запроса, все что перед списком процессов.

...

Рейтинг:

0 / 0

19.05.2016, 12:48

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239014

NewBie77

Гость

Alexius,

Извиняюсь если не правильно понял

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                
23456 postgres  20   0  763m  90m  50m R 100.0  4.8   1:03.73 postmaster                                                            
 2105 jira      20   0 2268m 349m 2060 S  0.7 18.6 239:18.25 java                                                                   
    1 root      20   0 19356  312  136 S  0.0  0.0   0:01.28 init                                                                   
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd                                                               
    3 root      RT   0     0    0    0 S  0.0  0.0   0:00.55 migration/0                                                            
    4 root      20   0     0    0    0 S  0.0  0.0   0:07.75 ksoftirqd/0                                                            
    5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0                                                            
    6 root      RT   0     0    0    0 S  0.0  0.0   0:03.45 watchdog/0

...

Рейтинг:

0 / 0

19.05.2016, 12:52

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239022

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

NewBie77,

нет, я имел ввиду все что выше. где load average, %sy, %wa, использование свопа и прочее.

...

Рейтинг:

0 / 0

19.05.2016, 12:57

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239024

NewBie77

Гость

Alexius,

Код: plsql

1.
2.
3.
4.
5.

top - 09:51:05 up 31 days, 39 min,  5 users,  load average: 0.28, 0.10, 0.04
Tasks: 174 total,   2 running, 172 sleeping,   0 stopped,   0 zombie
Cpu(s): 50.1%us,  0.2%sy,  0.0%ni, 49.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   1922464k total,  1854884k used,    67580k free,    34200k buffers
Swap:  4128760k total,  1608592k used,  2520168k free,   410480k cached

...

Рейтинг:

0 / 0

19.05.2016, 12:58

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239036

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

NewBie77,

спасибо, получается 2ГБ памяти и своп используется. какой размер shared_buffers? я вижу java, аппетиты ей как-то зарезаются? посмотрите в top кто еще память ест (можно нажать shift+M, чтобы отсортировать).

...

Рейтинг:

0 / 0

19.05.2016, 13:08

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239037

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

NewBie77,

я правильно понял, шо у вас там жаба бесконтрольно память похавала всю, с концаме ?
гоните ее с серверов БД сцаными тряпкаме.

поправьте меня, люди добрые, если вру.

...

Рейтинг:

0 / 0

19.05.2016, 13:09

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239060

NewBie77

Гость

Java стоит на тестовом сервере , на основном нету

Топ основного сервера :

Код: plsql

1.
2.
3.
4.
5.

top - 14:10:26 up 60 days, 20:15,  4 users,  load average: 0.56, 0.76, 0.95
Tasks: 243 total,   2 running, 241 sleeping,   0 stopped,   0 zombie
Cpu(s): 14.6%us,  1.8%sy,  0.0%ni, 83.3%id,  0.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  12190216k total, 11330408k used,   859808k free,    42976k buffers
Swap:  8388600k total,  1217232k used,  7171368k free,  4403328k cached

Память жрет сам OpenERP.

Код: plsql

 3166 openerp   20   0 2117m 377m 1544 S  0.0 20.1   2:58.66 python

shared buffer стоит на тестовом : 500MB
на основном : shared_buffers = 1600MB

...

Рейтинг:

0 / 0

19.05.2016, 13:22

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239067

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

NewBie77,

Приведите вывод:
системных команд `lscpu` и `free -m`

запроса: SELECT name,setting,unit FROM pg_settings WHERE source NOT IN ('default','override');

запроса: SELECT version();

На продукционном сервере этот эффект также воспроизводим?
Что ещё работает на продукции, кроме ПЖ?

...

Рейтинг:

0 / 0

19.05.2016, 13:25

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239085

NewBie77

Гость

vyegorov,

Тестовый сервер :

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.

Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                2
On-line CPU(s) list:   0,1
Thread(s) per core:    1
Core(s) per socket:    1
Socket(s):             2
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 44
Stepping:              2
CPU MHz:               2399.317
BogoMIPS:              4798.63
Hypervisor vendor:     VMware
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              12288K
NUMA node0 CPU(s):     0,1

             total       used       free     shared    buffers     cached
Mem:          1877       1795         81          0         38        406
-/+ buffers/cache:       1350        526
Swap:         4031       1570       2461


            name            |      setting       | unit 
----------------------------+--------------------+------
 application_name           | psql               | 
 client_encoding            | UTF8               | 
 DateStyle                  | ISO, MDY           | 
 default_text_search_config | pg_catalog.english | 
 lc_messages                | en_US.UTF-8        | 
 lc_monetary                | en_US.UTF-8        | 
 lc_numeric                 | en_US.UTF-8        | 
 lc_time                    | en_US.UTF-8        | 
 listen_addresses           | *                  | 
 log_destination            | stderr             | 
 log_directory              | pg_log             | 
 log_filename               | postgresql-%a.log  | 
 log_min_duration_statement | 100                | ms
 log_rotation_age           | 1440               | min
 log_rotation_size          | 0                  | kB
 log_timezone               | UTC                | 
 log_truncate_on_rotation   | on                 | 
 logging_collector          | on                 | 
 max_connections            | 100                | 
 max_stack_depth            | 2048               | kB
 port                       | 5432               | 
 shared_buffers             | 64000              | 8kB
 TimeZone                   | UTC                | 
 work_mem                   | 204800             | kB


  PostgreSQL 9.2.15 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit

Основной :

Код: plsql

Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                4
On-line CPU(s) list:   0-3
Thread(s) per core:    1
Core(s) per socket:    4
Socket(s):             1
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 30
Stepping:              5
CPU MHz:               1197.000
BogoMIPS:              4788.25
Virtualization:        VT-x
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              8192K
NUMA node0 CPU(s):     0-3

             total       used       free     shared    buffers     cached
Mem:         11904      10947        956          0         40       4196
-/+ buffers/cache:       6710       5193
Swap:         8191       1226       6965


            name            |      setting       | unit 
----------------------------+--------------------+------
 DateStyle                  | ISO, MDY           | 
 default_text_search_config | pg_catalog.english | 
 effective_cache_size       | 524288             | 8kB
 lc_messages                | en_US.UTF-8        | 
 lc_monetary                | en_US.UTF-8        | 
 lc_numeric                 | en_US.UTF-8        | 
 lc_time                    | en_US.UTF-8        | 
 listen_addresses           | *                  | 
 log_destination            | stderr             | 
 log_directory              | pg_log             | 
 log_filename               | postgresql-%a.log  | 
 log_rotation_age           | 1440               | min
 log_rotation_size          | 0                  | kB
 log_timezone               | UTC                      | 
 log_truncate_on_rotation   | on                 | 
 logging_collector          | on                 | 
 maintenance_work_mem       | 819200             | kB
 max_connections            | 100                | 
 max_stack_depth            | 2048               | kB
 port                       | 5432               | 
 shared_buffers             | 204800             | 8kB
 TimeZone                   | UTC                       | 
 work_mem                   | 131072             | kB
(23 rows)


--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit

На основном сервере только база и OpenERP и на обоих сервераз запрос глючит

...

Рейтинг:

0 / 0

19.05.2016, 13:42

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239193

NewBie77

Гость

Нету никаких идей ребят как решить данную проблему ?

...

Рейтинг:

0 / 0

19.05.2016, 15:20

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239248

Alexius

Участник

Сообщения: 749

Рейтинг: 0 / 0

NewBie77,

уменьшить work_mem, ограничить память у приложений как-то, добавить памяти.

для теста можно попробовать на тестовом сервере остановить приложения, чтобы память освободилась и своп не использовался и запустить запрос еще раз.

...

Рейтинг:

0 / 0

19.05.2016, 15:46

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239249

Maxim Boguk

Участник

Откуда: Melbourne, Австралия

Сообщения: 5 148

Рейтинг: 0 / 0

NewBie77Нету никаких идей ребят как решить данную проблему ?

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

create table test_table as 
SELECT "tbl".id,  "tbl"."display_name" 
FROM "tbl" 
WHERE (("tbl"."active" = 'True')  AND  ("tbl"."is_company" IS NULL or "tbl"."is_company" = false )) ;

vacuum analyze  test_table;

select count(*) from test_table;

select max(length(display_name)) from test_table;

select pg_size_pretty(pg_table_size('test_table'));

explain analyze select id from test_table order by display_name;

а дальше я подумаю в чем беда.

--
Maxim Boguk
www.postgresql-consulting.ru

...

Рейтинг:

0 / 0

19.05.2016, 15:47

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239260

qwwq

Участник

Сообщения: 2 880

Рейтинг: 0 / 0

NewBie77,

идея пока в том, что пж думает, что работает с нормальной памятью, и делает квик сорт, который унутре наверняка имеет обильную рендомную адресацию. а если бы он знал, что памяти с гулькин хрен --- он бы пользовался Sort Method: external merge Disk -- как у всех порядочных людей, с нехваткой памяти. кактотак.

...

Рейтинг:

0 / 0

19.05.2016, 15:53

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239270

vyegorov

Участник

Откуда: Баньоло-ин-Пьяно

Сообщения: 1 237

Рейтинг: 0 / 0

NewBie77,

work_mem слишком большой. у меня 512 памяти в продукциях, но work_mem 32MB максимум.
Потенциально, ПЖ может съесть shared_buffers + work_mem*max_connections, если придут все возможные сессии.
Вы сами загоняете себя в swap. Понизьте до умолчательных 4МБ, если нужно больше для каких-то запросов, то меняйте индивидуально в скрипте.

что выводит: sysctl -a|egrep '^vm.(dirty|swap|over)'

...

Рейтинг:

0 / 0

19.05.2016, 15:58

| Ответить | Цитировать | Написать

SELECT работает слишком медленно с ORDER BY

#39239291

NewBie77

Гость

vyegorovNewBie77,

work_mem слишком большой. у меня 512 памяти в продукциях, но work_mem 32MB максимум.
Потенциально, ПЖ может съесть shared_buffers + work_mem*max_connections, если придут все возможные сессии.
Вы сами загоняете себя в swap. Понизьте до умолчательных 4МБ, если нужно больше для каких-то запросов, то меняйте индивидуально в скрипте.

что выводит: sysctl -a|egrep '^vm.(dirty|swap|over)'

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.
9.

vm.overcommit_memory = 0
vm.overcommit_ratio = 50
vm.dirty_background_ratio = 10
vm.dirty_background_bytes = 0
vm.dirty_ratio = 20
vm.dirty_bytes = 0
vm.dirty_writeback_centisecs = 500
vm.dirty_expire_centisecs = 3000
vm.swappiness = 60

work_mem поставил на 4 mb

Код: plsql

1.
2.
3.
4.
5.
6.
7.
8.

Sort  (cost=15954.23..16191.63 rows=94960 width=47) (actual time=56611.277..63083.375 rows=94967 loops=1)
   Sort Key: display_name
   Sort Method: external merge  Disk: 5352kB
   ->  Seq Scan on tbl  (cost=0.00..5180.90 rows=94960 width=47) (actual time=7.892..218.937 rows=94967 loops=1)
         Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
         Rows Removed by Filter: 623
 Total runtime: 63097.712 ms
(7 rows)

...

Рейтинг:

0 / 0

19.05.2016, 16:09

| Ответить | Цитировать | Написать

25 сообщений из 30, страница 1 из 2

все

Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / SELECT работает слишком медленно с ORDER BY

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=53&msg=39238837&tid=1997232]:	0ms
get settings:	8ms
get forum list:	13ms
check forum access:	2ms
check topic access:	2ms
track hit:	146ms
get topic data:	11ms
get forum data:	3ms
get page messages:	66ms
get tp. blocked users:	1ms
others:	252ms

total:	504ms

	Необходимые cookie
	Cookie для сбора статистики
	Cookie для маркетинга и рекламы