PostgreSQL ਐਂਟੀਪੈਟਰਨਜ਼: ਨਾਮ ਦੁਆਰਾ ਖੋਜ ਦੇ ਦੁਹਰਾਏ ਸੁਧਾਰ ਦੀ ਕਹਾਣੀ, ਜਾਂ "ਅੱਗੇ ਅਤੇ ਅੱਗੇ ਅਨੁਕੂਲਤਾ"

ਦੇਸ਼ ਭਰ ਦੇ ਸੇਲਜ਼ ਆਫਿਸਾਂ ਦੇ ਹਜ਼ਾਰਾਂ ਮੈਨੇਜਰ ਰਿਕਾਰਡ ਕਰਦੇ ਹਨ ਸਾਡਾ CRM ਸਿਸਟਮ ਰੋਜ਼ਾਨਾ ਹਜ਼ਾਰਾਂ ਸੰਪਰਕ - ਸੰਭਾਵੀ ਜਾਂ ਮੌਜੂਦਾ ਗਾਹਕਾਂ ਨਾਲ ਸੰਚਾਰ ਦੇ ਤੱਥ। ਅਤੇ ਇਸਦੇ ਲਈ, ਤੁਹਾਨੂੰ ਪਹਿਲਾਂ ਇੱਕ ਕਲਾਇੰਟ ਲੱਭਣਾ ਚਾਹੀਦਾ ਹੈ, ਅਤੇ ਤਰਜੀਹੀ ਤੌਰ 'ਤੇ ਬਹੁਤ ਜਲਦੀ. ਅਤੇ ਇਹ ਨਾਮ ਦੁਆਰਾ ਅਕਸਰ ਵਾਪਰਦਾ ਹੈ.

ਇਸ ਲਈ, ਇਹ ਹੈਰਾਨੀ ਦੀ ਗੱਲ ਨਹੀਂ ਹੈ ਕਿ, ਇੱਕ ਵਾਰ ਫਿਰ ਸਭ ਤੋਂ ਵੱਧ ਲੋਡ ਕੀਤੇ ਡੇਟਾਬੇਸ ਵਿੱਚੋਂ ਇੱਕ 'ਤੇ "ਭਾਰੀ" ਸਵਾਲਾਂ ਦਾ ਵਿਸ਼ਲੇਸ਼ਣ ਕਰਨਾ - ਸਾਡੇ ਆਪਣੇ VLSI ਕਾਰਪੋਰੇਟ ਖਾਤਾ, ਮੈਨੂੰ "ਸਿਖਰ ਵਿੱਚ" ਮਿਲਿਆ ਨਾਮ ਦੁਆਰਾ "ਤੁਰੰਤ" ਖੋਜ ਲਈ ਬੇਨਤੀ ਸੰਗਠਨ ਕਾਰਡ ਲਈ.

ਇਸ ਤੋਂ ਇਲਾਵਾ, ਅਗਲੇਰੀ ਜਾਂਚ ਤੋਂ ਇਕ ਦਿਲਚਸਪ ਉਦਾਹਰਣ ਸਾਹਮਣੇ ਆਈ ਹੈ ਪਹਿਲਾਂ ਓਪਟੀਮਾਈਜੇਸ਼ਨ ਅਤੇ ਫਿਰ ਪ੍ਰਦਰਸ਼ਨ ਵਿੱਚ ਗਿਰਾਵਟ ਕਈ ਟੀਮਾਂ ਦੁਆਰਾ ਇਸਦੇ ਕ੍ਰਮਵਾਰ ਸੁਧਾਈ ਦੇ ਨਾਲ ਬੇਨਤੀ, ਜਿਨ੍ਹਾਂ ਵਿੱਚੋਂ ਹਰ ਇੱਕ ਨੇ ਪੂਰੀ ਤਰ੍ਹਾਂ ਵਧੀਆ ਇਰਾਦਿਆਂ ਨਾਲ ਕੰਮ ਕੀਤਾ।

0: ਉਪਭੋਗਤਾ ਕੀ ਚਾਹੁੰਦਾ ਸੀ?

[KDPV ਇੱਥੋਂ]

ਇੱਕ ਉਪਭੋਗਤਾ ਦਾ ਆਮ ਤੌਰ 'ਤੇ ਕੀ ਮਤਲਬ ਹੁੰਦਾ ਹੈ ਜਦੋਂ ਉਹ ਨਾਮ ਦੁਆਰਾ "ਤੁਰੰਤ" ਖੋਜ ਬਾਰੇ ਗੱਲ ਕਰਦੇ ਹਨ? ਇਹ ਲਗਭਗ ਕਦੇ ਵੀ ਸਬਸਟ੍ਰਿੰਗ ਵਰਗੀ "ਇਮਾਨਦਾਰ" ਖੋਜ ਨਹੀਂ ਨਿਕਲਦਾ ... LIKE '%роза%' - ਕਿਉਂਕਿ ਫਿਰ ਨਤੀਜੇ ਵਿੱਚ ਨਾ ਸਿਰਫ ਸ਼ਾਮਲ ਹੁੰਦਾ ਹੈ 'Розалия' и 'Магазин Роза'ਪਰ 'Гроза' ਅਤੇ ਵੀ 'Дом Деда Мороза'.

