2

I am trying to optimize a query that joins two large tables (40MM+ rows) in PostgreSQL 15.4.

SELECT files.id, ARRAY_AGG(b.status)
FROM files
LEFT OUTER JOIN processing_tasks b
    ON (files.id = b.file_id AND b.job_id = 113)
WHERE files.round_id = 591
GROUP BY files.id;

Two explain (analyze) plans for the exact same query are at:

In files, 908,275 / 39,000,105 (2.3%) tuples have round_id=591; it is static.
In processing_tasks, 4,026,364 / 60,780,802 (6.6%) tuples have job_id=113, and this value is going to keep getting more and more common as rows get inserted, maybe reaching 15% of the table.

The "Comments" tab at those links include table & index definitions and shows that the pg_stats data includes these most-common values.

I'd be happy with either of a couple possible goals:

  1. The 3–4 seconds taken when using Index Scan is acceptable, and if this is the best I can do, then should I continue to override enable_seqscan even in production? (inside a transaction I guess)

  2. But I'd much rather reduce that 3–4 seconds further, to under 2-seconds, and keep it there even as processing_tasks grows.

3
  • status is varchar(1). What are possible values for status? Is it a boolean under the hood? Or just a few distinct characters? How are possible values enforced? Aggregation does not seem to fold any rows, so always a single status in the result? If not, can there be duplicates? Is the order of array elements in the result significant? Dec 8, 2023 at 13:33
  • About status: There are 6 possible values but mostly dominated by one value. processing_tasks:files is many:1 (multiple attempts to process a single file, e.g. if one failed) so the aggregation sometimes (rarely) produces multiple statuses. The order of array elements is not significant. Ultimately the whole query returns one row per file, and summarizes all processing attempts for that file.
    – Jeff G
    Dec 8, 2023 at 16:33
  • But you are not going to display close to 1 million files in the dashboard you mentioned in comments. I assume you only actually need to refresh data currently displayed for each client? Dec 9, 2023 at 2:59

2 Answers 2

4

First there is a suspiciously slow seq scan at 42 MB/s in your query. As jjanes says, that's 5400rpm laptop hard drive speed, unless there are many other processes hitting the disk at the same time. It it's SSDs then you have an issue. If you are not using SSDs on a database then you should think about it. I get about 3GB/s read from zstd compressed btrfs on my cheap desktop SSD, so that seq scan takes about one second in the example below.

In both of your plans, it pulls :

  • 302,758 rows from files with the round_id you asked
  • 1,342,121 rows from processing_tasks, but these are only filtered on job_id. They may also contain many rows for files with the wrong round_id.

Unfortunately EXPLAIN does not tell what proportions of these rows were fetched and then kept or thrown away because they had the wrong file_id. You should investigate with a few count queries. There are two cases:

  • If most of the rows actually had good file_id's and were kept in the join.

In this case you can't do much since it has to fetch these rows. Your only options are either to not do the query, or make fetching the rows faster: SSDs/more RAM, or make the IO less random and do less of it by clustering the table or using a covering index.

  • If it did throw away a significant proportion of rows.

In this case the goal should be to not fetch the rows that will be discarded by the join. Perhaps a multicolumn index on processing_tasks would help. You already have a unique index on (job_id, file_id, task_id) and it doesn't get used, so perhaps (file_id,job_id) would be useful.

I created test data with roughly the same distribution and correlation:

CREATE UNLOGGED TABLE files (
    id         integer                 not null,
    url        character varying(1024) not null,
    bytes      bigint                          ,
    round_id   integer                 not null
);
INSERT INTO files 
    SELECT n, 'https://dba.stackexchange.com/questions/333837/improve-query-performance-of-filtered-left-outer-join-of-big-tables',
    0, 
    random()*50     -- values distributed randomly
--  n/500000        -- values nicely clustered in table
FROM generate_series(1,25000000) n;

ALTER TABLE files ADD PRIMARY KEY(id);
CREATE INDEX files_round_id ON files( round_id );
VACUUM ANALYZE files;

CREATE UNLOGGED TABLE processing_tasks (
    id             bigint                not null,
    task_id        character varying(64) not null,
    status         character varying(1)  not null,
    file_id        integer               not null,
    job_id         bigint                not null
);

TRUNCATE processing_tasks;
INSERT INTO processing_tasks
    SELECT n, 
    'x', 
    'x', 
    n/3, 
    n/18000000-(n%15)
FROM generate_series(1,60000000) n;
VACUUM ANALYZE processing_tasks;
SELECT * FROM pg_stats WHERE tablename='processing_tasks' AND attname='job_id';

ALTER TABLE processing_tasks ADD PRIMARY KEY (id);
CREATE INDEX processing_tasks_file_id ON processing_tasks(file_id);
CREATE INDEX processing_tasks_uniqueness ON processing_tasks(job_id, file_id, task_id);

To avoid benchmarking array_agg() I used max() instead:

EXPLAIN ANALYZE SELECT files.id, max(b.status)
FROM files
LEFT OUTER JOIN processing_tasks b
    ON (files.id = b.file_id AND b.job_id = 2)
WHERE files.round_id = 10
GROUP BY files.id;

With adequate work_mem (256MB), I get this plan: (not cached, cached) which is similar to your fast one, bitmap index scan on both tables plus hash join. It is quite slow and the bitmap index scan on files isn't very useful since it reads most of the table anyway, and SSDs may be fast at random reads but that's not a silver bullet. Please note your tables were cached in your run.

The amount of data read from files is very large, due to the fake url column I purposefully inserted. Thus, I create a covering index:

DROP INDEX files_round_id;
CREATE INDEX files_round_id_cov ON files( round_id ) INCLUDE ( id );

