Есть табличка T с полями A, B, C и D.
Тип полей не так важен, главное, что к ним применимы опреатры сравнения больше/меньше (int,float,timestamp,...).
Запросы к этой таблице имеют следующие вариации:
1) Всегда применяется сортировка (ASC или DESC) по одному любому полю из перечисленных.
2) Возможны ограничения min/max (как только min, только max, так и оба) для любых из перечисленных полей в любых комбинациях.

При определенном кол-ве строк всё работает медленно. В этом и проблема.

Что уже сделано:
1) Построены b-tree индексы по всем полям, что дало прекрасный результат в ускорении запросов где:
а) происходит только сортировка
б) вместе с сортировкой применяются низкоселективные ограничения
в) когда высокоселективное ограничение применяется к тому же полю, по которому идет сортировка
В принципе результат предсказуемый.

Наверное уже ясно когда возникают проблемы. А именно при высокоселективных ограничениях по полю, отличному от поля сортировки.
Селективность таких ограничений может быть и нулевой. Тогда вообще труба.

Конкретика:


Из EXPLAIN, как и ожидалось, Pg бежит по индексу поля сортировки (надеясь на низкую селективность ограничений?).
views > 10000000 - высокоселективный фильтр и высекает всего-то 64 записи из 4млн.
Хотя индекс по views тоже есть и конечно выгоднее было бы пробежать по нему. Но как видно Pg поступил иначе.

Думаю сделать частичные индексы:
create index "..._index" on T (col) where {expr};
где {expr} - высокоселективное условие по полю, отличному от col. Но я так задолбаюсь с:
1) Выяснением подходящего порога {expr}
2) В попытках не забыть все комбинации (col, {expr}) и на каждый построить индекс.
Здравый смысл, подсказывает, что Pg сам должен анализировать распределение по столбцам (хранить некоторые перцентили например) и из этой статы понимать когда бежать по индексу сортировки и применять фильтр условия, а когда по индексу условия и потом сортировать.
Т.е. банально имея хотя бы 0-ый и 100-ый перцентили можно ускорить все запросы с where col > {max of col} и where col < {min of col}. Хотя я не знаю как Pg хранит стату и работает с ней. Перцентили - лишь моё первое пришедшее на ум предположение.

Проблема не кажется мне уникальной. Как с ней бороться?

автор Bitmap Heap Scan on communities_post (cost=683.11..89781.83 rows=36474 width=24) (actual time=0.178..4.924 rows=64 loops=1)
Recheck Cond: (views > 10000000)
Heap Blocks: exact=52
-> Bitmap Index Scan on communities_post_views_index (cost=0.00..673.99 rows=36474 width=0) (actual time=0.036..0.036 rows=65 loops=1)
Index Cond: (views > 10000000)
Planning time: 1.441 ms
Execution time: 5.004 ms

По views есть индекс, поэтому всё норм.

Проблема, когда столбец в where не совпадает со столбцом в orer by:


автор Limit (cost=0.43..10432.98 rows=500 width=20) (actual time=41953.300..49853.537 rows=64 loops=1)
-> Index Scan Backward using communities_post_published_at_index on communities_post (cost=0.43..760846.33 rows=36465 width=20) (actual time=41953.291..49853.446 rows=64 loops=1)
Filter: (views > 10000001)
Rows Removed by Filter: 4332922
Planning time: 0.238 ms
Execution time: 49853.797 ms
Как я понимаю этот вывод - Pg сильно ошибается. Селективность всего 64 строки. Pg должен был предсказать это по гистограмме столбца views и не идти по индексу столбца published_at (сортировка), а идти по индексу столбца views.




автор name | setting
----------------------+---------
cpu_index_tuple_cost | 0.005
cpu_operator_cost | 0.0025
cpu_tuple_cost | 0.01
parallel_setup_cost | 1000
parallel_tuple_cost | 0.1
random_page_cost | 4
seq_page_cost | 1

ВАЖНО: я новичок, поэтому не исключайте глупых ошибок с моей стороны :)

Ранее уже обновлял стату. Все-же сделал еще раз:


авторINFO: analyzing "public.communities_post"
INFO: "communities_post": scanned 30000 of 172480 pages, containing 756270 live rows and 105186 dead rows; 30000 rows in sample, 4348048 estimated total rows




авторBitmap Heap Scan on communities_post (cost=683.49..89894.95 rows=36524 width=24) (actual time=0.279..5.328 rows=64 loops=1)
Recheck Cond: (views > 10000000)
Heap Blocks: exact=52
-> Bitmap Index Scan on communities_post_views_index (cost=0.00..674.36 rows=36524 width=0) (actual time=0.022..0.022 rows=65 loops=1)
Index Cond: (views > 10000000)
Planning time: 1.124 ms
Execution time: 5.413 ms




автор default_statistics_target
---------------------------
100
(1 row)


P.S: Ставил Pg 10.4 на Ubuntu 16.04 из официального репозитория от postgresql.org
Система (1 core, 1 Gb RAM, 30 Gb HDD) дохленькая но как я понял проблема не в ней.
postgresql.conf почти не менял:
автор#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 256MB # min 128kB
# (change requires restart)
#huge_pages = try # on, off, or try
# (change requires restart)
temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
work_mem = 4MB # min 64kB
maintenance_work_mem = 64MB # min 1MB
#replacement_sort_tuples = 150000 # limits use of replacement selection sort
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
max_stack_depth = 2MB # min 100kB
dynamic_shared_memory_type = posix # the default is the first option
# supported by the operating system:
# posix
# sysv
# windows
# mmap
# use none to disable dynamic shared memory
# (change requires restart)

# - Disk -

#temp_file_limit = -1 # limits per-process temp file space
# in kB, or -1 for no limit

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0 # 0-100 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
#bgwriter_flush_after = 512kB # measured in pages, 0 disables

# - Asynchronous Behavior -

#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
#max_worker_processes = 8 # (change requires restart)
#max_parallel_workers_per_gather = 2 # taken from max_parallel_workers
#max_parallel_workers = 8 # maximum number of max_worker_processes that
# can be used in parallel queries
#old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate
# (change requires restart)
#backend_flush_after = 0 # measured in pages, 0 disables

Maxim Boguk,

Точно!
Залез в postgresql.conf и установил default_statistics_target = 1000



И теперь мой первоначальный запрос


Спасибо.

Интересен один момент.
Вы сказали
Maxim Boguk... При таком размере таблицы стоит поднять default_statistics_target до 1000 или даже до 10000...
И действительно сейчас я наблюдаю всё еще немалую ошибку rows=3504 vs rows=65. Как я понимаю показатель default_statistics_target коррелирует с количеством строк в таблице. Но 10000 - это возможный максимум, в то время как в моей табличке всего 4млн строк. А как люди живут, когда в таблице строк на порядок/порядки больше чем в моей?