ਉਪਭੋਗਤਾ ਰੋਜ਼ਾਨਾ ਪੱਧਰ 'ਤੇ ਮੰਨਦਾ ਹੈ ਕਿ ਤੁਸੀਂ ਉਸਨੂੰ ਪ੍ਰਦਾਨ ਕਰੋਗੇ ਸ਼ਬਦ ਦੀ ਸ਼ੁਰੂਆਤ ਦੁਆਰਾ ਖੋਜ ਕਰੋ ਸਿਰਲੇਖ ਵਿੱਚ ਅਤੇ ਇਸ ਨੂੰ ਹੋਰ ਢੁਕਵਾਂ ਬਣਾਓ 'ਤੇ ਸ਼ੁਰੂ ਹੁੰਦਾ ਹੈ ਦਾਖਲ ਕੀਤਾ। ਅਤੇ ਤੁਸੀਂ ਇਹ ਕਰੋਗੇ ਲਗਭਗ ਤੁਰੰਤ - ਇੰਟਰਲਾਈਨਰ ਇੰਪੁੱਟ ਲਈ।

1: ਕੰਮ ਨੂੰ ਸੀਮਤ ਕਰੋ

ਅਤੇ ਇਸ ਤੋਂ ਵੀ ਵੱਧ, ਇੱਕ ਵਿਅਕਤੀ ਵਿਸ਼ੇਸ਼ ਤੌਰ 'ਤੇ ਦਾਖਲ ਨਹੀਂ ਹੋਵੇਗਾ 'роз магаз', ਤਾਂ ਜੋ ਤੁਹਾਨੂੰ ਅਗੇਤਰ ਦੁਆਰਾ ਹਰੇਕ ਸ਼ਬਦ ਦੀ ਖੋਜ ਕਰਨੀ ਪਵੇ। ਨਹੀਂ, ਕਿਸੇ ਉਪਭੋਗਤਾ ਲਈ ਪਿਛਲੇ ਸ਼ਬਦਾਂ ਨੂੰ ਜਾਣਬੁੱਝ ਕੇ "ਅੰਡਰ-ਨਿਰਧਾਰਤ" ਕਰਨ ਨਾਲੋਂ ਆਖਰੀ ਸ਼ਬਦ ਲਈ ਇੱਕ ਤੇਜ਼ ਸੰਕੇਤ ਦਾ ਜਵਾਬ ਦੇਣਾ ਬਹੁਤ ਸੌਖਾ ਹੈ - ਦੇਖੋ ਕਿ ਕੋਈ ਵੀ ਖੋਜ ਇੰਜਣ ਇਸ ਨੂੰ ਕਿਵੇਂ ਸੰਭਾਲਦਾ ਹੈ।

ਜਨਰਲ ਸੱਜੇ ਸਮੱਸਿਆ ਲਈ ਲੋੜਾਂ ਨੂੰ ਤਿਆਰ ਕਰਨਾ ਅੱਧੇ ਤੋਂ ਵੱਧ ਹੱਲ ਹੈ। ਕਈ ਵਾਰ ਧਿਆਨ ਨਾਲ ਕੇਸ ਵਿਸ਼ਲੇਸ਼ਣ ਦੀ ਵਰਤੋਂ ਕਰੋ ਨਤੀਜੇ ਨੂੰ ਕਾਫ਼ੀ ਪ੍ਰਭਾਵਿਤ ਕਰ ਸਕਦਾ ਹੈ.

ਇੱਕ ਐਬਸਟਰੈਕਟ ਡਿਵੈਲਪਰ ਕੀ ਕਰਦਾ ਹੈ?

1.0: ਬਾਹਰੀ ਖੋਜ ਇੰਜਣ

ਓਹ, ਖੋਜ ਕਰਨਾ ਔਖਾ ਹੈ, ਮੈਂ ਬਿਲਕੁਲ ਵੀ ਕੁਝ ਨਹੀਂ ਕਰਨਾ ਚਾਹੁੰਦਾ - ਚਲੋ ਇਸਨੂੰ ਡੇਵੋਪਸ ਨੂੰ ਦੇਈਏ! ਉਹਨਾਂ ਨੂੰ ਡੇਟਾਬੇਸ ਦੇ ਬਾਹਰੀ ਖੋਜ ਇੰਜਣ ਨੂੰ ਤੈਨਾਤ ਕਰਨ ਦਿਓ: Sphinx, ElasticSearch,...

ਇੱਕ ਕੰਮ ਕਰਨ ਦਾ ਵਿਕਲਪ, ਭਾਵੇਂ ਕਿ ਸਮਕਾਲੀਕਰਨ ਅਤੇ ਤਬਦੀਲੀਆਂ ਦੀ ਗਤੀ ਦੇ ਰੂਪ ਵਿੱਚ ਕਿਰਤ-ਸੰਬੰਧੀ ਹੈ। ਪਰ ਸਾਡੇ ਕੇਸ ਵਿੱਚ ਨਹੀਂ, ਕਿਉਂਕਿ ਖੋਜ ਹਰੇਕ ਗਾਹਕ ਲਈ ਸਿਰਫ਼ ਉਸਦੇ ਖਾਤੇ ਦੇ ਡੇਟਾ ਦੇ ਢਾਂਚੇ ਦੇ ਅੰਦਰ ਕੀਤੀ ਜਾਂਦੀ ਹੈ. ਅਤੇ ਡੇਟਾ ਵਿੱਚ ਕਾਫ਼ੀ ਉੱਚ ਪਰਿਵਰਤਨਸ਼ੀਲਤਾ ਹੈ - ਅਤੇ ਜੇਕਰ ਮੈਨੇਜਰ ਨੇ ਹੁਣ ਕਾਰਡ ਵਿੱਚ ਦਾਖਲ ਕੀਤਾ ਹੈ 'Магазин Роза', ਫਿਰ 5-10 ਸਕਿੰਟਾਂ ਬਾਅਦ ਉਸਨੂੰ ਪਹਿਲਾਂ ਹੀ ਯਾਦ ਹੋ ਸਕਦਾ ਹੈ ਕਿ ਉਹ ਉੱਥੇ ਆਪਣੀ ਈਮੇਲ ਦਰਸਾਉਣਾ ਭੁੱਲ ਗਿਆ ਸੀ ਅਤੇ ਇਸਨੂੰ ਲੱਭਣਾ ਅਤੇ ਇਸਨੂੰ ਠੀਕ ਕਰਨਾ ਚਾਹੁੰਦਾ ਹੈ।

