CREATE INDEX - Partial Index

Giriş

Açıklaması şöyle

If you know that your query will always ignore columns with defined values, you can make use of a partial index to save space and make it faster.

Örnek - Equals

Şöyle yaparız

CREATE INDEX app_user_address_is_main ON app_user_address (user_id)
    WHERE is_main = TRUE;

Açıklaması şöyle

In this query, you’ll be creating an index that only indexes rows if the column is_main equals TRUE.

Örnek - Equals

Şöyle yaparız

CREATE INDEX balances_total_balance_nan_idx ON balances_snapshots ((true))
WHERE total_balance = 'NaN';

Örnek - B- Tree Index + Equals

Şöyle yaparız

CREATE INDEX id_btree ON sample_data USING BTREE(id) WHERE id = 200000;

Örnek - Not Equals

Şöyle yaparız

CREATE INDEX ON f (parent_sha256) WHERE parent_sha256 <> sha256;

CREATE UNIQUE INDEX

Burada CREATE INDEX yerine CREATE UNIQUE index kullanılıyor

Örnek

Şöyle yaparız

CREATE UNIQUE INDEX ON users (user_id)

Örnek

Şöyle yaparız. 

CREATE UNIQUE INDEX event_unique_ower_uqx    -- index name
    ON mytable (event_id) 
    WHERE (status = 'owner') ;

JDBC PGSimpleDataSource Sınıfı

Giriş

Şu satırı dahil ederiz

import org.postgresql.ds.PGSimpleDataSource;

PGXADataSource yazısına bakılabilir

Açıklaması şöyle

The PGSimpleDataSource, which is the default DataSource implementation in PostgreSQL, uses the underlying Driver to acquire a physical connection which establishes a TCP connection to the database server.
And when the close method is called on the JDBC Connection object, the underlying Socket and TCP connection are terminated.

Maven

Şöyle yaparız.

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>9.4-1200-jdbc4</version>
</dependency>

constructor

Örnek

şöyle yaparız

PGSimpleDataSource dataSource = new PGSimpleDataSource();
dataSource.setDatabaseName("high_performance_java_persistence");
dataSource.setServerName("localhost");
dataSource.setUser("postgres");
dataSource.setPassword("admin");

Örnek

Şöyle yaparız

import org.postgresql.ds.common.BaseDataSource;

PostgreSQLContainer container = ...;

BaseDataSource dataSource = new PGSimpleDataSource();
dataSource.setUrl(container.getJdbcUrl());
dataSource.setUser(container.getUsername());
dataSource.setPassword(container.getPassword());
dataSource.setDatabaseName(container.getDatabaseName());

Örnek

Şöyle yaparız

import org.postgresql.ds.PGSimpleDataSource;
import org.postgresql.ds.common.BaseDataSource;
import org.testcontainers.containers.PostgreSQLContainer;

import javax.sql.CommonDataSource;
import javax.sql.DataSource;

// PGSimpleDataSource is both javax.sql.DataSource and javax.sql.CommonDataSource
CommonDataSource createDataSource() {
  PostgreSQLContainer postgreSQLContainer = ...;

  BaseDataSource dataSource = new PGSimpleDataSource();
  dataSource.setUrl(postgreSQLContainer.getJdbcUrl());
  dataSource.setUser(postgreSQLContainer.getUsername());
  dataSource.setPassword(postgreSQLContainer.getPassword());
  dataSource.setDatabaseName(postgreSQLContainer.getDatabaseName());

  return dataSource;
}

CREATE INDEX - Balanced Tree (B-Tree)

Balanced Tree (B-Tree)

Açıklaması şöyle

The B-Tree index type uses a balanced tree structure to speed up equality and range queries on columns of all data types. Since B-Tree index entries are sorted, they are sometimes used to retrieve table rows in order, thereby avoiding manually sorting them after retrieval.

This is the default index type and also the most commonly used.

B+ Tree vs B Tree

Açıklaması şöyle. Yani B Tree dense bile aslında PostgreSQL B+ Tree kullanıyor

Postgresql has an index with the type “B-tree” which is implemented based on B-tree. More specifically, on one of its variations — B-tree+ (keys to rows are stored only in leaf nodes).

Açıklaması şöyle

The most common index used in a relational database system is the B+ Tree one. Like the B-Tree index, the B+ Tree is a self-balanced ordered tree data structure.

Both the B-Tree and the B+Tree start from a Root node and may have Internal Nodes and Leaf Nodes. However, unlike the B-Tree, the B+ Tree stores all the keys in the leaf nodes, and the adjacent Leaf nodes are linked via pointers, which simplifies range scans.

Without an index, whenever we are looking for a given column value, we’d need to scan all the table records and compare each column value against the provided one. The larger the table, the more pages will have to be scanned in order to find all the matching records.

On the other hand, if the column value is highly selective (e.g., a small number of records match that column value), using a B+Tree index allows us to locate a column value much faster since fewer pages will be needed to be scanned.

Page Büyüklüğü

Açıklaması şöyle. 8 KB büyüklüğünde. Bu da büyük bir M sayısının oluşmasına sebep oluyor

As mentioned in the post, each node is located on one block (page in terms of PostgreSQL, usually 8KB). In practice, a node contains a lot (M = hundreds) keys. As a result, the depth (number of levels) of the B-Tree is quite small, about 4–5 for very large tables. Just imagine, B-tree allows us to get a needed key by using only 4–5 disk read operations.

