ืคึผืจืึธื”ืึธืกื˜ืขืจ > ื‘ืœืึธื’ > ืึทื“ืžื™ื ื™ืกื˜ืจืึทืฆื™ืข > PostgreSQL ืึทื ื˜ื™ืคึผืึทื˜ื˜ืขืจื ืก: ืฉืขื“ืœืขืš ื“ื–ืฉื•ื™ื ืฅ ืื•ืŸ ืึธืจืก

PostgreSQL ืึทื ื˜ื™ืคึผืึทื˜ื˜ืขืจื ืก: ืฉืขื“ืœืขืš ื“ื–ืฉื•ื™ื ืฅ ืื•ืŸ ืึธืจืก

ื”ื™ื˜ ืื™ื™ืš ืคื•ืŸ ืึทืคึผืขืจื™ื™ืฉืึทื ื– ื•ื•ืึธืก ื‘ืจืขื ื’ืขืŸ ื‘ืึทืคืขืจื– ...
ื ื™ืฆืŸ ืึท ืงืœื™ื™ืŸ ืึธื ืคึฟืจืขื’ ื•ื•ื™ ืึท ื‘ื™ื™ืฉืคึผื™ืœ, ืœืึธื–ืŸ ืื•ื ื“ื– ืงื•ืง ืขื˜ืœืขื›ืข ื•ื ื™ื•ื•ืขืจืกืึทืœ ืึทืคึผืจืึธื•ื˜ืฉื™ื– ืฆื• ืึธืคึผื˜ื™ืžื™ื–ื™ื ื’ ืคึฟืจืื’ืŸ ืื™ืŸ PostgreSQL. ืฆื™ ืื™ืจ ื ื•ืฆืŸ ื–ื™ื™ ืึธื“ืขืจ ื ื™ืฉื˜ ืื™ื– ืึทืจื•ื™ืฃ ืฆื• ืื™ืจ, ืึธื‘ืขืจ ืขืก ืื™ื– ื•ื•ืขืจื˜ ืฆื• ื•ื•ื™ืกืŸ ื•ื•ืขื’ืŸ ื–ื™ื™.

ืื™ืŸ ืขื˜ืœืขื›ืข ืกืึทื‘ืกืึทืงื•ื•ืึทื ื˜ ื•ื•ืขืจืกื™ืขืก ืคื•ืŸ PG ื“ื™ ืกื™ื˜ื•ืึทืฆื™ืข ืงืขืŸ ื˜ื•ื™ืฉืŸ ื•ื•ื™ ื“ื™ ืกืงืขื“ื–ืฉื•ืœืขืจ ื•ื•ืขืจื˜ ืกืžืึทืจื˜ืขืจ, ืึธื‘ืขืจ ืคึฟืึทืจ 9.4/9.6 ืขืก ืงื•ืงื˜ ื‘ืขืขืจืขืš ื“ื™ ื–ืขืœื‘ืข ื•ื•ื™ ืื™ืŸ ื“ื™ ื‘ื™ื™ืฉืคื™ืœืŸ ื“ืึธ.

ืœืึธืžื™ืจ ื ืขืžืขืŸ ืึท ื–ื™ื™ืขืจ ืคืึทืงื˜ื™ืฉ ื‘ืงืฉื”:

SELECT
  TRUE
FROM
  "ะ”ะพะบัƒะผะตะฝั‚" d
INNER JOIN
  "ะ”ะพะบัƒะผะตะฝั‚ะ ะฐััˆะธั€ะตะฝะธะต" doc_ex
    USING("@ะ”ะพะบัƒะผะตะฝั‚")
INNER JOIN
  "ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ" t_doc ON
    t_doc."@ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ" = d."ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ"
WHERE
  (d."ะ›ะธั†ะพ3" = 19091 or d."ะกะพั‚ั€ัƒะดะฝะธะบ" = 19091) AND
  d."$ะงะตั€ะฝะพะฒะธะบ" IS NULL AND
  d."ะฃะดะฐะปะตะฝ" IS NOT TRUE AND
  doc_ex."ะกะพัั‚ะพัะฝะธะต"[1] IS TRUE AND
  t_doc."ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ" = 'ะŸะปะฐะฝะ ะฐะฑะพั‚'
LIMIT 1;

