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

On 2014-04-25 18:25:44 +0200, Andres Freund wrote:
> On 2014-04-25 12:05:17 -0400, Tom Lane wrote:
> > Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > > The case I am worried most about is queries like:
> > > SELECT a, b FROM f WHERE f > ROW(38, 'whatever') ORDER BY f;
> > > I've seen such generated by a some query generators for paging. But I
> > > guess that's something we're going to have to accept.
> >
> > 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.
>
> In the cases I've seen it it was usually used in addition to a indexable
> condition, just for paging across different http requests.
>
> As completely ridiculous example:

> before:
> postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pg_rewrite r WHERE r > ('x'::name, '11854'::oid, NULL, NULL, NULL, NULL);
> QUERY PLAN

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.

Greetings,

Andres Freund

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