Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Compare current "B-tree indexes on array columns" with query by Kirill Borovikov #43

Open
mfvanek opened this issue May 4, 2024 · 3 comments
Assignees
Labels
enhancement New feature or request

Comments

@mfvanek
Copy link
Owner

mfvanek commented May 4, 2024

Need to compare current version of sql query with version by Kirill Borovikov from https://habr.com/ru/companies/tensor/articles/488104/

WITH sch AS (
  SELECT
    'public'::text sch -- schema
)
, def AS (
  SELECT
    clr.relname nmt
  , cli.relname nmi
  , pg_get_indexdef(cli.oid) def
  , cli.oid clioid
  , clr
  , cli
  , idx
, (
    SELECT
      array_agg(T::text ORDER BY f.i)
    FROM
      (
        SELECT
          clr.oid rel
        , i
        , idx.indkey[i] ik
        FROM
          generate_subscripts(idx.indkey, 1) i
      ) f
    JOIN
      pg_attribute T
        ON (T.attrelid, T.attnum) = (f.rel, f.ik)
  ) fld$
  FROM
    pg_class clr
  JOIN
    pg_index idx
      ON idx.indrelid = clr.oid
  JOIN
    pg_class cli
      ON cli.oid = idx.indexrelid
  JOIN
    pg_namespace nsp
      ON nsp.oid = cli.relnamespace AND
      nsp.nspname = (TABLE sch)
  WHERE
    NOT idx.indisunique AND
    idx.indisready AND
    idx.indisvalid AND
    cli.relam = (
      SELECT
        oid
      FROM
        pg_am
      WHERE
        amname = 'btree'
      LIMIT 1
    )
  ORDER BY
    clr.relname, cli.relname
)
, fld AS (
  SELECT
    *
  , ARRAY(
      SELECT
        (att::pg_attribute).attname
      FROM
        unnest(fld$) att
    ) nmf$
  , ARRAY(
      SELECT
        (
          SELECT
            typname
          FROM
            pg_type
          WHERE
            oid = (att::pg_attribute).atttypid
        )
      FROM
        unnest(fld$) att
    ) tpf$
  FROM
    def
)
SELECT
  nmt
, nmi
, nmf$
, tpf$
, def
FROM
  fld
WHERE
  tpf$ && ARRAY(
    SELECT
      typname
    FROM
      pg_type
    WHERE
      typname ~ '^_'
  )
ORDER BY
  1, 2;
  1. Use explain(analyze, buffers)
  2. Attach execution plans
  3. Choose the best option
@mfvanek
Copy link
Owner Author

mfvanek commented May 4, 2024

Child of mfvanek/pg-index-health#364

@mfvanek mfvanek added enhancement New feature or request help wanted Extra attention is needed labels May 4, 2024
@mfaulther
Copy link
Collaborator

Hi, @mfvanek
May i take this issue ?

@mfvanek mfvanek removed the help wanted Extra attention is needed label Jun 20, 2024
@mfvanek
Copy link
Owner Author

mfvanek commented Jun 20, 2024

Hi, @mfvanek May i take this issue ?

Hi @mfaulther!
Yes, of course

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

2 participants