ื•ื•ืขื’ืŸ ื˜ื™ืฉ ืื•ืŸ ืคืขืœื“ ื ืขืžืขืŸื“ื™ "ืจื•ืกื™ืฉืข" ื ืขืžืขืŸ ืคื•ืŸ ืคืขืœื“ืขืจ ืื•ืŸ ื˜ื™ืฉืŸ ืงืขื ืขืŸ ื–ื™ื™ืŸ ื‘ืื”ืื ื“ืœื˜ ืึทื ื“ืขืจืฉ, ืึธื‘ืขืจ ื“ืึธืก ืื™ื– ืึท ืขื ื™ืŸ ืคื•ืŸ ื’ืขืฉืžืึทืง. ื•ื•ื™ื™ึทืœ ื“ื™ ื“ืึธ ืื™ืŸ Tensor ืขืก ื–ืขื ืขืŸ ืงื™ื™ืŸ ืคืจืขืžื“ ื“ืขื•ื•ืขืœืึธืคึผืขืจืก, ืื•ืŸ PostgreSQL ืึทืœืึทื•ื– ืื•ื ื“ื– ืฆื• ื’ืขื‘ืŸ ื ืขืžืขืŸ ืืคื™ืœื• ืื™ืŸ ื›ื™ื™ืจืึธื•ื’ืœื™ืคืก, ืื•ื™ื‘ ื–ื™ื™ ืขื ืงืœืึธื•ื–ื“ ืื™ืŸ ืงื•ื•ืึธื˜ืขืก, ื“ืขืžืึธืœื˜ ืžื™ืจ ื‘ืขืกืขืจ ืฆื• ื ืึธืžืขืŸ ืึทื‘ื“ื–ืฉืขืงืฅ ืึทื ืึทืžื‘ื™ื’ื™ื•ืึทืกืœื™ ืื•ืŸ ืงืœืืจ ืึทื–ื•ื™ ืึทื– ืขืก ื–ืขื ืขืŸ ืงื™ื™ืŸ ื“ื™ืกืงืจืขืคึผืึทื ืกื™ื–.
ื–ืืœ ืก ืงื•ืง ืื™ืŸ ื“ื™ ืจื™ื–ืึทืœื˜ื™ื ื’ ืคึผืœืึทืŸ:
PostgreSQL ืึทื ื˜ื™ืคึผืึทื˜ื˜ืขืจื ืก: ืฉืขื“ืœืขืš ื“ื–ืฉื•ื™ื ืฅ ืื•ืŸ ืึธืจืก
[ืงื•ืง ืื•ื™ืฃ explain.tensor.ru]

144ms ืื•ืŸ ื›ึผืžืขื˜ 53K ื‘ืึทืคืขืจื– - ื“ืึธืก ืื™ื–, ืžืขืจ ื•ื•ื™ 400 ืžื‘ ืคื•ืŸ ื“ืึทื˜ืŸ! ืื•ืŸ ืžื™ืจ ื•ื•ืขืœืŸ ื–ื™ื™ืŸ ืžืึทื–ืœื“ื™ืง ืื•ื™ื‘ ืึทืœืข ืคื•ืŸ โ€‹โ€‹โ€‹โ€‹ื–ื™ื™ ื–ืขื ืขืŸ ืื™ืŸ ื“ื™ ืงืึทืฉ ื“ื•ืจืš ื“ื™ ืฆื™ื™ื˜ ืคื•ืŸ ืื•ื ื“ื–ืขืจ ื‘ืงืฉื”, ืึทื ื“ืขืจืฉ ืขืก ื•ื•ืขื˜ ื ืขืžืขืŸ ืคื™ืœืข ืžืึธืœ ืžืขืจ ื•ื•ืขืŸ ืœื™ื™ืขื ืขืŸ ืคึฟื•ืŸ ื“ื™ืกืง.

ื“ื™ ืึทืœื’ืขืจื™ื“ืึทื ืื™ื– ืžืขืจืกื˜ ื•ื•ื™ื›ื˜ื™ืง!

