Hi,
Hmmm, I've checked this patch and can't see performance difference on a large
(20000 key-value pairs) json, using toasted json column several times makes no
difference between current implementation on master (like queries mentioned above).
Maybe I'm doing something wrong?
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
-- Regards,
--
Nikita Malakhov
Postgres Professional
The Russian Postgres Company