WITH optimization?

Question

PostgreSQL

Hello. Made the WITH construction which adds records to the main and subordinate table. But the question is how in terms of performance, can you advise to simplify something? Especially confuses me, the subquery

(( SELECT id FROM exchange ), 'ETHBTC'),
(( SELECT id FROM exchange ), 'LTCBTC'),
(( SELECT id FROM exchange ), 'BNBBTC')

Thank you.

WITH
    -- Получаем обменник
    exchange AS (
      SELECT id, name FROM exchanges
      WHERE name = 'coins'
      LIMIT 1
    ),
    -- Добавляем пары
    pairs_new AS (
      INSERT INTO pairs (
        exchange_id,
        symbol
      )
        VALUES
          (( SELECT id FROM exchange ), 'ETHBTC'),
          (( SELECT id FROM exchange ), 'LTCBTC'),
          (( SELECT id FROM exchange ), 'BNBBTC')
        ON CONFLICT ( exchange_id, symbol )
          DO UPDATE SET symbol = EXCLUDED.symbol
        RETURNING id, exchange_id, symbol
    ),
    -- Добавляем цены
    prices_new AS (
      INSERT INTO prices (
        pair_id,
        price,
        close_time
      )
        VALUES
          (( SELECT id FROM pairs_new WHERE symbol='ETHBTC' ), 0.05441800,'1970-01-01 02:00:00'),
          (( SELECT id FROM pairs_new WHERE symbol='LTCBTC' ), 0.01597600,'1970-01-01 02:00:00'),
          (( SELECT id FROM pairs_new WHERE symbol='BNBBTC' ), 0.00145470,'1970-01-01 02:00:00')
        ON CONFLICT ( pair_id, close_time )
          DO UPDATE SET price = EXCLUDED.price
        RETURNING id, pair_id, price
    )
  -- Результирующий запрос
  SELECT aa.id AS exchange_id,
         aa.name AS exchange_name,
         bb.id AS pairs_id,
         bb.symbol,
         cc.id AS price_id,
         cc.price
    FROM exchange aa
    LEFT JOIN pairs_new bb ON aa.id = bb.exchange_id
    LEFT JOIN prices_new cc ON bb.id = cc.pair_id

Answer 1

Instead of

(( SELECT id FROM exchange ), 'ETHBTC'),
(( SELECT id FROM exchange ), 'LTCBTC'),
(( SELECT id FROM exchange ), 'BNBBTC')

you can use the unnest function

INSERT INTO pairs (
  exchange_id,
  symbol
)
SELECT exchange.id, unnest(ARRAY['ETHBTC', 'ETHBTC', 'BNBBTC'])
FROM exchange  
ON CONFLICT ( exchange_id, symbol )
  DO UPDATE SET symbol = EXCLUDED.symbol
RETURNING id, exchange_id, symbol