ืื™ืŸ ืกื“ืจ ืฆื• ืขืคืขืก ืึทืคึผื˜ืึทืžื™ื™ื– ืงื™ื™ืŸ ื‘ืงืฉื”, ืื™ืจ ืžื•ื–ืŸ ืขืจืฉื˜ืขืจ ืคึฟืึทืจืฉื˜ื™ื™ืŸ ื•ื•ืึธืก ืขืก ื–ืึธืœ ื˜ืึธืŸ.
ืœืึธืžื™ืจ ืœืึธื–ืŸ ื“ื™ ืึทื ื˜ื•ื•ื™ืงืœื•ื ื’ ืคื•ืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ืกื˜ืจื•ืงื˜ื•ืจ ื–ื™ืš ืึทืจื•ื™ืก ื“ื™ ืคืึทืจื ืขื ืคื•ืŸ ื“ืขื ืึทืจื˜ื™ืงืœ ืคึฟืึทืจ ืื™ืฆื˜, ืื•ืŸ ืฉื˜ื™ืžืขืŸ ืึทื– ืžื™ืจ ืงืขื ืขืŸ ืœืขืคื™ืขืจืขืš "ื‘ื™ืœื™ืง" ืจื™ืจื™ื™ื˜ ื“ื™ ื‘ืงืฉื” ืื•ืŸ / ืึธื“ืขืจ ื–ืขืžืœ ืึทื ื˜ื• ื“ื™ ื‘ืึทื–ืข ืขื˜ืœืขื›ืข ืคื•ืŸ โ€‹โ€‹โ€‹โ€‹ื“ื™ ื˜ื™ื ื’ื– ื•ื•ืึธืก ืžื™ืจ ื“ืึทืจืคึฟืŸ ื™ื ื“ื™ืกืขืก.

ืึทื–ื•ื™ ื“ื™ ื‘ืงืฉื”:
- ื˜ืฉืขืงืก ื“ื™ ืขืงื–ื™ืกื˜ืขื ืฅ ืคื•ืŸ ื‘ื™ื™ึท ืžื™ื ื“ืกื˜ืขืจ ืขื˜ืœืขื›ืข ื“ืึธืงื•ืžืขื ื˜
- ืื™ืŸ ื“ืขื ืฆื•ืฉื˜ืึทื ื“ ื•ื•ืึธืก ืžื™ืจ ื“ืึทืจืคึฟืŸ ืื•ืŸ ืคื•ืŸ ืึท ื–ื™ื›ืขืจ ื˜ื™ืคึผ
- ื•ื•ื• ื“ืขืจ ืžื—ื‘ืจ ืึธื“ืขืจ ืคึผืขืจืคืึธืจืžืขืจ ืื™ื– ื“ืขืจ ืึธื ื’ืขืฉื˜ืขืœื˜ืขืจ ื•ื•ืึธืก ืžื™ืจ ื“ืึทืจืคึฟืŸ

JOIN + ืœื™ืžื™ื˜ 1

ื’ืึทื ืฅ ืึธืคื˜ ืขืก ืื™ื– ื’ืจื™ื ื’ืขืจ ืคึฟืึทืจ ืึท ื“ืขื•ื•ืขืœืึธืคึผืขืจ ืฆื• ืฉืจื™ื™ึทื‘ืŸ ืึท ืึธื ืคึฟืจืขื’ ื•ื•ื• ืึท ื’ืจื•ื™ืก ื ื•ืžืขืจ ืคื•ืŸ ื˜ื™ืฉืŸ ื–ืขื ืขืŸ ืขืจืฉื˜ืขืจ ืื™ื™ื ื’ืขืฉืจื™ื‘ืŸ, ืื•ืŸ ื‘ืœื•ื™ื– ืื™ื™ืŸ ืจืขืงืึธืจื“ ื‘ืœื™ื™ื‘ื˜ ืคื•ืŸ ื“ืขื ื’ืึทื ืฅ ื’ืึทื ื’. ืึธื‘ืขืจ ื’ืจื™ื ื’ืขืจ ืคึฟืึทืจ ื“ื™ ื“ืขื•ื•ืขืœืึธืคึผืขืจ ื˜ื•ื˜ ื ื™ืฉื˜ ืžื™ื™ื ืขืŸ ืžืขืจ ืขืคืขืงื˜ื™ื•ื• ืคึฟืึทืจ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก.
ืื™ืŸ ืื•ื ื“ื–ืขืจ ืคืึทืœ ืขืก ื–ืขื ืขืŸ ื’ืขื•ื•ืขืŸ ื‘ืœื•ื™ื– 3 ื˜ื™ืฉืŸ - ืื•ืŸ ื•ื•ืึธืก ืื™ื– ื“ื™ ื•ื•ื™ืจืงื•ื ื’ ...

