When properties are stored one row per property (property name plus value) rather than as columns, how do you return several properties as columns of a single result row, and why does that get expensive as the number of properties and rows grows?

middleshould knowfreq 40%

answer

Conditional aggregation vs N self-joins
N rows per entity, scattered across pages
Multi-filter = intersect row sets
Casts on the value column kill index range scans
Filter first, pivot the survivors

basics

~20 s

You pivot: either join the value table once per property, or group by the entity and use conditional aggregation (MAX of a CASE per property). Cost grows because each property is another join or another pass, row counts multiply, and the planner's estimates for each attribute predicate are unreliable.

solid answer

~50 s

Two standard shapes. Conditional aggregation reads the value rows for the wanted entities once and collapses them: GROUP BY entity_id with MAX(CASE WHEN attribute = 'color' THEN value END) AS color per property. Or you self-join the value table once per property, which lets each join use its own index but multiplies the joins.

Expense comes from three places. First, volume: one entity with thirty properties is thirty rows plus index entries, so a scan touches thirty times the data a wide table would. Second, filtering on several properties means intersecting several row sets, and the optimizer estimates each one badly because all attributes share one column's statistics, so it picks wrong join orders. Third, outer joins are required for optional properties, and any property that is absent silently becomes NULL rather than a defined default.

Mitigations: index (attribute_id, value, entity_id), filter down to a small entity set first and pivot only that set, or maintain a materialised pivot for read paths.

Two standard shapes. Conditional aggregation reads the value rows for the wanted entities once and collapses them: `GROUP BY entity_id` with `MAX(CASE WHEN attribute = 'color' THEN value END) AS color` per property. Or you self-join the value table once per property, which lets each join use its own index but multiplies the joins. Expense comes from three places. First, volume: one entity with thirty properties is thirty rows plus index entries, so a scan touches thirty times the data a wide table would. Second, filtering on several properties means intersecting several row sets, and the optimizer estimates each one badly because all attributes share one column's statistics, so it picks wrong join orders. Third, outer joins are required for optional properties, and any property that is absent silently becomes NULL rather than a defined default. Mitigations: index `(attribute_id, value, entity_id)`, filter down to a small entity set first and pivot only that set, or maintain a materialised pivot for read paths.

code

sql · 8 lines

sql

SELECT e.entity_id,
       MAX(CASE WHEN a.code = 'color' THEN v.value_text  END) AS color,
       MAX(CASE WHEN a.code = 'size'  THEN v.value_text  END) AS size,
       MAX(CASE WHEN a.code = 'price' THEN v.value_number END) AS price
FROM   entity_value v
JOIN   attribute a ON a.attribute_id = v.attribute_id
WHERE  v.entity_id IN (SELECT entity_id FROM candidate_ids)
GROUP  BY e.entity_id;

go deeper

for a junior

Show that you can write a conditional-aggregation pivot and explain in words that one entity spans many rows, so reassembling it costs joins or grouping.

for a middle

Compare the two pivot shapes and pick correctly per workload; mention indexing (attribute leading the key) and the filter-then-pivot two-phase approach.

for a senior

Bring in the optimizer: blended statistics, cardinality error compounding across attribute predicates, spills from under-estimated hash builds, plus materialised pivots and promotion of hot attributes.

for a principal

Position the pivot cost as a systemic tax on every consumer including BI and ETL, and decide where the denormalised read model lives and who owns keeping it correct.

The two pivot shapes

Conditional aggregation. Select the value rows for the entities you want, group by the entity, and use one aggregate per property that ignores every other property. This scans the relevant value rows once, so it scales with (entities x properties per entity), not with the number of properties squared. It is the usual choice when you want many properties for a moderate set of entities.

Self-join per property. Join the value table to itself once per property, each join filtered to a single attribute. Each join can use the (attribute_id, entity_id) index, so it is efficient when you want few properties, but joins accumulate: ten properties means ten joins, and each optional property needs a LEFT JOIN, which constrains join reordering.