ਇਸ ਲਈ - ਆਓ "ਡਾਟਾਬੇਸ ਵਿੱਚ ਸਿੱਧਾ" ਖੋਜੋ. ਖੁਸ਼ਕਿਸਮਤੀ ਨਾਲ, PostgreSQL ਸਾਨੂੰ ਅਜਿਹਾ ਕਰਨ ਦੀ ਇਜਾਜ਼ਤ ਦਿੰਦਾ ਹੈ, ਨਾ ਕਿ ਸਿਰਫ਼ ਇੱਕ ਵਿਕਲਪ - ਅਸੀਂ ਉਹਨਾਂ ਨੂੰ ਦੇਖਾਂਗੇ.

1.1: "ਇਮਾਨਦਾਰ" ਸਬਸਟਰਿੰਗ

ਅਸੀਂ "ਸਬਸਟ੍ਰਿੰਗ" ਸ਼ਬਦ ਨਾਲ ਚਿੰਬੜੇ ਹਾਂ। ਪਰ ਸਬਸਟਰਿੰਗ ਦੁਆਰਾ ਸੂਚਕਾਂਕ ਖੋਜ ਲਈ (ਅਤੇ ਨਿਯਮਤ ਸਮੀਕਰਨ ਦੁਆਰਾ ਵੀ!) ਇੱਕ ਸ਼ਾਨਦਾਰ ਹੈ ਮੋਡੀਊਲ pg_trgm! ਕੇਵਲ ਤਦ ਹੀ ਇਸ ਨੂੰ ਸਹੀ ਢੰਗ ਨਾਲ ਕ੍ਰਮਬੱਧ ਕਰਨ ਦੀ ਲੋੜ ਹੋਵੇਗੀ.

ਆਉ ਮਾਡਲ ਨੂੰ ਸਰਲ ਬਣਾਉਣ ਲਈ ਹੇਠਾਂ ਦਿੱਤੀ ਪਲੇਟ ਨੂੰ ਲੈਣ ਦੀ ਕੋਸ਼ਿਸ਼ ਕਰੀਏ:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

ਅਸੀਂ ਉੱਥੇ ਅਸਲ ਸੰਸਥਾਵਾਂ ਦੇ 7.8 ਮਿਲੀਅਨ ਰਿਕਾਰਡ ਅੱਪਲੋਡ ਕਰਦੇ ਹਾਂ ਅਤੇ ਉਹਨਾਂ ਨੂੰ ਸੂਚੀਬੱਧ ਕਰਦੇ ਹਾਂ:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

ਆਉ ਇੰਟਰਲੀਨੀਅਰ ਖੋਜ ਲਈ ਪਹਿਲੇ 10 ਰਿਕਾਰਡਾਂ ਨੂੰ ਵੇਖੀਏ:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਖੈਰ, ਇਹ... 26ms, 31MB ਡਾਟਾ ਪੜ੍ਹੋ ਅਤੇ 1.7K ਤੋਂ ਵੱਧ ਫਿਲਟਰ ਕੀਤੇ ਰਿਕਾਰਡ - 10 ਖੋਜੀਆਂ ਲਈ। ਓਵਰਹੈੱਡ ਖਰਚੇ ਬਹੁਤ ਜ਼ਿਆਦਾ ਹਨ, ਕੀ ਇੱਥੇ ਕੁਝ ਹੋਰ ਕੁਸ਼ਲ ਨਹੀਂ ਹੈ?

1.2: ਟੈਕਸਟ ਦੁਆਰਾ ਖੋਜ ਕਰੋ? ਇਹ FTS ਹੈ!

