PostgreSQL Antipatterns: un conte sur le raffinement itératif d'une recherche par nom, ou «aller-retour d'optimisation»

0: que voulait l'utilisateur

1: limiter la tâche

1.0: moteur de recherche externe

1.1: sous-chaîne «honnête»

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: recherche de texte? c'est 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: toujours comme?

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: «modifier avec un fichier»

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 *: supprimer le fichier

La valeur par défaut est implicite ASC. De plus, vous pouvez spécifier le nom d'un opérateur de tri spécifique dans une phrase USING. L'opérateur de tri doit être un membre inférieur ou supérieur à une certaine famille d'opérateurs B-tree. ASCgénéralement équivalent USING <et DESCgénéralement équivalent USING >.

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

2: comment "aigrir" les demandes

2.1: la naissance de la pagination

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

2.2: envie d'exotisme

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

2.3: DISTINCT insignifiant et impitoyable

• doubler la taille des échantillons originaux
• mettez DISTINCT pour que nous n'obtenions que des instances uniques de chaque ligne

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: la coopération avant tout!