ื–ืืœ ืก ืขืจืฉื˜ืขืจ ื‘ืึทืงื•ืžืขืŸ ื‘ืึทืคืจื™ื™ึทืขืŸ ืคื•ืŸ ื“ื™ ืงืฉืจ ืžื™ื˜ ื“ื™ "ื“ืึธืงื•ืžืขื ื˜ ื˜ื™ืคึผ" ื˜ื™ืฉ, ืื•ืŸ ืื™ืŸ ื“ืขืจ ื–ืขืœื‘ื™ืงืขืจ ืฆื™ื™ื˜ ื–ืึธื’ืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ืึทื– ืื•ื ื“ื–ืขืจ ื˜ื™ืคึผ ืจืขืงืึธืจื“ ืื™ื– ื™ื™ื ืฆื™ืง (ืžื™ืจ ื•ื•ื™ืกืŸ ื“ืขื, ืึธื‘ืขืจ ื“ื™ ืกืงืขื“ื–ืฉื•ืœืขืจ ื”ืื˜ ืงื™ื™ืŸ ื’ืขื“ืึทื ืง ื ืึธืš):

WITH T AS (
  SELECT
    "@ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ"
  FROM
    "ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ"
  WHERE
    "ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ" = 'ะŸะปะฐะฝะ ะฐะฑะพั‚'
  LIMIT 1
)
...
WHERE
  d."ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ" = (TABLE T)
...

ื™ืึธ, ืื•ื™ื‘ ื“ื™ ื˜ื™ืฉ / CTE ื‘ืืฉื˜ื™ื™ื˜ ืคื•ืŸ ืึท ืื™ื™ืŸ ืคืขืœื“ ืคื•ืŸ ืึท ืื™ื™ืŸ ืจืขืงืึธืจื“, ืื™ืŸ PG ืื™ืจ ืงืขื ืขืŸ ืืคื™ืœื• ืฉืจื™ื™ึทื‘ืŸ ื•ื•ื™ ื“ืึธืก, ืึทื ืฉื˜ืึธื˜ ืคื•ืŸ

d."ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ" = (SELECT "@ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ" FROM T LIMIT 1)

ืคื•ื™ืœ ืืคืฉืืฆื•ื ื’ ืื™ืŸ PostgreSQL ืคึฟืจืื’ืŸ

ื‘ื™ื˜ืžืึทืคึผืึธืจ ื•ื•ืก ื™ื•ื ื™ืึทืŸ