ਦਰਅਸਲ, PostgreSQL ਇੱਕ ਬਹੁਤ ਸ਼ਕਤੀਸ਼ਾਲੀ ਪ੍ਰਦਾਨ ਕਰਦਾ ਹੈ ਪੂਰਾ ਟੈਕਸਟ ਖੋਜ ਇੰਜਣ (ਪੂਰੀ ਲਿਖਤ ਖੋਜ), ਅਗੇਤਰ ਖੋਜ ਕਰਨ ਦੀ ਯੋਗਤਾ ਸਮੇਤ। ਇੱਕ ਵਧੀਆ ਵਿਕਲਪ, ਤੁਹਾਨੂੰ ਐਕਸਟੈਂਸ਼ਨਾਂ ਨੂੰ ਸਥਾਪਤ ਕਰਨ ਦੀ ਵੀ ਲੋੜ ਨਹੀਂ ਹੈ! ਚਲੋ ਕੋਸ਼ਿਸ਼ ਕਰੀਏ:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਇੱਥੇ ਪੁੱਛਗਿੱਛ ਐਗਜ਼ੀਕਿਊਸ਼ਨ ਦੇ ਸਮਾਨਤਾ ਨੇ ਸਾਡੀ ਥੋੜੀ ਮਦਦ ਕੀਤੀ, ਸਮੇਂ ਨੂੰ ਅੱਧਾ ਕਰ ਦਿੱਤਾ 11 ਮਿ. ਅਤੇ ਸਾਨੂੰ 1.5 ਗੁਣਾ ਘੱਟ ਪੜ੍ਹਨਾ ਪਿਆ - ਕੁੱਲ ਮਿਲਾ ਕੇ 20MB. ਪਰ ਇੱਥੇ, ਘੱਟ, ਬਿਹਤਰ, ਕਿਉਂਕਿ ਅਸੀਂ ਜਿੰਨੀ ਵੱਡੀ ਮਾਤਰਾ ਨੂੰ ਪੜ੍ਹਦੇ ਹਾਂ, ਕੈਸ਼ ਮਿਸ ਹੋਣ ਦੀ ਸੰਭਾਵਨਾ ਵੱਧ ਹੁੰਦੀ ਹੈ, ਅਤੇ ਡਿਸਕ ਤੋਂ ਪੜ੍ਹੇ ਜਾਣ ਵਾਲੇ ਡੇਟਾ ਦਾ ਹਰ ਵਾਧੂ ਪੰਨਾ ਬੇਨਤੀ ਲਈ ਇੱਕ ਸੰਭਾਵੀ "ਬ੍ਰੇਕ" ਹੁੰਦਾ ਹੈ।

1.3: ਅਜੇ ਵੀ ਪਸੰਦ ਹੈ?

ਪਿਛਲੀ ਬੇਨਤੀ ਸਾਰਿਆਂ ਲਈ ਚੰਗੀ ਹੈ, ਪਰ ਜੇ ਤੁਸੀਂ ਦਿਨ ਵਿਚ ਲੱਖ ਵਾਰੀ ਖਿੱਚੋ ਤਾਂ ਹੀ ਆਵੇਗੀ 2TB ਡਾਟਾ ਪੜ੍ਹੋ. ਸਭ ਤੋਂ ਵਧੀਆ ਕੇਸ ਵਿੱਚ, ਮੈਮੋਰੀ ਤੋਂ, ਪਰ ਜੇ ਤੁਸੀਂ ਬਦਕਿਸਮਤ ਹੋ, ਤਾਂ ਡਿਸਕ ਤੋਂ. ਇਸ ਲਈ ਇਸ ਨੂੰ ਛੋਟਾ ਕਰਨ ਦੀ ਕੋਸ਼ਿਸ਼ ਕਰੀਏ.

ਆਓ ਯਾਦ ਕਰੀਏ ਕਿ ਉਪਭੋਗਤਾ ਕੀ ਦੇਖਣਾ ਚਾਹੁੰਦਾ ਹੈ ਪਹਿਲਾਂ "ਜਿਸ ਨਾਲ ਸ਼ੁਰੂ ਹੁੰਦਾ ਹੈ...". ਇਸ ਲਈ ਇਹ ਆਪਣੇ ਸ਼ੁੱਧ ਰੂਪ ਵਿੱਚ ਹੈ ਅਗੇਤਰ ਖੋਜ ਦੀ ਸਹਾਇਤਾ ਨਾਲ text_pattern_ops! ਅਤੇ ਕੇਵਲ ਤਾਂ ਹੀ ਜੇਕਰ ਸਾਡੇ ਕੋਲ 10 ਰਿਕਾਰਡਾਂ ਤੱਕ "ਕਾਫ਼ੀ ਨਹੀਂ ਹੈ" ਜੋ ਅਸੀਂ ਲੱਭ ਰਹੇ ਹਾਂ, ਤਾਂ ਸਾਨੂੰ FTS ਖੋਜ ਦੀ ਵਰਤੋਂ ਕਰਕੇ ਉਹਨਾਂ ਨੂੰ ਪੜ੍ਹਨਾ ਪੂਰਾ ਕਰਨਾ ਹੋਵੇਗਾ:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਸ਼ਾਨਦਾਰ ਪ੍ਰਦਰਸ਼ਨ - ਕੁੱਲ 0.05ms ਅਤੇ 100KB ਤੋਂ ਥੋੜ੍ਹਾ ਵੱਧ ਪੜ੍ਹੋ! ਸਿਰਫ਼ ਅਸੀਂ ਭੁੱਲ ਗਏ ਨਾਮ ਦੁਆਰਾ ਕ੍ਰਮਬੱਧਤਾਂ ਜੋ ਉਪਭੋਗਤਾ ਨਤੀਜਿਆਂ ਵਿੱਚ ਗੁਆਚ ਨਾ ਜਾਵੇ:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਓਹ, ਕੋਈ ਚੀਜ਼ ਹੁਣ ਇੰਨੀ ਸੁੰਦਰ ਨਹੀਂ ਹੈ - ਅਜਿਹਾ ਲਗਦਾ ਹੈ ਜਿਵੇਂ ਕੋਈ ਸੂਚਕਾਂਕ ਹੈ, ਪਰ ਛਾਂਟੀ ਇਸ ਨੂੰ ਪਾਰ ਕਰਦੀ ਹੈ... ਬੇਸ਼ਕ, ਇਹ ਪਹਿਲਾਂ ਹੀ ਪਿਛਲੇ ਵਿਕਲਪ ਨਾਲੋਂ ਕਈ ਗੁਣਾ ਜ਼ਿਆਦਾ ਪ੍ਰਭਾਵਸ਼ਾਲੀ ਹੈ, ਪਰ...

