Additional optimizations: 1. Create a compound index on resource_depedency(prior_id, label) which reduces work within recursion. 2. Rewrite query to minimize # of joins in the recursive CTE. This greatly reduces the size the the Work Table and increases efficiency. explain.dalibo.com/plan/6d8bb7a7ccb948h3

We have individual indexes on prior_id, resource_id, and label. Adding label to make a compound index for ix_resource_dependency_prior_id improves performance as it is easier to match that constraint on label. Without that index, we sometimes fall into Bitmap Index scan for both prior_id and label, which performs poorly. resource.barcode column is indexed, but perhaps due to materialization we can't use an index (unless we resort to a temporary table). explain.dalibo.com/plan/6d8bb7a7ccb948h3 Includes a rewrite to only use minimal joins in Recursive CTE. Now under 1 second.

Maybe a more general question to summarize: is there a way to detect and prevent cycles in a recursive CTE without duplicating work. (UNION works to remove duplicates) It seems like each "fan in" multiplies row counts. If we drop path and cycle, I think the CTE table shrinks a fair deal.

Long term that might be a consideration, but my intuition would be that requires a good bit of schema rework to split the PostgreSQL information into a graph DB. The graph is contextualized and filtered with other tables / PKs in the PostgreSQL DB. One problem I am uncovering is that each "fan-in" multiplies the # of CTE rows as the "path" column differs, 1 -> 1000 -> 1 -> 10 where I alternate fanning out back to fanning in results in 2021 unique parent->child rows (including the terminal left join null child nodes). Without removing unique paths, the CTE is 32000 rows.

Does looping over the same Shared Buffer page many times ("720GB Shared Buffers Hit" or 93MM pages but actual unique pages ~131) indicate a possible performance problem?

I think I understand the mystery of this crazy large Shared Buffers Hit numbers. This is an artifact (at least that is my understanding of the notes) of reading shared buffers in a loop. Reading the same shared buffer page multiple times in a loop will still increment shared buffers hit. 720GB shared buffers hit does not make sense in the context of a 4GB shared buffers setting :). Read Buffers (share cache miss) on the other hand are only counted once, even in a loop. pganalyze.com/blog/5mins-explain-analyze-buffers-nested-loop‌​s @jjanes calculation above now makes more sense

I've seen another case of this issue in a PG13 deployment. The query in question there was using a CTE with recursion. It also had hundreds of GB of buffer hits, but the underlying tables I think are much smaller. Another case of PG seeming to get carried away with Nested Loops within the CTE.

Still not quite sure why we had to hit 720 GB of buffers for that Index Scan on btree in PG10. The step_instance_sample table is certainly not even that large. The query plan in PG13 is quite different as CTEs can be planned (non-materialized default) with the rest of the query which certainly could change the outcome.

For the Vacuum in PG13: INFO: vacuuming "public.step_instance_sample", INFO: scanned index "idx_sis_sample" to remove 2580 row versions, INFO: "step_instance_sample": removed 2580 row versions in 1236 pages. INFO: index "step_instance_sample_pkey" now contains 192408 row versions in 812 pages INFO: index "idx_sis_sample" now contains 192408 row versions in 222 pages DETAIL: 0 index row versions were removed.

After upgrading to PG13, running vacuum analyze, REINDEX (concurrently) the step_instance_sample table, the production query is down from 10 minutes to 50 seconds. For the scope of the report and the join strategies used, this makes more sense (despite the still long run time). The updated query plan (obsfuscated) is here explain.depesz.com/s/TIGb The remaining performance bottlenecks are more obvious. The query targets too many rows (full system scan), and uses wildcard filters on varchar fields. The grouping of those results (array_agg) is also expensive.

Neither side of the join is a primary key or unique. Step instance sample identifies samples in an experimental step. child_parents_connection_2 (an alias for resource_dependency) defines connections between 2 samples). sample_id is not unique in either step_instance_sample or resource_dependency. The sample_id is a unique primary key in another table - workflowable_resource. workflowable resource has a 1 to many relationship to both step_instance_sample as well as resource_dependency. This might not be enough, but providing it in hope it can give some context.

I will give some of the above operations a try. We had performed a VACUUM ANALYZE on all tables just before this query. The Index Scan there is the most time consuming and concerning - this is our step_instance_sample table shown above. It shows 720GB of buffers scanned in those 93MM pages, which seems strange. (sis.sample_id = child_parents_connection_2.resource_id) It is being joined back to 178,700 rows in a nested loop (all preceding joins), but had planned for only 1 row. The SIS table was 190,000 n_live_tup. Before vacuum 13,284 n_dead_tup there. btree index on both sides. Int type

I think my naive assumption was that sending less statements to the DB might be more efficient, but if I exceed work_mem (not the case here) or if the stat limitations drive the planner in the wrong direction - then the only recourse is to split the query into multiple statements which can correct or mitigate some of the estimation errors. Any rule of thumb to when a query is too complex? It seems there are a lot of tradeoffs --- more statements means more of a hit for app->db latency (handshakes), but too complex and the planner can fail to find a good plan in a few ways.

@FrankHeikens PG10 defaults for most settings (collapse limits at 8). Memory settings - 256MB work_mem, effective_cache_size 24GB, shared_buffers 8GB, temp_buffers 8MB, maintenance_work_mem 64MB (32GB RAM DB. Going to bump temp_buffers, maintenance_work_mem, and perhaps work_mem. These timings after VACUUM ANALYZE. Is there a good cutoff point for - "this should be 2 or more query statements, vs 1 statement" or does that just require experimentation? I think breaking up the query at least limits impacts of estimation errors.

This is specific to version of the web server software which is using Postgres. It has only been validated with PG13 in that specific version (that work has already been done, a while back). I believe we are currently working on PG15-17 validation in an upcoming release. I am going to port this dataset to PG17 to see if there is any impact on performance, but it may be some time before we can validate with those newer versions.