0

I want to use a result of an update query in a select query. The idea is the following: every time I query for a question, I am doing an update of when it was viewed the first time, so the update query updates the answer_viewed_date column only if it has the default value (it's non-nullable) with the current date, and does not update it on subsequent runs. I need to return whether this is the first time the question is viewed (and the date is updated) or not.

This query works, but I strongly suspect that this is not the most optimal way of doing it:

WITH upd AS (
    UPDATE questions
        SET answer_viewed_date = NOW()
        WHERE question_id = 'yQUsYLkrkgWoJGUWHnZiM'
            AND answer_viewed_date = '0001-01-01'
        RETURNING 1
)
SELECT(SELECT EXISTS(SELECT 1 FROM upd)) AS viewednow,
      question_id,
      answerer.name,
      answerer.avatar,
      answerer.id,
      question_txt,
      question_date,
      answer_date,
      answer_txt, -- showing here but not in the list to track 'read on'
      answer_viewed_date,
      q.price,
      q.currency
FROM questions q
         LEFT JOIN users answerer ON answerer.id = q.answerer_id
         LEFT JOIN upd ON TRUE
WHERE question_id = 'yQUsYLkrkgWoJGUWHnZiM';

The result of EXPLAIN ANALYZE:

Nested Loop Left Join  (cost=1.27..10.47 rows=1 width=156) (actual time=0.246..0.419 rows=1 loops=1)
  CTE upd
    ->  Update on questions  (cost=0.00..1.09 rows=1 width=14) (actual time=0.033..0.064 rows=0 loops=1)
          ->  Seq Scan on questions  (cost=0.00..1.09 rows=1 width=14) (actual time=0.016..0.023 rows=0 loops=1)
                Filter: ((question_id = 'yQUsYLkrkgWoJGUWHnZiM'::text) AND (answer_viewed_date = '0001-01-01 00:00:00+00'::timestamp with time zone))
                Rows Removed by Filter: 6
  InitPlan 3 (returns $3)
    ->  Result  (cost=0.02..0.03 rows=1 width=1) (actual time=0.024..0.055 rows=1 loops=1)
          InitPlan 2 (returns $2)
            ->  CTE Scan on upd upd_1  (cost=0.00..0.02 rows=1 width=0) (actual time=0.008..0.015 rows=0 loops=1)

Question: is there a simpler and more efficient way to do what I want to do here? Also, what indexes would you suggest to use in this case? (Postgres 14)

UPDATE

Thanks to @ypercubeᵀᴹ, the query is simplified to:

WITH upd AS (
    UPDATE questions
        SET answer_viewed_date = NOW()
        WHERE question_id = 'yQUsYLkrkgWoJGUWHnZiM'
            AND answer_viewed_date = '0001-01-01'
        RETURNING 1 AS u -- changed: added alias
)
SELECT(upd.u IS NOT NULL) AS viewednow, -- changed: simplified
      question_id,
      answerer.name,
      answerer.avatar,
      answerer.id,
      question_txt,
      question_date,
      answer_date,
      answer_txt, -- showing here but not in the list to track 'read on'
      answer_viewed_date,
      q.price,
      q.currency
FROM questions q
         LEFT JOIN users answerer ON answerer.id = q.answerer_id
         LEFT JOIN upd ON TRUE
WHERE question_id = 'yQUsYLkrkgWoJGUWHnZiM';
1
  • 2
    That (SELECT EXISTS(SELECT 1 FROM upd)) AS viewednow, could be simplified to EXISTS(SELECT 1 FROM upd) AS viewednow, or even simpler to (upd IS NOT NULL) AS viewednow since you are already joining the table. Nov 22 at 9:22

Your Answer

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

Browse other questions tagged or ask your own question.