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