PostgreSQL Antipatterns: नाम से खोज के पुनरावृत्ति शोधन, या "अनुकूलन आगे और पीछे" के बारे में एक कहानी

0: उपयोगकर्ता क्या चाहता था

1: कार्य को सीमित करें

1.0: बाहरी खोज इंजन

1.1: "ईमानदार" विकल्प

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

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

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|\s)' || '')
ORDER BY
  lower(name) ~ ('^' || '') DESC --  " "
, lower(name) --   
LIMIT 10;

1.2: पाठ खोज? यह FTS है!

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;

1.3: अभी भी पसंद है?

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

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

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

1.4: "एक फ़ाइल के साथ संशोधित करें"

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;

(
  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;

1.5 *: फाइल के साथ डिस्पेंस

डिफ़ॉल्ट निहित है ASC। इसके अलावा, आप एक वाक्य में एक विशिष्ट प्रकार के ऑपरेटर का नाम निर्दिष्ट कर सकते हैं USING। सॉर्ट ऑपरेटर बी-ट्री ऑपरेटरों के एक निश्चित परिवार से कम या अधिक का सदस्य होना चाहिए। ASCआमतौर पर समतुल्य USING <और DESCआमतौर पर समतुल्य USING >।

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

2: कैसे "खट्टा" अनुरोध करने के लिए

२.१: पेजिंग का जन्म

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

२.२: विदेशी चाहिए

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- -    
FROM
  q
LIMIT 10 OFFSET <N>;

2.3: DISTINCT अर्थहीन और निर्दयी है

• मूल नमूनों का आकार दोगुना
• 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>;

2.4: सब से ऊपर सहयोग!