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

On 2014-04-25 12:05:17 -0400, Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.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_rewriter  (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=11Planning time: 0.141 msExecution 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_rewriter  (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=152Planning time: 0.139 msExecution 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