3

I am just starting to use some of the jsonb functionality in Postgres 9.4 (would love to move to 9.6) . I have the following query:

SELECT
    queryable_type, 
    jsonb_array_length(as_data->'users_who_like') as js_users_count 
FROM 
    queryables 
WHERE 
    queryable_type = 'Item' and jt_users_count > 1;

but I am unable to access jt_users_count in the where phrase but can like this:

SELECT
    queryable_type, 
    jsonb_array_length(as_data->'users_who_like') as js_users_count 
FROM 
    queryables 
WHERE
    queryable_type = 'Item' and jsonb_array_length(as_data->'users_who_like') > 1 
ORDER BY
    jsonb_array_length(as_data->'users_who_like') desc;

To be honest, this just seems a bit unusual and possibly wasteful. Is there a way for Postgres to only compute this once and use a reference to jt_users_count?

3

This is the standard behaviour of SQL. SQL statements are (logically) processed in a certain specified order.

Following that order, the WHERE clause is executed before the SELECT one. That's why the WHERE clause cannot access the value of js_users_count: It has not yet been computed.

That's why your second query works, but your first one doesn't.

On the other hand, it is possible to have js_users_count on the ORDER BY, because that clause is processed after the SELECT:

SELECT
    queryable_type, 
    jsonb_array_length(as_data->'users_who_like') as js_users_count 
FROM 
    queryables 
WHERE
    queryable_type = 'Item' and jsonb_array_length(as_data->'users_who_like') > 1 
ORDER BY
    js_users_count desc;

Don't be afraid about a certain computation being done twice. It can be either optimized (as some kind of common subexpression elimination optimization) or its impact is completely neglectable in the overall performance of a query.

If you want to really make sure that an expression is not evaluated twice, you can use one (often annoying) feature of PostgreSQL CTE: the fact that they're optimization fences (i.e.: they won't be inlined, or converted to subqueries in the final statement):

WITH q1 AS
(
    SELECT
        queryable_type, 
        jsonb_array_length(as_data->'users_who_like') as js_users_count 
    FROM 
        queryables 
    WHERE
        queryable_type = 'Item'
)
SELECT
     *
FROM
    q1 
WHERE
    js_users_count > 1 
ORDER BY
    js_users_count desc;

This will create a (virtual temporary) table q1 with the already computed values of jsonb_array_length. I would only do this kind of thing if the function used takes a very long time to execute (has a very high cost), and there's evidence that it's actually called twice (you can check with some RAISE DEBUG statements in the function itself, if it's been written by you).

References:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.