On 2014-04-25 12:05:17 -0400, Tom Lane wrote:
> Andres Freund <[email protected]> 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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
