### Merge Append ![[assets/ex_mergeappend.png|256]] ### The Explain Trace ```sql -- Merging two sorted index scans with UNION ALL + ORDER BY EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) SELECT * FROM (SELECT id FROM animals WHERE id < 100 ORDER BY id) s1 UNION ALL SELECT * FROM (SELECT id FROM animals WHERE id >= 100 AND id < 200 ORDER BY id) s2 ORDER BY id; ``` ```text Merge Append (cost=1.58..14.31 rows=199 width=4) (actual time=1.008..1.018 rows=199 loops=1) Sort Key: animals.id Buffers: shared hit=6 -> Index Only Scan using animals_pkey on public.animals (...) -> Index Only Scan using animals_pkey on public.animals animals_1 (...) ``` --- - **Description**: Merges sorted results from multiple subqueries. - **Performance**: High performance as it uses a tournament tree or heap to merge pre-sorted inputs. - **Factors**: Number and size of input datasets, and the cost of the child sort/index scan nodes. - **Cost**: Sum of sorted subquery costs. ![[assets/ex_merge_append.svg|256]] - **Operates on**: [[Structures/Result Set]] - **Workloads**: - [[Workloads/LWLock/Buffers/BufferContent|LWLock: BufferContent]] - [[Workloads/IPC/Parallel/AppendReady|IPC: AppendReady]]