Where the cost comes from

Row multiplication. In a wide table, an entity is one row and a full read is one page visit. In the row-per-property form, an entity is N rows spread across the table, plus N index entries. Reading 1,000 entities with 30 properties touches 30,000 rows, and those rows are typically clustered by attribute or by insertion order rather than by entity, so a given entity's properties are scattered across pages. Random I/O and buffer-cache pressure rise accordingly.

Filter intersection. "Colour red AND size large AND price under 50" is not a single predicate against a single row. It is three separate row sets that must be intersected on entity_id. Each set is a separate index range scan, and every extra filter adds another semi-join. That is exactly the workload the wide-table version handles with one index and one predicate list.

Bad estimates. The optimizer keeps statistics per column. Because all values live in one column, it cannot know that attribute = 'status' has three distinct values while attribute = 'serial' is unique. Every predicate looks equally selective, so it may build a hash on the wrong side, choose a nested loop where a hash join is right, or under-allocate memory and spill to disk. This is the failure mode people notice as "it was fine in test, it is terrible in production": estimation error compounds with each additional attribute predicate.

Type coercion in predicates. If the value column is text, a numeric range filter needs a cast, and a cast around the indexed column prevents ordinary index range scans unless you build an expression index. Worse, a cast can raise an error when a row from an unrelated attribute holds non-numeric text, because the engine is free to evaluate the cast before the attribute filter.

Absence versus NULL. A property that was never recorded produces no row, so after an outer join it appears as NULL - indistinguishable from a property explicitly recorded as unknown. Any downstream logic that needs that distinction has to model it explicitly.

Practical mitigations

Index for the access path. (attribute_id, value, entity_id) supports "find entities where this attribute has this value" as a covering index; (entity_id, attribute_id) (often the primary key) supports "fetch all properties of these entities".
Two-phase queries. Resolve the filter first to a small list of entity ids using the most selective attribute, then pivot only those entities. This bounds the expensive step.
Materialise the pivot. Keep a denormalised table or materialised view with the commonly queried attributes as real columns, refreshed on write or on a schedule. Reads become ordinary relational reads; you pay staleness and maintenance instead.
Promote hot attributes. If a property is filtered, sorted or reported on regularly, it is not really user-defined long-tail data. Give it a real typed column.
Never let reporting tools consume the raw form. BI tools generate wide selects; every one of them will independently reinvent the pivot, usually badly.

What the interviewer is checking

That you can write both pivot shapes, that you can say which is better for many-properties-few-entities versus few-properties-many-entities, and that you understand the cost is not merely "more joins" but scattered rows, intersected filters and unreliable cardinality estimates. Candidates who answer only "use a pivot function" have not engaged with why the shape is slow.