1.4: "ਇੱਕ ਫਾਈਲ ਨਾਲ ਖਤਮ ਕਰੋ"

ਪਰ ਇੱਕ ਇੰਡੈਕਸ ਹੈ ਜੋ ਤੁਹਾਨੂੰ ਰੇਂਜ ਦੁਆਰਾ ਖੋਜ ਕਰਨ ਦੀ ਇਜਾਜ਼ਤ ਦਿੰਦਾ ਹੈ ਅਤੇ ਫਿਰ ਵੀ ਆਮ ਤੌਰ 'ਤੇ ਛਾਂਟੀ ਦੀ ਵਰਤੋਂ ਕਰਦਾ ਹੈ - ਨਿਯਮਤ btree!

CREATE INDEX ON firms(lower(name));

ਸਿਰਫ਼ ਇਸ ਲਈ ਬੇਨਤੀ ਨੂੰ "ਹੱਥੀਂ ਇਕੱਠਾ" ਕਰਨਾ ਹੋਵੇਗਾ:

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

ਸ਼ਾਨਦਾਰ - ਛਾਂਟੀ ਦਾ ਕੰਮ, ਅਤੇ ਸਰੋਤ ਦੀ ਖਪਤ "ਮਾਈਕ੍ਰੋਸਕੋਪਿਕ" ਰਹਿੰਦੀ ਹੈ, "ਸ਼ੁੱਧ" FTS ਨਾਲੋਂ ਹਜ਼ਾਰਾਂ ਗੁਣਾ ਜ਼ਿਆਦਾ ਪ੍ਰਭਾਵਸ਼ਾਲੀ! ਜੋ ਕੁਝ ਬਚਿਆ ਹੈ ਉਹ ਇਸਨੂੰ ਇੱਕ ਸਿੰਗਲ ਬੇਨਤੀ ਵਿੱਚ ਜੋੜਨਾ ਹੈ:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

ਨੋਟ ਕਰੋ ਕਿ ਦੂਜੀ ਸਬਕਵੇਰੀ ਚਲਾਈ ਗਈ ਹੈ ਕੇਵਲ ਤਾਂ ਹੀ ਜੇਕਰ ਪਹਿਲਾ ਇੱਕ ਉਮੀਦ ਤੋਂ ਘੱਟ ਵਾਪਸ ਆਇਆ ਆਖਰੀ LIMIT ਲਾਈਨਾਂ ਦੀ ਗਿਣਤੀ। ਮੈਂ ਪੁੱਛਗਿੱਛ ਅਨੁਕੂਲਨ ਦੀ ਇਸ ਵਿਧੀ ਬਾਰੇ ਗੱਲ ਕਰ ਰਿਹਾ ਹਾਂ ਪਹਿਲਾਂ ਹੀ ਲਿਖਿਆ ਸੀ.

ਇਸ ਲਈ ਹਾਂ, ਸਾਡੇ ਕੋਲ ਹੁਣ ਮੇਜ਼ 'ਤੇ btree ਅਤੇ gin ਦੋਵੇਂ ਹਨ, ਪਰ ਅੰਕੜਿਆਂ ਅਨੁਸਾਰ ਇਹ ਪਤਾ ਚਲਦਾ ਹੈ ਕਿ 10% ਤੋਂ ਘੱਟ ਬੇਨਤੀਆਂ ਦੂਜੇ ਬਲਾਕ ਦੇ ਐਗਜ਼ੀਕਿਊਸ਼ਨ ਤੱਕ ਪਹੁੰਚਦੀਆਂ ਹਨ. ਭਾਵ, ਕੰਮ ਲਈ ਪਹਿਲਾਂ ਤੋਂ ਜਾਣੀਆਂ ਗਈਆਂ ਅਜਿਹੀਆਂ ਖਾਸ ਸੀਮਾਵਾਂ ਦੇ ਨਾਲ, ਅਸੀਂ ਸਰਵਰ ਸਰੋਤਾਂ ਦੀ ਕੁੱਲ ਖਪਤ ਨੂੰ ਲਗਭਗ ਇੱਕ ਹਜ਼ਾਰ ਗੁਣਾ ਘਟਾਉਣ ਦੇ ਯੋਗ ਸੀ!

1.5*: ਅਸੀਂ ਬਿਨਾਂ ਫਾਈਲ ਦੇ ਕਰ ਸਕਦੇ ਹਾਂ

ਉੱਪਰ LIKE ਸਾਨੂੰ ਗਲਤ ਛਾਂਟੀ ਦੀ ਵਰਤੋਂ ਕਰਨ ਤੋਂ ਰੋਕਿਆ ਗਿਆ ਸੀ। ਪਰ ਇਸਨੂੰ USING ਆਪਰੇਟਰ ਨੂੰ ਨਿਸ਼ਚਿਤ ਕਰਕੇ "ਸਹੀ ਮਾਰਗ 'ਤੇ ਸੈੱਟ" ਕੀਤਾ ਜਾ ਸਕਦਾ ਹੈ:

ਮੂਲ ਰੂਪ ਵਿੱਚ ਇਹ ਮੰਨਿਆ ਜਾਂਦਾ ਹੈ ASC. ਇਸ ਤੋਂ ਇਲਾਵਾ, ਤੁਸੀਂ ਇੱਕ ਧਾਰਾ ਵਿੱਚ ਇੱਕ ਖਾਸ ਲੜੀਬੱਧ ਓਪਰੇਟਰ ਦਾ ਨਾਮ ਨਿਰਧਾਰਤ ਕਰ ਸਕਦੇ ਹੋ USING. ਲੜੀਬੱਧ ਆਪਰੇਟਰ ਬੀ-ਟ੍ਰੀ ਓਪਰੇਟਰਾਂ ਦੇ ਕੁਝ ਪਰਿਵਾਰ ਤੋਂ ਘੱਟ ਜਾਂ ਵੱਧ ਦਾ ਮੈਂਬਰ ਹੋਣਾ ਚਾਹੀਦਾ ਹੈ। ASC ਆਮ ਤੌਰ 'ਤੇ ਬਰਾਬਰ USING < и DESC ਆਮ ਤੌਰ 'ਤੇ ਬਰਾਬਰ USING >.

ਸਾਡੇ ਕੇਸ ਵਿੱਚ, "ਘੱਟ" ਹੈ ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

[explanation.tensor.ru 'ਤੇ ਦੇਖੋ]

2: ਬੇਨਤੀਆਂ ਕਿਵੇਂ ਖੱਟੇ ਹੋ ਜਾਂਦੀਆਂ ਹਨ

ਹੁਣ ਅਸੀਂ ਆਪਣੀ ਬੇਨਤੀ ਨੂੰ ਛੇ ਮਹੀਨਿਆਂ ਜਾਂ ਇੱਕ ਸਾਲ ਲਈ "ਉਬਾਲਣ" ਲਈ ਛੱਡ ਦਿੰਦੇ ਹਾਂ, ਅਤੇ ਮੈਮੋਰੀ ਦੇ ਕੁੱਲ ਰੋਜ਼ਾਨਾ "ਪੰਪਿੰਗ" ਦੇ ਸੰਕੇਤਾਂ ਦੇ ਨਾਲ ਇਸਨੂੰ ਦੁਬਾਰਾ "ਸਿਖਰ 'ਤੇ" ਲੱਭ ਕੇ ਅਸੀਂ ਹੈਰਾਨ ਹਾਂ।ਬਫਰ ਸ਼ੇਅਰ ਹਿੱਟ) ਵਿੱਚ 5.5TB - ਭਾਵ, ਇਸ ਤੋਂ ਵੀ ਵੱਧ ਇਹ ਅਸਲ ਵਿੱਚ ਸੀ।

ਨਹੀਂ, ਬੇਸ਼ੱਕ, ਸਾਡਾ ਕਾਰੋਬਾਰ ਵਧਿਆ ਹੈ ਅਤੇ ਸਾਡੇ ਕੰਮ ਦਾ ਬੋਝ ਵਧਿਆ ਹੈ, ਪਰ ਉਸੇ ਰਕਮ ਨਾਲ ਨਹੀਂ! ਇਸਦਾ ਮਤਲਬ ਹੈ ਕਿ ਇੱਥੇ ਕੁਝ ਫਿਸ਼ ਹੈ - ਚਲੋ ਇਸਦਾ ਪਤਾ ਲਗਾਓ।

2.1: ਪੇਜਿੰਗ ਦਾ ਜਨਮ

ਕਿਸੇ ਸਮੇਂ, ਇੱਕ ਹੋਰ ਵਿਕਾਸ ਟੀਮ ਉਸੇ ਦੇ ਨਾਲ ਰਜਿਸਟਰੀ ਵਿੱਚ ਇੱਕ ਤੇਜ਼ ਸਬਸਕ੍ਰਿਪਟ ਖੋਜ ਤੋਂ "ਛਾਲਣਾ" ਸੰਭਵ ਬਣਾਉਣਾ ਚਾਹੁੰਦੀ ਸੀ, ਪਰ ਵਿਸਤ੍ਰਿਤ ਨਤੀਜਿਆਂ. ਪੰਨਾ ਨੈਵੀਗੇਸ਼ਨ ਤੋਂ ਬਿਨਾਂ ਰਜਿਸਟਰੀ ਕੀ ਹੈ? ਆਓ ਇਸ ਨੂੰ ਪੇਚ ਕਰੀਏ!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

ਹੁਣ ਡਿਵੈਲਪਰ ਲਈ ਬਿਨਾਂ ਕਿਸੇ ਤਣਾਅ ਦੇ "ਪੰਨੇ-ਦਰ-ਪੰਨੇ" ਲੋਡਿੰਗ ਦੇ ਨਾਲ ਖੋਜ ਨਤੀਜਿਆਂ ਦੀ ਰਜਿਸਟਰੀ ਨੂੰ ਦਿਖਾਉਣਾ ਸੰਭਵ ਸੀ।