ืื™ืŸ ืขื˜ืœืขื›ืข ืงืึทืกืขืก, ื‘ื™ื˜ืžืึทืคึผ ื”ืขืึทืคึผ ืกืงืึทืŸ ื•ื•ืขื˜ ืงืึธืกื˜ืŸ ืื•ื ื“ื– ืึท ืคึผืœืึทืฅ - ืคึฟืึทืจ ื‘ื™ื™ึทืฉืคึผื™ืœ, ืื™ืŸ ืื•ื ื“ื–ืขืจ ืกื™ื˜ื•ืึทืฆื™ืข, ื•ื•ืขืŸ ื’ืึทื ืฅ ืึท ืคึผืœืึทืฅ ืคื•ืŸ ืจืขืงืึธืจื“ืก ื˜ืจืขืคืŸ ื“ื™ ืคืืจืœืื ื’ื˜ ืฆื•ืฉื˜ืึทื ื“. ืžื™ืจ ื”ืึธื‘ืŸ ืขืก ื•ื•ื™ื™ึทืœ ืึธื“ืขืจ ืฆื•ืฉื˜ืึทื ื“ ืคืืจืงืขืจื˜ ืื™ืŸ ื‘ื™ื˜ืžืึทืคึผืึธืจ- ืึธืคึผืขืจืึทืฆื™ืข ืื™ืŸ ืคึผืœืึทืŸ.
ื–ืืœ ืก ืฆื•ืจื™ืงืงื•ืžืขืŸ ืฆื• ื“ืขืจ ืึธืจื™ื’ื™ื ืขืœ ืคึผืจืึธื‘ืœืขื - ืžื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ื’ืขืคึฟื™ื ืขืŸ ืึท ืจืขืงืึธืจื“ ืงืึธืจืึทืกืคึผืึทื ื“ื™ื ื’ ื•ื•ืขืจ ืขืก ื™ื– ืคึฟื•ืŸ ื“ื™ ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ - ื“ืึธืก ืื™ื–, ืขืก ืื™ื– ื ื™ื˜ ื“ืึทืจืคึฟืŸ ืฆื• ื–ื•ื›ืŸ ืคึฟืึทืจ ืึทืœืข 59 ืง ืจืขืงืึธืจื“ืก ืื•ื ื˜ืขืจ ื‘ื™ื™ื“ืข ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ. ืขืก ืื™ื– ืึท ื•ื•ืขื’ ืฆื• ืึทืจื‘ืขื˜ืŸ ืื•ื™ืก ืื™ื™ืŸ ืฆื•ืฉื˜ืึทื ื“, ืื•ืŸ ื’ื™ื™ืŸ ืฆื• ื“ื™ ืจื’ืข ื ืึธืจ ื•ื•ืขืŸ ื’ืึธืจื ื™ืฉื˜ ืื™ื– ื’ืขืคื•ื ืขืŸ ืื™ืŸ ื“ืขืจ ืขืจืฉื˜ืขืจ. ื“ื™ ืคืืœื’ืขื ื“ืข ืคึผืœืึทืŸ ื•ื•ืขื˜ ื”ืขืœืคึฟืŸ ืื•ื ื“ื–:

(
  SELECT
    ...
  LIMIT 1
)
UNION ALL
(
  SELECT
    ...
  LIMIT 1
)
LIMIT 1

"ืคื•ื ื“ืจื•ื™ืกื ื“ื™ืง" ืœื™ืžื™ื˜ 1 ื™ื ืฉื•ืจื– ืึทื– ื“ื™ ื–ื•ื›ืŸ ืขื ื“ืก ื•ื•ืขืŸ ื“ืขืจ ืขืจืฉื˜ืขืจ ืจืขืงืึธืจื“ ืื™ื– ื’ืขืคึฟื•ื ืขืŸ. ืื•ืŸ ืื•ื™ื‘ ืขืก ืื™ื– ืฉื•ื™ืŸ ื’ืขืคึฟื•ื ืขืŸ ืื™ืŸ ื“ืขืจ ืขืจืฉื˜ืขืจ ื‘ืœืึธืง, ื“ื™ ืจื’ืข ื‘ืœืึธืง ื•ื•ืขื˜ ื ื™ืฉื˜ ื–ื™ื™ืŸ ืขืงืกืึทืงื™ื•ื˜ืึทื“ (ืงื™ื™ื ืžืึธืœ ืขืงืกืึทืงื™ื•ื˜ืึทื“ ืื™ืŸ ืจืขืกืคึผืขืงื˜ ืคื•ืŸ).

"ื‘ืึทื”ืึทืœื˜ืŸ ืฉื•ื•ืขืจ ื˜ื ืึธื™ื ืื•ื ื˜ืขืจ CASE"

