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

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
----------------------------------------------------------------------------------------------------------
Seq Scan on pg_rewrite r (cost=0.00..12.36 rows=36 width=720) (actual time=0.425..0.425 rows=0 loops=1)
Filter: (r.* > ROW('x'::name, 11854::oid, NULL::unknown, NULL::unknown, NULL::unknown, NULL::unknown))
Rows Removed by Filter: 109
Buffers: shared hit=11
Planning time: 0.141 ms
Execution time: 0.485 ms

after:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pg_rewrite r WHERE r > ('x'::name, '11854'::oid, NULL, NULL, NULL, NULL);
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on pg_rewrite r (cost=0.00..12.36 rows=36 width=720) (actual time=14.257..14.257 rows=0 loops=1)
Filter: (r.* > ROW('x'::name, 11854::oid, NULL::unknown, NULL::unknown, NULL::unknown, NULL::unknown))
Rows Removed by Filter: 109
Buffers: shared hit=152
Planning time: 0.139 ms
Execution time: 14.310 ms
(6 rows)

> I'm actually more worried about the function-returning-tuple case, as that
> might bite people who thought they'd use some cute functional notation or
> other and it wouldn't cost 'em anything.

Right, that's not actually all that infrequent :/.

Greetings,

Andres Freund

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