1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
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; |
Schlagwort-Archive: Performance
PostgreSQL: stats_temp_directory auf RAMDisk
Link
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.
SQL: Cardinality is a bitch
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:
1 2 3 4 5 6 7 8 9 10 |
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
Zu faul zum mehrmals Laufen: Komplexes JSON aus PostgreSQL holen
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