Datenbanken « DevBox

Mitunter möchte man ein Abfrageergebnis nach einer vorgegebenen Liste von IDs sortieren, also weder alphabetisch nach Namen, noch numerisch nach Wert, sondern auf Basis einer konfigurierten Reihenfolge von Werten. Da man in PostgreSQL auch Funktions-Ergebnisse als Argumente für eine Sortierung verwenden kann, können wir eine Hilfsfunktion in der Datenbank deklarieren, über die sich diese Form der Sortierung umsetzen lässt:

CREATE OR REPLACE FUNCTION arridx(anyarray, anyelement)
  RETURNS integer AS
$BODY$
  SELECT i FROM (
     SELECT generate_series(array_lower($1,1),array_upper($1,1))
  ) g(i)
  WHERE $1[i] = $2
  LIMIT 1;
$BODY$
  LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION arridx(anyarray, anyelement)

RETURNS integer AS

$BODY$

SELECT i FROM (

SELECT generate_series(array_lower($1,1),array_upper($1,1))

) g(i)

WHERE $1[i] = $2

LIMIT 1;

$BODY$

LANGUAGE sql IMMUTABLE;

Jetzt können wir die Funktion zur Sortierung einsetzen:

SELECT n.node_id, n.name
FROM nodes n
ORDER BY arridx(ARRAY[5,1,3,8,12],n.node_id) NULLS LAST, n.name ASC;

SELECT n.node_id, n.name

FROM nodes n

ORDER BY arridx(ARRAY[5,1,3,8,12],n.node_id) NULLS LAST, n.name ASC;

Das NULLS LAST stellt sicher, dass Werte die nicht in der Liste enthalten sind ans Ende des Ergebnisses sortiert werden.

Bei sehr umfangreichen Datenmengen kann die direkte Nutzung eines Index für die Funktion sinnvoll sein. Die ist aufgrund der IMMUTABLE-Deklaration auch möglich, allerdings nur für eine spezifische Werteliste, die damit Teil der Index-Deklaration werden muss. Im Normalfall genügt allerdings ein Index auf das verwendete Feld selbst.

In ColdFusion kann auch an dieser Stelle <cfqueryparam> für die Sortierung eingesetzt werden. Wichtig ist dabei, dass hier der richtige Datentyp für die Liste gewählt wird – z.B. wenn die Spalte vom Typ int8/bigInt ist. Auch einfache int-Arrays (cf_sql_integer) oder Text-Arrays (cf_sql_varchar) sind natürlich möglich.

<cfset variables.lstIds='5,1,3,8,12' />
<cfquery name="variables.qGetExample" datasource="myDS">
SELECT n.node_id, n.name
FROM nodes n
ORDER BY arridx(ARRAY[
  <cfqueryparam cfsqltype="cf_sql_bigint" 
    list="yes" value="#variables.lstIds#" />
],n.node_id) NULLS LAST, n.name ASC;
</cfquery>

SELECT n.node_id, n.name

FROM nodes n

ORDER BY arridx(ARRAY[

<cfqueryparam cfsqltype="cf_sql_bigint"

list="yes" value="#variables.lstIds#" />

],n.node_id) NULLS LAST, n.name ASC;

</cfquery>

New New Index Bloat Query

WITH btree_index_atts AS (
    SELECT nspname, relname, reltuples, relpages, indrelid, relam,
        regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
        indexrelid as index_oid
    FROM pg_index
    JOIN pg_class ON pg_class.oid=pg_index.indexrelid
    JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    JOIN pg_am ON pg_class.relam = pg_am.oid
    WHERE pg_am.amname = 'btree'
    ),