ਬੇਸ਼ੱਕ, ਅਸਲ ਵਿੱਚ, ਡੇਟਾ ਦੇ ਹਰੇਕ ਅਗਲੇ ਪੰਨੇ ਲਈ ਵੱਧ ਤੋਂ ਵੱਧ ਪੜ੍ਹਿਆ ਜਾਂਦਾ ਹੈ (ਸਾਰੇ ਪਿਛਲੇ ਸਮੇਂ ਤੋਂ, ਜਿਸ ਨੂੰ ਅਸੀਂ ਰੱਦ ਕਰ ਦੇਵਾਂਗੇ, ਨਾਲ ਹੀ ਜ਼ਰੂਰੀ "ਪੂਛ") - ਭਾਵ, ਇਹ ਇੱਕ ਸਪੱਸ਼ਟ ਐਂਟੀਪੈਟਰਨ ਹੈ. ਪਰ ਇੰਟਰਫੇਸ ਵਿੱਚ ਸਟੋਰ ਕੀਤੀ ਕੁੰਜੀ ਤੋਂ ਅਗਲੀ ਦੁਹਰਾਓ 'ਤੇ ਖੋਜ ਸ਼ੁਰੂ ਕਰਨਾ ਵਧੇਰੇ ਸਹੀ ਹੋਵੇਗਾ, ਪਰ ਇਸ ਬਾਰੇ ਕਿਸੇ ਹੋਰ ਸਮੇਂ।

2.2: ਮੈਨੂੰ ਕੁਝ ਵਿਦੇਸ਼ੀ ਚਾਹੀਦਾ ਹੈ

ਕਿਸੇ ਸਮੇਂ ਡਿਵੈਲਪਰ ਚਾਹੁੰਦਾ ਸੀ ਨਤੀਜੇ ਵਾਲੇ ਨਮੂਨੇ ਨੂੰ ਡੇਟਾ ਦੇ ਨਾਲ ਵਿਭਿੰਨ ਬਣਾਓ ਕਿਸੇ ਹੋਰ ਸਾਰਣੀ ਤੋਂ, ਜਿਸ ਲਈ ਸਾਰੀ ਪਿਛਲੀ ਬੇਨਤੀ CTE ਨੂੰ ਭੇਜੀ ਗਈ ਸੀ:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

ਅਤੇ ਫਿਰ ਵੀ, ਇਹ ਬੁਰਾ ਨਹੀਂ ਹੈ, ਕਿਉਂਕਿ ਸਬਕਵੇਰੀ ਦਾ ਮੁਲਾਂਕਣ ਸਿਰਫ 10 ਵਾਪਸ ਕੀਤੇ ਰਿਕਾਰਡਾਂ ਲਈ ਕੀਤਾ ਜਾਂਦਾ ਹੈ, ਜੇ ਨਹੀਂ ...

2.3: DISTINCT ਬੇਸਮਝ ਅਤੇ ਬੇਰਹਿਮ ਹੈ

ਕਿਤੇ ਦੂਜੀ ਸਬਕਵੇਰੀ ਤੋਂ ਅਜਿਹੇ ਵਿਕਾਸ ਦੀ ਪ੍ਰਕਿਰਿਆ ਵਿੱਚ ਗੁੰਮ ਹੋ ਗਿਆ NOT LIKE ਸ਼ਰਤ. ਇਹ ਸਪੱਸ਼ਟ ਹੈ ਕਿ ਇਸ ਤੋਂ ਬਾਅਦ UNION ALL ਵਾਪਸ ਆਉਣਾ ਸ਼ੁਰੂ ਕਰ ਦਿੱਤਾ ਕੁਝ ਇੰਦਰਾਜ਼ ਦੋ ਵਾਰ - ਪਹਿਲਾਂ ਲਾਈਨ ਦੇ ਸ਼ੁਰੂ ਵਿੱਚ ਪਾਇਆ ਗਿਆ, ਅਤੇ ਫਿਰ ਦੁਬਾਰਾ - ਇਸ ਲਾਈਨ ਦੇ ਪਹਿਲੇ ਸ਼ਬਦ ਦੇ ਸ਼ੁਰੂ ਵਿੱਚ। ਸੀਮਾ ਵਿੱਚ, ਦੂਜੀ ਸਬਕਵੇਰੀ ਦੇ ਸਾਰੇ ਰਿਕਾਰਡ ਪਹਿਲੇ ਦੇ ਰਿਕਾਰਡਾਂ ਨਾਲ ਮੇਲ ਖਾਂਦੇ ਹਨ।

ਇੱਕ ਡਿਵੈਲਪਰ ਕਾਰਨ ਲੱਭਣ ਦੀ ਬਜਾਏ ਕੀ ਕਰਦਾ ਹੈ?... ਕੋਈ ਸਵਾਲ ਨਹੀਂ!

• ਆਕਾਰ ਨੂੰ ਦੁੱਗਣਾ ਅਸਲੀ ਨਮੂਨੇ
• DISTINCT ਲਾਗੂ ਕਰੋਹਰ ਇੱਕ ਲਾਈਨ ਦੇ ਸਿਰਫ਼ ਇੱਕ ਉਦਾਹਰਨ ਪ੍ਰਾਪਤ ਕਰਨ ਲਈ

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

ਭਾਵ, ਇਹ ਸਪੱਸ਼ਟ ਹੈ ਕਿ ਨਤੀਜਾ, ਅੰਤ ਵਿੱਚ, ਬਿਲਕੁਲ ਉਹੀ ਹੈ, ਪਰ 2nd CTE ਸਬਕਵੇਰੀ ਵਿੱਚ "ਉੱਡਣ" ਦੀ ਸੰਭਾਵਨਾ ਬਹੁਤ ਜ਼ਿਆਦਾ ਹੋ ਗਈ ਹੈ, ਅਤੇ ਇਸ ਤੋਂ ਬਿਨਾਂ ਵੀ, ਸਪੱਸ਼ਟ ਤੌਰ 'ਤੇ ਵਧੇਰੇ ਪੜ੍ਹਨਯੋਗ.