ืขืก ืื™ื– ืึท ื’ืึธืจ ื•ืžื‘ืึทืงื•ื•ืขื ืžืึธืžืขื ื˜ ืื™ืŸ ื“ืขืจ ืึธืจื™ื’ื™ื ืขืœ ืึธื ืคึฟืจืขื’ - ืงืึธื ื˜ืจืึธืœื™ืจืŸ ื“ื™ ืกื˜ืึทื˜ื•ืก ืงืขื’ืŸ ื“ื™ ืคึฟืึทืจื‘ื•ื ื“ืขื ืข ื˜ื™ืฉ "DocumentExtension". ืจืขื’ืึทืจื“ืœืขืกืก ืคื•ืŸ ื“ื™ ืืžืช ืคื•ืŸ ืื ื“ืขืจืข ื‘ืื“ื™ื ื’ื•ื ื’ืขืŸ ืื™ืŸ ื“ืขืจ ืื•ื™ืกื“ืจื•ืง (ืœืžืฉืœ, ื“. "ืื•ื™ืกื’ืขืžืขืงื˜" ืื™ื– ื ื™ืฉื˜ ืืžืช), ื“ืขืจ ืงืฉืจ ืื™ื– ืฉื˜ืขื ื“ื™ืง ืขืงืกืึทืงื™ื•ื˜ืึทื“ ืื•ืŸ "ืงืึธืก ืจืขืกื•ืจืกืŸ". ืžืขืจ ืึธื“ืขืจ ื•ื•ื™ื™ื ื™ืงืขืจ ืคื•ืŸ ื–ื™ื™ ื•ื•ืขื˜ ื–ื™ื™ืŸ ืคืืจื‘ืจืื›ื˜ - ื“ืขืคึผืขื ื“ืก ืื•ื™ืฃ ื“ื™ ื’ืจื™ื™ืก ืคื•ืŸ ื“ืขื ื˜ื™ืฉ.
ืึธื‘ืขืจ ืื™ืจ ืงืขื ืขืŸ ืžืึธื“ื™ืคื™ืฆื™ืจืŸ ื“ื™ ืึธื ืคึฟืจืขื’ ืึทื–ื•ื™ ืึทื– ื“ื™ ื–ื•ื›ืŸ ืคึฟืึทืจ ืึท ืคึฟืึทืจื‘ื•ื ื“ืขื ืข ืจืขืงืึธืจื“ ืึทืงืขืจื– ื‘ืœื•ื™ื– ื•ื•ืขืŸ ืขืก ืื™ื– ื˜ืึทืงืข ื ื™ื™ื˜ื™ืง:

SELECT
  ...
FROM
  "ะ”ะพะบัƒะผะตะฝั‚" d
WHERE
  ... /*index cond*/ AND
  CASE
    WHEN "$ะงะตั€ะฝะพะฒะธะบ" IS NULL AND "ะฃะดะฐะปะตะฝ" IS NOT TRUE THEN (
      SELECT
        "ะกะพัั‚ะพัะฝะธะต"[1] IS TRUE
      FROM
        "ะ”ะพะบัƒะผะตะฝั‚ะ ะฐััˆะธั€ะตะฝะธะต"
      WHERE
        "@ะ”ะพะบัƒะผะตะฝั‚" = d."@ะ”ะพะบัƒะผะตะฝั‚"
    )
  END

ืึทืžืึธืœ ืคื•ืŸ ื“ื™ ืœื™ื ื’ืงื˜ ื˜ื™ืฉ ืฆื• ืื•ื ื“ื– ืงื™ื™ื ืขืจ ืคื•ืŸ ื“ื™ ืคืขืœื“ืขืจ ืื™ื– ื ื™ื˜ ื“ืืจืฃ ืคึฟืึทืจ ื“ืขืจ ืจืขื–ื•ืœื˜ืึทื˜, ื“ืขืžืึธืœื˜ ืžื™ืจ ื”ืึธื‘ืŸ ื“ื™ ื’ืขืœืขื’ื ื”ื™ื™ื˜ ืฆื• ื•ื•ืขื ื“ืŸ JOIN ืื™ืŸ ืึท ืฆื•ืฉื˜ืึทื ื“ ืื•ื™ืฃ ืึท ืกืึทื‘ืงื•ื•ืขืจื™.
ืœืึธืžื™ืจ ืœืึธื–ืŸ ื“ื™ ื™ื ื“ืขืงืกื˜ ืคืขืœื“ืขืจ "ืึทืจื•ื™ืก ื“ื™ ืงืึทืกืข ื‘ืจืึทืงืึทืฅ", ืœื™ื™ื’ืŸ ืคึผืฉื•ื˜ ื˜ื ืึธื™ื ืคื•ืŸ ื“ื™ ืจืขืงืึธืจื“ ืฆื• ื“ื™ ื•ื•ืขืŸ ื‘ืœืึธืง - ืื•ืŸ ืื™ืฆื˜ ื“ื™ "ืฉื•ื•ืขืจ" ืึธื ืคึฟืจืขื’ ืื™ื– ืขืงืกืึทืงื™ื•ื˜ืึทื“ ื‘ืœื•ื™ื– ื•ื•ืขืŸ ื’ื™ื™ื˜ ืคืืจื‘ื™ื™ ืฆื• THEN.