index_item_sizes AS (
    SELECT
    i.nspname, i.relname, i.reltuples, i.relpages, i.relam,
    s.starelid, a.attrelid AS table_oid, index_oid,
    current_setting('block_size')::numeric AS bs,
    /* MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?) */
    CASE
        WHEN version() ~ 'mingw32' OR version() ~ '64-bit' THEN 8
        ELSE 4
    END AS maxalign,
    24 AS pagehdr,
    /* per tuple header: add index_attribute_bm if some cols are null-able */
    CASE WHEN max(coalesce(s.stanullfrac,0)) = 0
        THEN 2
        ELSE 6
    END AS index_tuple_hdr,
    /* data len: we remove null values save space using it fractionnal part from stats */
    sum( (1-coalesce(s.stanullfrac, 0)) * coalesce(s.stawidth, 2048) ) AS nulldatawidth
    FROM pg_attribute AS a
    JOIN pg_statistic AS s ON s.starelid=a.attrelid AND s.staattnum = a.attnum
    JOIN btree_index_atts AS i ON i.indrelid = a.attrelid AND a.attnum = i.attnum
    WHERE a.attnum > 0
    GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
),
index_aligned AS (
    SELECT maxalign, bs, nspname, relname AS index_name, reltuples,
        relpages, relam, table_oid, index_oid,
      ( 2 +
          maxalign - CASE /* Add padding to the index tuple header to align on MAXALIGN */
            WHEN 2%maxalign = 0 THEN maxalign
            ELSE 2%maxalign
          END
        + nulldatawidth + maxalign - CASE /* Add padding to the data to align on MAXALIGN */
            WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
            ELSE nulldatawidth::integer%maxalign
          END
      )::numeric AS nulldatahdrwidth, pagehdr
    FROM index_item_sizes AS s1
),
otta_calc AS (
  SELECT bs, nspname, table_oid, index_oid, index_name, relpages, coalesce(
    ceil((reltuples*(4+nulldatahdrwidth))/(bs-pagehdr::float)) +
      CASE WHEN am.amname IN ('hash','btree') THEN 1 ELSE 0 END , 0 -- btree and hash have a metadata reserved block
    ) AS otta
  FROM index_aligned AS s2
    LEFT JOIN pg_am am ON s2.relam = am.oid
),
raw_bloat AS (
    SELECT current_database() as dbname, nspname, c.relname AS table_name, index_name,
        bs*(sub.relpages)::bigint AS totalbytes,
        CASE
            WHEN sub.relpages <= otta THEN 0
            ELSE bs*(sub.relpages-otta)::bigint END
            AS wastedbytes,
        CASE
            WHEN sub.relpages <= otta
            THEN 0 ELSE bs*(sub.relpages-otta)::bigint * 100 / (bs*(sub.relpages)::bigint) END
            AS realbloat,
        pg_relation_size(sub.table_oid) as table_bytes,
        stat.idx_scan as index_scans
    FROM otta_calc AS sub
    JOIN pg_class AS c ON c.oid=sub.table_oid
    JOIN pg_stat_user_indexes AS stat ON sub.index_oid = stat.indexrelid
)
SELECT dbname as database_name, nspname as schema_name, table_name, index_name,
        round(realbloat, 1) as bloat_pct,
        wastedbytes as bloat_bytes, pg_size_pretty(wastedbytes) as bloat_size,
        totalbytes as index_bytes, pg_size_pretty(totalbytes) as index_size,
        table_bytes, pg_size_pretty(table_bytes) as table_size,
        index_scans
FROM raw_bloat
WHERE ( realbloat > 50 and wastedbytes > 50000000 )
ORDER BY wastedbytes DESC;