ਪਰ ਇਹ ਸਭ ਤੋਂ ਦੁਖਦਾਈ ਗੱਲ ਨਹੀਂ ਹੈ. ਕਿਉਂਕਿ ਡਿਵੈਲਪਰ ਨੇ ਚੁਣਨ ਲਈ ਕਿਹਾ ਹੈ DISTINCT ਖਾਸ ਲੋਕਾਂ ਲਈ ਨਹੀਂ, ਪਰ ਇੱਕ ਵਾਰ ਵਿੱਚ ਸਾਰੇ ਖੇਤਰਾਂ ਲਈ ਰਿਕਾਰਡ, ਫਿਰ ਸਬ_ਕਵੇਰੀ ਫੀਲਡ — ਸਬਕਵੇਰੀ ਦਾ ਨਤੀਜਾ — ਉੱਥੇ ਆਟੋਮੈਟਿਕਲੀ ਸ਼ਾਮਲ ਕੀਤਾ ਗਿਆ ਸੀ। ਹੁਣ, ਚਲਾਉਣ ਲਈ DISTINCT, ਡੇਟਾਬੇਸ ਨੂੰ ਪਹਿਲਾਂ ਹੀ ਚਲਾਉਣਾ ਸੀ 10 ਸਬਕਵੇਰੀਆਂ ਨਹੀਂ, ਪਰ ਸਾਰੀਆਂ <2 * N> + 10!

2.4: ਸਭ ਤੋਂ ਉੱਪਰ ਸਹਿਯੋਗ!

ਇਸ ਲਈ, ਡਿਵੈਲਪਰ ਰਹਿੰਦੇ ਸਨ - ਉਹਨਾਂ ਨੇ ਪਰੇਸ਼ਾਨ ਨਹੀਂ ਕੀਤਾ, ਕਿਉਂਕਿ ਉਪਭੋਗਤਾ ਕੋਲ ਸਪੱਸ਼ਟ ਤੌਰ 'ਤੇ ਹਰੇਕ ਅਗਲੇ "ਪੰਨੇ" ਨੂੰ ਪ੍ਰਾਪਤ ਕਰਨ ਵਿੱਚ ਇੱਕ ਗੰਭੀਰ ਮੰਦੀ ਦੇ ਨਾਲ ਮਹੱਤਵਪੂਰਣ N ਮੁੱਲਾਂ ਵਿੱਚ ਰਜਿਸਟਰੀ ਨੂੰ "ਅਡਜਸਟ" ਕਰਨ ਲਈ ਕਾਫ਼ੀ ਧੀਰਜ ਨਹੀਂ ਸੀ।

ਜਦੋਂ ਤੱਕ ਕਿਸੇ ਹੋਰ ਵਿਭਾਗ ਦੇ ਡਿਵੈਲਪਰ ਉਨ੍ਹਾਂ ਕੋਲ ਨਹੀਂ ਆਉਂਦੇ ਅਤੇ ਅਜਿਹੇ ਸੁਵਿਧਾਜਨਕ ਢੰਗ ਦੀ ਵਰਤੋਂ ਕਰਨਾ ਚਾਹੁੰਦੇ ਸਨ ਦੁਹਰਾਓ ਖੋਜ ਲਈ - ਭਾਵ, ਅਸੀਂ ਕੁਝ ਨਮੂਨੇ ਤੋਂ ਇੱਕ ਟੁਕੜਾ ਲੈਂਦੇ ਹਾਂ, ਇਸ ਨੂੰ ਵਾਧੂ ਸ਼ਰਤਾਂ ਦੁਆਰਾ ਫਿਲਟਰ ਕਰਦੇ ਹਾਂ, ਨਤੀਜਾ ਖਿੱਚਦੇ ਹਾਂ, ਫਿਰ ਅਗਲਾ ਟੁਕੜਾ (ਜੋ ਸਾਡੇ ਕੇਸ ਵਿੱਚ N ਨੂੰ ਵਧਾ ਕੇ ਪ੍ਰਾਪਤ ਕੀਤਾ ਜਾਂਦਾ ਹੈ), ਅਤੇ ਇਸ ਤਰ੍ਹਾਂ ਉਦੋਂ ਤੱਕ ਜਦੋਂ ਤੱਕ ਅਸੀਂ ਸਕ੍ਰੀਨ ਨਹੀਂ ਭਰਦੇ।

ਆਮ ਤੌਰ 'ਤੇ, ਫੜੇ ਗਏ ਨਮੂਨੇ ਵਿੱਚ N ਲਗਭਗ 17K ਦੇ ਮੁੱਲਾਂ 'ਤੇ ਪਹੁੰਚ ਗਿਆ, ਅਤੇ ਕੇਵਲ ਇੱਕ ਦਿਨ ਵਿੱਚ ਘੱਟੋ-ਘੱਟ 4K ਅਜਿਹੀਆਂ ਬੇਨਤੀਆਂ ਨੂੰ "ਸੰਗੀਤ ਦੇ ਨਾਲ" ਲਾਗੂ ਕੀਤਾ ਗਿਆ ਸੀ। ਉਨ੍ਹਾਂ ਵਿੱਚੋਂ ਆਖਰੀ ਨੂੰ ਦਲੇਰੀ ਨਾਲ ਸਕੈਨ ਕੀਤਾ ਗਿਆ ਸੀ ਪ੍ਰਤੀ ਦੁਹਰਾਓ 1GB ਮੈਮੋਰੀ...

ਕੁੱਲ

ਸਰੋਤ: www.habr.com