Şeklen şöyle. Burada M = 3 ve row bilgisi son yaprak ta saklanıyor.

Search bu equality şeklen şöyle

Search by range şeklen şöyle. N>=11 olan tüm değerleri arıyoruz. Arama hep sağa doğru gidiyor

Kullanım

Örnek
Şöyle yaparız.

CREATE INDEX idx ON adsets (date, platform);

Örnek
Şöyle yaparız. Burada USING BTREE ile indeksin tipi belirtiliyor. Normalde gerekli değil

CREATE INDEX id_idx ON fake_data USING BTREE(id);

Bu index aynı zamanda pattern_ops ile de kullanılabilir. 

- text_pattern_ops : text için

- varchar_pattern_ops : varchar için

- bpchar_pattern_ops : char için

değerlerini alabilir.

PostgreSQL'de Primary Index Yoktur

MySQL'de secondary index satırın tüm verisini içeren bir primary index'e işaret eder. PostgreSQL böyle değil. Direkt heap'teki bir yere işaret eder. Şeklen şöyle. 

SQL DISTINCT

Giriş

Açıklaması şöyle

- SQL Distinct statement returns distinct values for a given column.

- SQL Distinct returns distinct combination of columns when used with multiple columns.

Örnek

Şeklen şöyle. Burada DISTINCT iki tane sütunla kullanıldığı için bunların kombinasyonunu aldığı görülebilir.

Örnek

Elimizde şöyle bir tablo olsun

Emp_id Dept_id Job_id
1      24      117
2      24      117
3      24      118
4      25      117

Bu tabloyu şöyle sorgulayalım

SELECT Dept_id, Job_id FROM Employees

Şu sonucu alırız.

24 117
24 117
24 118
25 117

24 ile başlayan satırların çift olduğu görülebilir. Bunlardan kurtulmak için şöyle yaparız.

SELECT DISTINCT Dept_id, Job_id FROM Employees

Bu durumda şu sonucu alırız.

24 117
24 118
25 117




INNER JOIN USING

Giriş

Açıklaması şöyle

The USING clause works for Oracle, PostgreSQL, MySQL, and MariaDB. SQL Server doesn’t support the USING clause, so you need to use the ON clause instead.

Örnek

Şöyle yaparız

SELECT * FROM post
  INNER JOIN post_comment USING(post_id)
  ORDER BY post_id, post_comment_id

Çıktısı şöyle

| post_id | title     | post_comment_id | review    |
|---------|-----------|-----------------|-----------|
| 1       | Java      | 1               | Good      |
| 1       | Java      | 2               | Excellent |
| 2       | Hibernate | 3               | Awesome   |

Eğer INNER JOIN ON kullansaydık şöyle yaparız

SELECT * FROM post
  INNER JOIN post_comment ON post.post_id = post_comment.post_id
  ORDER BY post.post_id, post_comment_id

Çıktısı şöyle. İki tane post_id sütunu geldi. Birisi pos tablosundan, diğeri de post_comment tablosundan

| post_id | title     | post_comment_id | review    | post_id |
|---------|-----------|-----------------|-----------|---------|
| 1       | Java      | 1               | Good      | 1       |
| 1       | Java      | 2               | Excellent | 1       |
| 2       | Hibernate | 3               | Awesome   | 2       |

SQL EXISTS ve NOT EXISTS

Giriş

EXIST (subquery) şeklinde kullanılır.

EXIST yerine COUNT(*) kullanılması çok verimsiz.

Örnek

Şu kod yanlış

SELECT COUNT(*) FROM actor a 
JOIN film_actor fa USING (actor_id)
WHERE a.last_name = 'WAHLBERG'

Doğrusu şöyle

SELECT EXISTS (
  SELECT * FROM actor a
  JOIN film_actor fa USING (actor_id)
  WHERE a.last_name = 'WAHLBERG'
)

Örnek

Şöyle yaparız. Burada student tablosu üzerinde yürünüyor. Her öğrencinin başka derslere ait bir sürü notu daha student_grade tablosunda var. EXIST kullandığımız için bir öğrencinin tüm notlarını dolaşırken eğer ders ismi Math ve notu 10 ise EXISTS hemen TRUE döner ve öğrenci için çalışan döngüden çıkarız. Dolayısıyla daha hızlı

SELECT id, first_name, last_name
FROM student
WHERE EXISTS (
    SELECT 1
    FROM student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade = 10 AND
        student_grade.class_name = 'Math'
)
ORDER BY id

SELECT id, first_name, last_name
FROM student
WHERE NOT EXSITS (
    SELECT 1
    FROM student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade < 9
)
ORDER BY id

Örnek

Şöyle yaparız. Burada student tablosu üzerinde yürünüyor. Her öğrencinin başka derslere ait bir sürü notu daha student_grade tablosunda var. NOT EXIST kullandığımız için bir öğrencinin tüm notlarını dolaşırken eğer ders ismi Math ve notu 9'dan az ise NOT EXISTS hemen FALSE döner ve öğrenci için çalışan döngüden çıkarız. Dolayısıyla daha hızlı

SELECT id, first_name, last_name
FROM student
WHERE NOT EXSITS (
    SELECT 1
    FROM student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade < 9
)
ORDER BY id