Let's redo the same query: the resulting plan is much better since it seeks round_id in the index and fetches file_id directly. This avoids a lot of IO on table files, but it will work only if all the columns you use in your query are in that covering index. Otherwise it will still have to fetch from the table.

If your table contains a few short columns plus large columns like URL, you can use vertical partitioning and store the URL in a secondary table. This is a bit similar to what TOAST does. This makes access to columns in the secondary table slower, but the main table becomes smaller. Or you can add more columns to the covering index.

Another solution is to CLUSTER table files, in this case by (round_id,file_id) but that takes a while and locks the table. The advantage is that files having the same round_id are adjacent in the table, which means the bitmap index scan will do mostly sequential IO instead of lots of random seeks, so it is faster (note this one is cached, so it's artificially fast).

None of that solves the IO problem on the other table. So I try a few indices on processing_tasks...

(job_id,file_id)     no effect, besides you already had it
(file_id,job_id)     no effect
(job_id,file_id) INCLUDE (status)

The last one works nicely since it allows a merge join which completes in less than 400ms. However this index is redundant with your unique constraint, so this combination should work better:

"processing_tasks_pkey" PRIMARY KEY, btree (id)
"processing_tasks_file_id_job_id" btree (file_id, job_id)
"processing_tasks_uniqueness" btree (job_id, file_id, task_id) INCLUDE (status)

IMO this is the best option, unless you have many different queries of the same type which all require their own special covering index. In this case you'd end up with a lot of duplicated data.

Another option is to just denormalize and put round_id in the processing_tasks table.

CREATE TABLE processing_tasks_2 AS
SELECT p.*, round_id FROM processing_tasks p
JOIN files f ON (f.id=p.file_id);
CREATE INDEX pt2t ON processing_tasks_2( round_id, job_id, file_id );

This works well and runs in ~500ms uncached, 255ms cached.

CREATE INDEX pt2tc ON processing_tasks_2( round_id, job_id, file_id ) INCLUDE (status);

This allows a merge join between two index only scans, which is the fastest possible option.

Not using the files table and only fetching the rows from processing_tasks_2 takes 16 milliseconds, but it gives a different result, without the left join.

All these optimizations are effective but they are targeted: these indices do eat up space and resources to maintain up to date. It's up to you to decide if they're worth that cost.

HOWEVER

Sure the query is slow, but if you want to optimize it, it must mean you're running it often. Otherwise it would be a 4AM batch job. If it needs to run under 4 seconds, maybe you run it every minute or even more frequent? So why do you need that 1 million row result so often?

This looks like a job management system which builds a todolist and assigns jobs to subprocesses or machines.

Are you actually using all the rows?

If you are not, and what you actually want is get the next batch of stuff that should be dispatched to workers, then there are simpler ways involving LIMIT or cursors, to only fetch a much smaller number of rows but do it more often.

If you want to poll worker status and are only looking for changes between two executions of this query, then postgres also has a notify mechanism, or you could also filter more precisely, etc.

2
  • The use case is: this powers a dashboard. The dashboard is not open 24/7 or anything, but when it is used, it's because tasks are processing and people want to monitor as file statuses change do to processing that's currently happening.
    – Jeff G
    Dec 8, 2023 at 16:51
  • OK. You can try the index optimizations I wrote about, but there's probably a way to actually give the dashboard what it wants without doing the big query. For example you could set a trigger on the status change events you want, that inserts events into a separate table. Or that updates counts into a summary table. Then you get the changes directly without having to constantly redo the big query, or the dashboard can use the summary table. But dashboard software may be uncooperative, these things really do love their constant huge queries lol
    – bobflux
    Dec 8, 2023 at 16:55
5

This is a weird plan. Part of the weirdness is just that depesz screws up the presentation of the parallel bitmap heap scan, showing the 'Buffers:' for just one of the workers as if it were the 'Buffers:' of the whole section of plan tree. Going to the "Source" tab gives the real info.

Which is still pretty weird. Buffers: shared hit=108093 with no reads. This section of the plan needs almost 850MB of data and every single bit of it just happens to already be in shared_buffers? Is this just because you ran this particular query parameterization over and over again, but only showed us the fastest run of it? If so, that seems like a pretty unrealistic way to optimize your queries. What is it like if you run it from a cold cache, or if you run a new parameterization of it which you haven't run recently?

There is no way to tell PostgreSQL to expect to find all the data needed for one particular query already in the cache. There is effective_cache_size, but that is for when a single query execution is expected to hit the same data repeatedly, not for when different executions all hit the same data as each other. You could screw around with the seq and random page_cost settings, but it is unlikely that whatever setting you come up with would be globally applicable. If you need to tweaks settings just for the scope of this one query, it would be more straight forward to tweak enable_seqscan rather than those other things.

Or, you could use a query hint. There is a 3rd party extension which implements them, https://github.com/ossc-db/pg_hint_plan. It is even available in RDS; I don't know about other managed db providers.

Alternatively, an index on (job_id, file_id,status) should enable an index-only scan on this table, and that would probably look better to the planner than the seq scan would if many of the pages are marked allvisible.

Some other comments on this: Your work_mem seems much too low if you routinely run such large queries; raising it wouldn't fix the seqscan issue, but might make the fast plan even faster. Your IO seems bad, 40MB/s seems like something from a 15 year old laptop, not modern server-class hardware (although that is apparently for each worker, so really 120MB/s which is better but still not great).

1
  • Great ideas. I generated those plans one after the other, so the first link shows cache misses where the second shows hits. This is an Azure hosted server (series D2ds v4), I think they want to upsell you on faster IO. They do appear to support pg_hint_plan; I will try some of your ideas and report back. Thank you!
    – Jeff G
    Dec 8, 2023 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.