ืžื™ื™ืŸ ืœืขืฆื˜ืข ื ืึธืžืขืŸ ืื™ื– "ื’ืึทื ืฅ"

ืžื™ืจ ืงืœื™ื™ึทื‘ืŸ ื“ื™ ืจื™ื–ืึทืœื˜ื™ื ื’ ืึธื ืคึฟืจืขื’ ืžื™ื˜ ืึทืœืข ื“ื™ ืžืึทืงืึทื ื™ืงืก ื“ื™ืกืงืจื™ื™ื‘ื“ ืื•ื™ื‘ืŸ:

WITH T AS (
  SELECT
    "@ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ"
  FROM
    "ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ"
  WHERE
    "ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ" = 'ะŸะปะฐะฝะ ะฐะฑะพั‚'
)
  (
    SELECT
      TRUE
    FROM
      "ะ”ะพะบัƒะผะตะฝั‚" d
    WHERE
      ("ะ›ะธั†ะพ3", "ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ") = (19091, (TABLE T)) AND
      CASE
        WHEN "$ะงะตั€ะฝะพะฒะธะบ" IS NULL AND "ะฃะดะฐะปะตะฝ" IS NOT TRUE THEN (
          SELECT
            "ะกะพัั‚ะพัะฝะธะต"[1] IS TRUE
          FROM
            "ะ”ะพะบัƒะผะตะฝั‚ะ ะฐััˆะธั€ะตะฝะธะต"
          WHERE
            "@ะ”ะพะบัƒะผะตะฝั‚" = d."@ะ”ะพะบัƒะผะตะฝั‚"
        )
      END
    LIMIT 1
  )
UNION ALL
  (
    SELECT
      TRUE
    FROM
      "ะ”ะพะบัƒะผะตะฝั‚" d
    WHERE
      ("ะขะธะฟะ”ะพะบัƒะผะตะฝั‚ะฐ", "ะกะพั‚ั€ัƒะดะฝะธะบ") = ((TABLE T), 19091) AND
      CASE
        WHEN "$ะงะตั€ะฝะพะฒะธะบ" IS NULL AND "ะฃะดะฐะปะตะฝ" IS NOT TRUE THEN (
          SELECT
            "ะกะพัั‚ะพัะฝะธะต"[1] IS TRUE
          FROM
            "ะ”ะพะบัƒะผะตะฝั‚ะ ะฐััˆะธั€ะตะฝะธะต"
          WHERE
            "@ะ”ะพะบัƒะผะตะฝั‚" = d."@ะ”ะพะบัƒะผะตะฝั‚"
        )
      END
    LIMIT 1
  )
LIMIT 1;

ืึทื“ื–ืฉืึทืกื˜ื™ื ื’ [ืฆื•] ื™ื ื“ืขืงืกื™ื–