WITH btree_index_atts AS (

SELECT nspname, relname, reltuples, relpages, indrelid, relam,

regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,

indexrelid as index_oid

FROM pg_index

JOIN pg_class ON pg_class.oid=pg_index.indexrelid

JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace

JOIN pg_am ON pg_class.relam = pg_am.oid

WHERE pg_am.amname = 'btree'

index_item_sizes AS (

SELECT

i.nspname, i.relname, i.reltuples, i.relpages, i.relam,

s.starelid, a.attrelid AS table_oid, index_oid,

current_setting('block_size')::numeric AS bs,

/* MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?) */

CASE

WHEN version() ~ 'mingw32' OR version() ~ '64-bit' THEN 8

ELSE 4

END AS maxalign,

24 AS pagehdr,

/* per tuple header: add index_attribute_bm if some cols are null-able */

CASE WHEN max(coalesce(s.stanullfrac,0)) = 0

THEN 2

ELSE 6

END AS index_tuple_hdr,

/* data len: we remove null values save space using it fractionnal part from stats */

sum( (1-coalesce(s.stanullfrac, 0)) * coalesce(s.stawidth, 2048) ) AS nulldatawidth

FROM pg_attribute AS a

JOIN pg_statistic AS s ON s.starelid=a.attrelid AND s.staattnum = a.attnum

JOIN btree_index_atts AS i ON i.indrelid = a.attrelid AND a.attnum = i.attnum

WHERE a.attnum > 0

GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9

index_aligned AS (

SELECT maxalign, bs, nspname, relname AS index_name, reltuples,

relpages, relam, table_oid, index_oid,

( 2 +

maxalign - CASE /* Add padding to the index tuple header to align on MAXALIGN */

WHEN 2%maxalign = 0 THEN maxalign

ELSE 2%maxalign

END

+ nulldatawidth + maxalign - CASE /* Add padding to the data to align on MAXALIGN */

WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign

ELSE nulldatawidth::integer%maxalign

END

)::numeric AS nulldatahdrwidth, pagehdr

FROM index_item_sizes AS s1

otta_calc AS (

SELECT bs, nspname, table_oid, index_oid, index_name, relpages, coalesce(

ceil((reltuples*(4+nulldatahdrwidth))/(bs-pagehdr::float)) +

CASE WHEN am.amname IN ('hash','btree') THEN 1 ELSE 0 END , 0 -- btree and hash have a metadata reserved block

) AS otta

FROM index_aligned AS s2

LEFT JOIN pg_am am ON s2.relam = am.oid

raw_bloat AS (

SELECT current_database() as dbname, nspname, c.relname AS table_name, index_name,

bs*(sub.relpages)::bigint AS totalbytes,

CASE

WHEN sub.relpages <= otta THEN 0

ELSE bs*(sub.relpages-otta)::bigint END

AS wastedbytes,

CASE

WHEN sub.relpages <= otta

THEN 0 ELSE bs*(sub.relpages-otta)::bigint * 100 / (bs*(sub.relpages)::bigint) END

AS realbloat,

pg_relation_size(sub.table_oid) as table_bytes,

stat.idx_scan as index_scans

FROM otta_calc AS sub

JOIN pg_class AS c ON c.oid=sub.table_oid

JOIN pg_stat_user_indexes AS stat ON sub.index_oid = stat.indexrelid

)

SELECT dbname as database_name, nspname as schema_name, table_name, index_name,

round(realbloat, 1) as bloat_pct,

wastedbytes as bloat_bytes, pg_size_pretty(wastedbytes) as bloat_size,

totalbytes as index_bytes, pg_size_pretty(totalbytes) as index_size,

table_bytes, pg_size_pretty(table_bytes) as table_size,

index_scans

FROM raw_bloat

WHERE ( realbloat > 50 and wastedbytes > 50000000 )

ORDER BY wastedbytes DESC;

Quelle: https://gist.github.com/jberkus/9923948

Putting stats_temp_directory on a ramdisk – schnelle und billige Perfomance-Optimierung; wieviel das im Einzelfall bringt ist unterschiedlich, speziell VACUUM sollte davon aber durchaus profitieren.

Wir haben eine Tabelle zum Tracking von Suchwörtern, d.h. den Bestandteilen einer Suchphrase:

CREATE TABLE stats.tracking_keywords
(
id bigserial,
keyword text NOT NULL,
site_id integer NOT NULL,
created timestamp without time zone NOT NULL,
user_id integer NOT NULL,
CONSTRAINT "pk_t_kw" PRIMARY KEY (id)
)WITHOUT OIDS;

CREATE TABLE stats.tracking_keywords

(

id bigserial,

keyword text NOT NULL,

site_id integer NOT NULL,

created timestamp without time zone NOT NULL,

user_id integer NOT NULL,

CONSTRAINT "pk_t_kw" PRIMARY KEY (id)

)WITHOUT OIDS;

Um jetzt aus einer um Dropwords und bösartigen/dämlichen Blödsinn (nicht druckbare Zeichen, Deppen-Apostrophe, ggf. Satzzeichen) bereinigten Phrase die einzelnen Keywords einzufügen, kann man die Phrase natürlich im Applikationscode an den Leerzeichen splitten und dann in einem Loop die einzelnen Wörter einfügen. Aus einer Suchphrase von drei Wörtern werden dann drei einzelne INSERT-Statements.

Es geht aber auch eleganter.
Weiterlesen →

Es gab SQL vor Window Functions und es gibt SQL nach Window Functions

… so fängt ein lesenwertes Tutorial für dieses Thema an, das beim Einstieg erst einmal recht sperrig ist.

Wenn man sich da aber etwas durchwühlt, dann merkt man wie genial das ganze ist – CTE+Window-Function=Pr0n. Bis man aber dahin kommt, braucht’s einfach erst einmal ein bisschen mehr Brainfuck als im Alltag oft möglich ist. Darum will ich hier mal den allersimpelsten Fall zeigen, weitermachen kann man dann eben z.B. mit dem obengenannten Artikel.

Ich baue eine Suchfunktion für unsere Produktdatenbank. Schnell muss es sein, Prefix-Suche muss unterstützt werden, es wird natürlich paginiert. Also brauche ich nicht nur die aktuell dargestellten Produkte, sondern auch immer eine Gesamtzahl.
Weiterlesen →

Mag vielleicht trivial sein, aber ich bin gestern wieder über einen relativ häufigen Optimierungsfall gestolpert, der zumindest in der Entwicklungsphase nicht immer so einfach zu Tage tritt:

SELECT  a.article_id
      , a.headline
      , a.images
FROM babel_videogameszone.article a
   , public.MAIN_DVD b
WHERE a.entity_id = b.id
AND   a.article_type_id in (1,2,5,6,34,38,41)
AND             checkRegex(a.images,'µ.*?µ')
ORDER BY        a.published desc
LIMIT   4

SELECT a.article_id

, a.headline

, a.images

FROM babel_videogameszone.article a

, public.MAIN_DVD b

WHERE a.entity_id = b.id

AND a.article_type_id in (1,2,5,6,34,38,41)

AND checkRegex(a.images,'µ.*?µ')

ORDER BY a.published desc

LIMIT 4

Das Query läuft ca. 1,3 Sekunden – nicht gut. Was passiert hier?
Weiterlesen →

Jeder Round-Trip zur Datenbank kostet Zeit und Ressourcen. Oftmals hat man aber das Problem, dass man zu einem Datum noch eine unspezifizierte Anzahl von Relationen braucht. Meistens holt man die dann in einem Loop in der Applikation, denn üblicherweise bekommt man in den Ergebnisfeldern eines SQL-Queries nur flache Daten zurück. Mit PostgreSQL und JSON in der Datenbank geht das in manchen Fällen aber eleganter – und man kann ein gutes Dutzend Queries auf genau eine schnelle Abfrage reduzieren.
Weiterlesen →

DevBox

Developer-Blog der Computec-Entwickler

Archiv der Kategorie: Datenbanken

PostgreSQL: Sortierung nach Listenreihenfolge

PostgreSQL: Neues Index-Bloat-Query

Link

PostgreSQL: stats_temp_directory auf RAMDisk

Link

Effektiveres Multi-Insert mit unnest und CTE

Window Function Pr0n – und wann man’s besser ohne macht

SQL: Cardinality is a bitch

Zu faul zum mehrmals Laufen: Komplexes JSON aus PostgreSQL holen