On 2026-03-18 04:52, Merlin Moncure wrote:
On Tue, Mar 17, 2026 at 4:16 PM Alexey Ermakov
<[email protected]> wrote:
On 2026-03-18 03:01, Merlin Moncure wrote:
I've been maintaining an airflow style orchestrator in pl/pgsql,
and it's revealed a performance issue I just can't solve. There
is a table, task, which may normally contain billions of rows,
but only a tiny portion is interesting for specific reasons—a
common pattern in task-type systems.
...
I'm wondering if there are other tricks that might apply here,
for example, multi column index statistics...curious if anyone
has thoughts on that.
Any suggestions?
merlin
Hello. I think planner doesn't have information about distribution
of *async.task_execution_state(task)* unless it's part of any full
index. I would try to give that with extended statistics
(postgresql 14+):
create statistics (mcv) task_task_execution_state_stat on
((async.task_execution_state(task))) from async.task;
analyze async.task;
If that won't help - please show distribution from pg_stats_ext
view for extended statistic above.
This unfortunately fails, probably because the table type includes
system columns (despite not using them).
orchestrator_service_user@orchestrator=> create statistics task_stats
(mcv) on (async.task_execution_state(task)) from async.task;
ERROR: statistics creation on system columns is not supported
This would require some refactoring to fix.
Interesting... In that case functional index should help (as it also
makes statistic for the planner):
create index concurrently on task_task_execution_state_idx async.task using
btree ((async.task_execution_state(task)));
analyze async.task;
Perhaps multicolumn index will also help for queries but hard to say
without knowing distributions. We could check state distribution info
after index creation and analyze with query like this:
select * from pg_stats where tablename = 'task_task_execution_state_idx' \gx
--
Alexey Ermakov
