Re: Composite Datums containing toasted fields are a bad idea(?)

On 2014-04-27 14:18:46 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> >> On 2014-04-25 12:05:17 -0400, Tom Lane wrote:
> >>> Meh ... is it likely that the columns involved in an ordering comparison
> >>> would be so wide as to be toasted out-of-line? Such a query would only be
> >>> fast if the row value were indexed, which would pretty much preclude use
> >>> of wide columns.
>
> > Just for some clarity, that also happens with expressions like:
> > WHERE
> > ROW(ev_class, rulename, ev_action) >= ROW('pg_rewrite'::regclass, '_RETURN', NULL)
> > ORDER BY ROW(ev_class, rulename, ev_action);
>
> > which is what is generated by such query generators - where the leading
> > columns *are* indexed but not necessarily unique.
>
> Ah, I see. Well, we're pretty darn stupid about such queries anyway :-(.
> Your first example could be greatly improved by expanding the whole-row
> Var into a ROW() construct (so that RowCompareExpr could be used), and
> the second one by exploding the ROW() order-by into separate order-by
> columns.

The problem is that - at least to my knowledge - it's not possible to do
the WHERE part as an indexable clause using individual columns.

> On the whole I feel fairly good about the opinion that this change won't
> be disastrous for mainstream usages, and will be beneficial for
> performance some of the time.

I am less convinced of that. But I don't have a better idea. How about
letting it stew in HEAD for a while? It's not like it's affecting all
that many people, given the amount of reports over the last couple
years.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services