Query Optimization and Execution Plans Questions

Direct answer. Predicate pushdown means moving a filter as early as possible in a query's execution, ideally all the way down to the storage layer itself, so the engine reads less data from disk in the first place rather than reading everything and filtering afterward in memory.

Structured elaboration. Against columnar formats like Parquet or ORC, files are typically organized into row groups or stripes, each carrying summary statistics (like min/max values) for every column. If a filter can be pushed all the way down to the storage layer, the engine can skip entire row groups whose min/max range can't possibly satisfy the filter, without even reading their column data off disk, which is a much bigger win than filtering rows after they've already been read into memory. The same principle applies through a foreign data wrapper or a similar external-source connector: pushing a filter down to the remote system means less data crosses the network and less work happens locally, versus pulling everything across first and filtering client-side.

Worked example. Consider a wide, JSON-payload-heavy analytics table where a query filters on a narrow, well-indexed timestamp range and only needs a couple of columns. If the filter and the column selection both get pushed down to the storage layer, the engine reads only the row groups whose timestamp range overlaps the filter, and only the specific columns requested, skipping the (often much larger) JSON payload column entirely; if pushdown fails for some reason (the filter is wrapped in a function the storage layer's statistics can't reason about, for instance), the engine instead reads every row group's relevant columns into memory and applies the filter there, doing far more I/O for the identical logical result.

To verify pushdown is actually happening rather than assumed, check your engine's plan output for an explicit indicator (many engines report something like "filter pushed down" or a bytes-read figure noticeably smaller than the table's full size on disk) rather than trusting that a filter which LOOKS pushdown-friendly actually was.

Trade-offs and pitfalls. Pushdown eligibility is sensitive to exactly how a predicate is written, the same logical filter expressed with a function wrapped around the column, or compared against a mismatched type, can silently fail to push down even though a semantically-equivalent bare-column comparison would have; this is the same underlying sensitivity that defeats sargability for index usage, applied one layer further down the stack.

Direct answer. Confirm duplication is the cause (rather than a logic bug) by comparing the row count immediately after the join to the row count you'd expect from the smaller side alone, and by checking, on a small sample, whether specific keys legitimately have multiple matches on both sides; fix it either by aggregating one side down to uniqueness before the join, or by deduplicating the joined result afterward in a way that doesn't silently discard rows you actually need.

Structured elaboration. An unanticipated many-to-many relationship means a join key you expected to be unique on at least one side actually has multiple matching rows on BOTH sides for some values, which multiplies rather than merely combines: two matching rows on each side for the same key produce four joined rows, not two, which then inflates any downstream SUM or COUNT computed from that joined result. To confirm this is the cause, pick a specific key value, count its rows on each side of the join independently, and multiply those counts together; if that product matches the number of joined rows you're seeing for that key, you've confirmed genuine multiplicative duplication rather than, say, a join condition that's simply too loose.

Two realistic fixes, and they aren't interchangeable: pre-aggregate one side down to one row per join key BEFORE the join (appropriate when you only actually need a single value per key from that side, like a "most recent" or "total" per key); or deduplicate the JOINED result afterward using a window function or an explicit grouping, which is appropriate when you genuinely need attributes from multiple matching rows on the many-side and the "duplication" is actually correct given the relationship, just not what a naive downstream SUM assumed.

Worked example. A product joined to promotions where a product can have multiple active promotions and a promotion can apply to multiple products is a genuine many-to-many; a report computing "total sales per product" that naively joins in promotions and then sums a sales column will multiply each product's real sales figure by however many active promotions it happens to have, a bug that's invisible on products with exactly one promotion and only becomes obvious (and often only gets NOTICED) on products with several.

Trade-offs and pitfalls. The riskiest version of this bug is exactly the one described in the worked example: it's silently correct for the common case (one match per key) and silently wrong only for the less common case (multiple matches), which means it can ship, look fine in testing, and only surface as a real discrepancy once someone happens to look at a key with genuine multiplicity, often much later and much harder to trace back to its root cause.

Complexity

Detecting this costs a handful of targeted counting queries against a specific suspect key value, not a full reprocessing of the dataset, so confirmation is cheap even though the underlying bug can be expensive to have shipped.

Edge cases

A key with exactly one match on both sides produces exactly one joined row and looks completely correct, which is precisely why this bug tends to survive testing against a small, low-multiplicity sample and only appears once the real data includes genuine multi-match keys.

Direct answer. A Bloom filter, built on the build side's join key values and shipped to the workers handling the probe side, lets the probe side cheaply test "could this row possibly match anything on the build side" and discard the ones that provably can't, before ever transferring or joining them, at the cost of a small, tunable false-positive rate that never produces a false NEGATIVE.

Structured elaboration. A Bloom filter is a compact, probabilistic membership structure: querying it for a value either says "definitely not present" (always correct) or "possibly present" (which can occasionally be wrong, a false positive, but never wrong in the other direction). In a distributed join, building one from the smaller (build) side's join keys and shipping just that small filter, rather than the full build-side data, to wherever the probe side lives lets the probe side filter out rows that DEFINITELY have no match before those rows ever need to be shuffled or transferred for the actual join, which can dramatically cut network transfer when the join is highly selective (few probe-side rows actually have a match).

Worked example. Joining a small, highly-selective dimension table to a much larger fact table across a network boundary: building a Bloom filter from the dimension table's keys and applying it against the fact table BEFORE shuffling fact-table rows to wherever the join actually executes lets you discard the (likely large) majority of fact-table rows that can't possibly match, transferring only the (likely much smaller) set that pass the filter, of which a small further fraction might be false positives that get correctly eliminated by the real join condition once actually compared.

Trade-offs and pitfalls. The Bloom filter's false-positive rate is a genuine tuning knob: a smaller filter (less memory, less to transfer) has a higher false-positive rate, meaning more rows survive the filter unnecessarily and still get shuffled for the real join to eventually discard; a larger filter costs more memory and transfer overhead itself but filters more precisely. This only pays off when the join is genuinely selective, if most probe-side rows DO have a match, the filter does little useful discarding and its own construction and transfer cost becomes pure overhead rather than a net win.