## The two pivot shapes **Conditional aggregation.** Select the value rows for the entities you want, group by the entity, and use one aggregate per property that ignores every other property. This scans the relevant value rows once, so it scales with (entities x properties per entity), not with the number of properties squared. It is the usual choice when you want many properties for a moderate set of entities. **Self-join per property.** Join the value table to itself once per property, each join filtered to a single attribute. Each join can use the `(attribute_id, entity_id)` index, so it is efficient when you want few properties, but joins accumulate: ten properties means ten joins, and each optional property needs a LEFT JOIN, which constrains join reordering. ## Where the cost comes from **Row multiplication.** In a wide table, an entity is one row and a full read is one page visit. In the row-per-property form, an entity is N rows spread across the table, plus N index entries. Reading 1,000 entities with 30 properties touches 30,000 rows, and those rows are typically clustered by attribute or by insertion order rather than by entity, so a given entity's properties are scattered across pages. Random I/O and buffer-cache pressure rise accordingly. **Filter intersection.** "Colour red AND size large AND price under 50" is not a single predicate against a single row. It is three separate row sets that must be intersected on `entity_id`. Each set is a separate index range scan, and every extra filter adds another semi-join. That is exactly the workload the wide-table version handles with one index and one predicate list. **Bad estimates.** The optimizer keeps statistics per column. Because all values live in one column, it cannot know that `attribute = 'status'` has three distinct values while `attribute = 'serial'` is unique. Every predicate looks equally selective, so it may build a hash on the wrong side, choose a nested loop where a hash join is right, or under-allocate memory and spill to disk. This is the failure mode people notice as "it was fine in test, it is terrible in production": estimation error compounds with each additional attribute predicate. **Type coercion in predicates.** If the value column is text, a numeric range filter needs a cast, and a cast around the indexed column prevents ordinary index range scans unless you build an expression index. Worse, a cast can raise an error when a row from an unrelated attribute holds non-numeric text, because the engine is free to evaluate the cast before the attribute filter. **Absence versus NULL.** A property that was never recorded produces no row, so after an outer join it appears as NULL - indistinguishable from a property explicitly recorded as unknown. Any downstream logic that needs that distinction has to model it explicitly. ## Practical mitigations 1. **Index for the access path.** `(attribute_id, value, entity_id)` supports "find entities where this attribute has this value" as a covering index; `(entity_id, attribute_id)` (often the primary key) supports "fetch all properties of these entities". 2. **Two-phase queries.** Resolve the filter first to a small list of entity ids using the most selective attribute, then pivot only those entities. This bounds the expensive step. 3. **Materialise the pivot.** Keep a denormalised table or materialised view with the commonly queried attributes as real columns, refreshed on write or on a schedule. Reads become ordinary relational reads; you pay staleness and maintenance instead. 4. **Promote hot attributes.** If a property is filtered, sorted or reported on regularly, it is not really user-defined long-tail data. Give it a real typed column. 5. **Never let reporting tools consume the raw form.** BI tools generate wide selects; every one of them will independently reinvent the pivot, usually badly. ## What the interviewer is checking That you can write both pivot shapes, that you can say which is better for many-properties-few-entities versus few-properties-many-entities, and that you understand the cost is not merely "more joins" but scattered rows, intersected filters and unreliable cardinality estimates. Candidates who answer only "use a pivot function" have not engaged with why the shape is slow.

Which pivot shape would you choose to fetch 40 properties for 10 entities, and which for 2 properties across a million entities?
For 40 properties on 10 entities, conditional aggregation wins: one index range scan on the entity ids returns roughly 400 rows and collapses them in a single grouping step, whereas 40 self-joins would be 40 separate access paths. For 2 properties across a million entities, self-joins are usually better because each join can drive off a selective `(attribute_id, value)` index and the engine can pick a hash join between two narrow row sets.
A range filter on a numeric property stored in a text value column is slow and sometimes errors out. Why, and what fixes it?
The comparison needs a cast, and wrapping the indexed column in a cast prevents an ordinary index range scan, so the engine scans and casts every row. It can also raise a conversion error because predicate evaluation order is not guaranteed, so the cast may be applied to rows belonging to other attributes. Fixes are a typed value column per datatype, or a partial expression index defined only over rows for that attribute.

saying these in an interview costs you the question

Believing a vendor PIVOT function makes the underlying access cost disappear
Assuming an index on the value column alone is enough - the attribute must lead the key for a selective lookup
Ignoring that an absent property becomes NULL and is then confused with an explicitly unknown value
Casting the value column inside the WHERE clause and expecting the index to still be used
Claiming the pivot cost is negligible because 'the database caches everything'

When properties are stored one row per property (property name plus value) rather than as columns, how do you return several properties as columns of a single result row, and why does that get expensive as the number of properties and rows grows?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

The two pivot shapes

Where the cost comes from

Practical mitigations

What the interviewer is checking

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions4 questions

▾related topics4 topics

The two pivot shapes

Where the cost comes from

Practical mitigations

What the interviewer is checking

answer

go deeper

context