# information\_schema

Published articles for information\_schema.

This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles.

## Using jOOQ's Implicit Join From Within the JOIN .. ON Clause

DevFeed: [Using jOOQ's Implicit Join From Within the JOIN .. ON Clause](<https://devfeed.tech/articles/using-jooq-s-implicit-join-from-within-the-join-on-clause-28970.md>)

Original publisher: [Read original article](<https://blog.jooq.org/using-jooqs-implicit-join-from-within-the-join-on-clause/>)

Author: lukaseder

Published: 2022-09-13T07:29:59Z

Content type: tutorial

Language: en

Sources: [jOOQ](<https://devfeed.tech/sources/jooq.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [PostgreSQL](<https://devfeed.tech/topics/postgresql.md>)

Tags: [dictionary-views](<https://devfeed.tech/tags/dictionary-views.md>), [how-to](<https://devfeed.tech/tags/how-to.md>), [implicit-join](<https://devfeed.tech/tags/implicit-join.md>), [information-schema](<https://devfeed.tech/tags/information-schema.md>), [jooq](<https://devfeed.tech/tags/jooq.md>), [jooq-3-17](<https://devfeed.tech/tags/jooq-3-17.md>), [jooq-in-use](<https://devfeed.tech/tags/jooq-in-use.md>), [pg-catalog](<https://devfeed.tech/tags/pg-catalog.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [sql](<https://devfeed.tech/tags/sql.md>), [technical](<https://devfeed.tech/tags/technical.md>)

### AI overview

This tutorial explains how to use jOOQ's type-safe implicit JOINs from within an explicit JOIN ... ON clause. It uses PostgreSQL dictionary views and related tables as an example, and describes how jOOQ generates SQL while preserving operator precedence.

### Source excerpt

Starting with jOOQ 3.11, type safe implicit JOIN have been made available, and they've been enhanced to be supported also in DML statements in jOOQ 3.17. Today, I'd like to focus on a somewhat weird but really powerful use-case for implicit JOIN, when joining additional tables from within an explicit JOIN's ON clause. The use ... Continue reading Using jOOQ's Implicit Join From Within the JOIN .. ON Clause ->

## Postgres Indexing - A collection of indexing tips

DevFeed: [Postgres Indexing - A collection of indexing tips](<https://devfeed.tech/articles/postgres-indexing-a-collection-of-indexing-tips-41141.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2013/05/30/Postgres-Indexing-A-collection-of-indexing-tips/>)

Author: Map

Published: 2013-05-30T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [Databases](<https://devfeed.tech/topics/databases.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Heroku Postgres](<https://devfeed.tech/topics/heroku-postgres.md>), [JOIN](<https://devfeed.tech/topics/join.md>)

Tags: [heroku](<https://devfeed.tech/tags/heroku.md>), [heroku-postgres](<https://devfeed.tech/tags/heroku-postgres.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [indexing](<https://devfeed.tech/tags/indexing.md>), [information-schema](<https://devfeed.tech/tags/information-schema.md>), [join](<https://devfeed.tech/tags/join.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgres-performance](<https://devfeed.tech/tags/postgres-performance.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [query](<https://devfeed.tech/tags/query.md>), [schema](<https://devfeed.tech/tags/schema.md>), [sql](<https://devfeed.tech/tags/sql.md>), [stat](<https://devfeed.tech/tags/stat.md>), [tips](<https://devfeed.tech/tags/tips.md>)

### AI overview

A collection of PostgreSQL indexing tips covering unused-index analysis, indexing costs, and the trade-offs between composite and separate indexes. It includes SQL examples and Heroku tooling for examining index usage.

### Source excerpt

Even from intial reviews of my previous post on expression based indexes I received a lot of questions and feedback around many different parts of indexing in Postgres. Here's a mixed collection of valuable tips and guides around much of that. Unused Indexes In an earlier tweet I joked about some SQL that would generate the SQL to add an index to every column: # SELECT 'CREATE INDEX idx_' || table_name || '_' || column_name || ' ON ' || table_name || ' ("' || column_name || '");' FROM information_schema.columns; ?column? --------------------------------------------------------------------- CREATE INDEX idx_pg_proc_proname ON pg_proc ("proname"); CREATE INDEX idx_pg_proc_pronamespace ON pg_proc ("pronamespace"); CREATE INDEX idx_pg_proc_proowner ON pg_proc ("proowner"); The reasoning behind this is guessing whether an index will be helpful can be a bit hard within Postgres. So the easy solution is to add indexes to everything, then just observe if they're being used. Of course you want to add it to all tables/columns because you never know if core of Postgres may be missing some needed ones As included with the pg-extras plugin for Heroku you can run a query to show you all unused indexes. On Heroku simply install the plugin the run heroku pg:unused_indexes to show the size and number of times an index scan has been used. On a non Heroku Postgres database you can run: # SELECT schemaname || '.' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC; table | index | index_size | index_scans ---------------------+--------------------------------------------+------------+------------- public.grade_levels | index_placement_attempt

## MySQL - Proste metody optymalizacji

DevFeed: [MySQL - Proste metody optymalizacji](<https://devfeed.tech/articles/mysql-proste-metody-optymalizacji-27509.md>)

Original publisher: [Read original article](<https://gagor.pro/2011/12/mysql-proste-metody-optymalizacji/>)

Author: Tom

Published: 2011-12-29T00:00:00Z

Content type: tutorial

Language: pl

Sources: [Tomasz Gągor](<https://devfeed.tech/sources/tomasz-gagor.md>)

Topics: [MySQL](<https://devfeed.tech/topics/mysql.md>), [Cache](<https://devfeed.tech/topics/cache.md>)

Tags: [backup](<https://devfeed.tech/tags/backup.md>), [cache](<https://devfeed.tech/tags/cache.md>), [index](<https://devfeed.tech/tags/index.md>), [information-schema](<https://devfeed.tech/tags/information-schema.md>), [mariadb](<https://devfeed.tech/tags/mariadb.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [percona](<https://devfeed.tech/tags/percona.md>), [schema](<https://devfeed.tech/tags/schema.md>), [var](<https://devfeed.tech/tags/var.md>)

### AI overview

A Polish tutorial presents MySQL configuration adjustments for improving database performance, covering MyISAM key caching, InnoDB buffer pool and log file sizing, and the storage and compaction behavior of InnoDB data files.

### Source excerpt

Wcześniej czy później zawsze pojawia się potrzeba zoptymalizowania naszej bazy MySQL. Przedstawię kilka zmian w konfiguracji, które powinny zwiększyć wydajność w większości przypadków. MyISAM - key_buffer_size Najprostszą optymalizacją baz/tabel z mechanizmem MyISAM jest odpowiednie dobranie bufora na cache dla kluczy i indeksów (dane nigdy nie są cachowane). Poniższe zapytanie pozwala oszacować zalecany rozmiar cache'u: SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_key_buffer_size FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM (SELECT SUM(index_length) KBS1 FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) AA ) A, (SELECT 2 PowerOf1024) B; Wynik określa zalecany rozmiar bufora (parametr key_buffer_size w pliku /etc/mysql/my.cnf) dla bieżącego stanu bazy - warto ciut dodać na zapas. Na systemach 32 bitowych parametr key_buffer_size może przyjmować maksymalnie 4GB, na 64 bitowych maksymalnie 8GB.