ื ื˜ืจื™ื™ื ื“ ืื•ื™ื’ ื‘ืืžืขืจืงื˜ ืึทื– ื“ื™ ื™ื ื“ืขืงืกื˜ ื˜ื ืึธื™ื ืื™ืŸ ื“ื™ UNION ืกื•ื‘ื‘ืœืึธืงืงืก ื–ืขื ืขืŸ ืึท ื‘ื™ืกืœ ืึทื ื“ืขืจืฉ - ื“ืึธืก ืื™ื– ื•ื•ื™ื™ึทืœ ืžื™ืจ ื”ืึธื‘ืŸ ืฉื•ื™ืŸ ืคึผืึทืกื™ืง ื™ื ื“ืขืงืกื™ื– ืื•ื™ืฃ ื“ื™ ื˜ื™ืฉ. ืื•ืŸ ืื•ื™ื‘ ื–ื™ื™ ื–ืขื ืขืŸ ื ื™ืฉื˜ ืขืงืกื™ืกื˜ื™ืจื˜, ืขืก ื•ื•ืึธืœื˜ ื–ื™ื™ืŸ ื•ื•ืขืจื˜ ืฆื• ืฉืึทืคึฟืŸ: ื“ืึธืงื•ืžืขื ื˜ (ืคึผืขืจืกืึธืŸ 3, ื“ืึธืงื•ืžืขื ื˜ ื˜ื™ืคึผ) ะธ ื“ืึธืงื•ืžืขื ื˜ (ื“ืึธืงื•ืžืขื ื˜ื˜ื™ืคึผืข, ืึธื ื’ืขืฉื˜ืขืœื˜ืขืจ).
ื•ื•ืขื’ืŸ ื“ื™ ืกื“ืจ ืคื•ืŸ ืคืขืœื“ืขืจ ืื™ืŸ ROW ื˜ื ืึธื™ืืคื•ืŸ ื“ื™ ืคึผืœืึทื ื ืขืจ ืก ืคื•ื ื˜ ืคื•ืŸ ืžื™ื™ื ื•ื ื’, ืคื•ืŸ ืงื•ืจืก, ืื™ืจ ืงืขื ืขืŸ ืฉืจื™ื™ึทื‘ืŸ (ื, ื‘) = (ืงืึธื ืกื˜ืึท, ืงืึธื ืกื˜ื‘)ืื•ืŸ (ื‘, ื) = (ืงืึธื ืกื˜ื‘, ืงืึธื ืกื˜ืึท). ืื‘ืขืจ ื•ื•ืขืŸ ืจืขืงืึธืจื“ื™ื ื’ ืื™ืŸ ื“ื™ ืกื“ืจ ืคื•ืŸ ื“ื™ ืคืขืœื“ืขืจ ืื™ืŸ ื“ื™ ืื™ื ื“ืขืงืก, ืึทื–ืึท ืึท ื‘ืงืฉื” ืื™ื– ืคืฉื•ื˜ ืžืขืจ ื‘ืึทืงื•ื•ืขื ืฆื• ื“ื™ื‘ื•ื’ ืฉืคึผืขื˜ืขืจ.
ื•ื•ืึธืก ืื™ื– ืื™ืŸ ื“ื™ ืคึผืœืึทืŸ?
PostgreSQL ืึทื ื˜ื™ืคึผืึทื˜ื˜ืขืจื ืก: ืฉืขื“ืœืขืš ื“ื–ืฉื•ื™ื ืฅ ืื•ืŸ ืึธืจืก
[ืงื•ืง ืื•ื™ืฃ explain.tensor.ru]

ืฆื•ื ื‘ืึทื“ื•ื™ืขืจืŸ, ืžื™ืจ ื”ืึธื‘ืŸ ืฉืœื™ืžืึทื–ืœื“ื™ืง ืื•ืŸ ื’ืึธืจื ื™ืฉื˜ ืื™ื– ื’ืขืคื•ื ืขืŸ ืื™ืŸ ื“ืขืจ ืขืจืฉื˜ืขืจ ื™ื•ื ื™ืึทืŸ ื‘ืœืึธืง, ืึทื–ื•ื™ ื“ืขืจ ืฆื•ื•ื™ื™ื˜ืขืจ ืื™ื– ื ืึธืš ืขืงืกืึทืงื™ื•ื˜ืึทื“. ืื‘ืขืจ ืืคื™ืœื• ืึทื–ื•ื™ - ื‘ืœื•ื™ื– 0.037ms ืื•ืŸ 11 ื‘ืึทืคืขืจื–!
ืžื™ืจ ื”ืึธื‘ืŸ ืกืคึผื™ื“ืึทื“ ื“ื™ ื‘ืขื˜ืŸ ืื•ืŸ ืจื™ื“ื•ืกื˜ ื“ืึทื˜ืŸ ืคึผืึทืžืคึผื™ื ื’ ืื™ืŸ ื–ื›ึผืจื•ืŸ ืขื˜ืœืขื›ืข ื˜ื•ื™ื–ื ื˜ ืžืืœ, ื ื™ืฆืŸ ืคืขืจืœื™ ืคึผืฉื•ื˜ ื˜ืขืงื ื™ืงืก - ืึท ื’ื•ื˜ ืจืขื–ื•ืœื˜ืึทื˜ ืžื™ื˜ ืึท ื‘ื™ืกืœ ืงืึธืคึผื™ืข-ืคึผืึทืคึผ. ๐Ÿ™‚

ืžืงื•ืจ: www.habr.com

ืœื™ื™ื’ืŸ ืึท ื‘ืึทืžืขืจืงื•ื ื’