Hi,
Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
> SELECT toastable_col FROM t1
> WHERE f(t1.toastable_col)
> ORDER BY nonindexed;
Thanks for this example! it's true that the current design requires more
memory to sort since toastable_col is detoasted at the scan stage and it
is output to the sort node. It should be avoided.
> SELECT ev_class
> FROM pg_rewrite
> WHERE octet_length(ev_action) > 1
> ORDER BY ev_class;
This one is different I think, since the ev_action (the toastable_col) is
*NOT* output to sort node, so no extra memory is required IIUC.
* CP_SMALL_TLIST specifies that a narrower tlist is preferred. This is
* passed down by parent nodes such as Sort and Hash, which will have to
* store the returned tuples.
We can also verify this by
explain (costs off, verbose) SELECT ev_class
FROM pg_rewrite
WHERE octet_length(ev_action) > 1
ORDER BY ev_class;
QUERY PLAN
------------------------------------------------------------------
Sort
Output: ev_class
Sort Key: pg_rewrite.ev_class
-> Seq Scan on pg_catalog.pg_rewrite
Output: ev_class
Filter: (octet_length((pg_rewrite.ev_action)::text) > 1)
(6 rows)
Only ev_class is output to Sort node.
So if we want to make sure there is performance regression for all the
existing queries in any case, we can add 1 more restriction into the
saved-detoast-value logic. It must be (NOT under CP_SMALL_TLIST) OR (the
toastable_col is not in the output list). It can be a planner decision.
If we code like this, the result will be we need to dotoast N times
for toastable_col in qual for the below query.
SELECT toastable_col FROM t
WHERE f1(toastable_col)
AND f2(toastable_col)
..
AND fn(toastable_col)
ORDER BY any-target-entry;
However
SELECT
f1(toastable_col),
f2(toastable_col),
..
fn(toastable_col)
FROM t
ORDER BY any-target-entry;
the current path still works for it.
This one is my favorite one so far. Another option is saving the
detoast-value in some other memory or existing-slot-in-place for
different sistuation, that would requires more expr expression changes
and planner changes. I just checked all the queries in my hand, the
current design can cover all of them.
--
Best Regards
Andy Fan
