CREATE INDEX

CREATE INDEX CONCURRENTLY

Açıklaması şöyle

Creating an index is always a resource-intensive operation, so always use the “CONCURRENTLY” parameter so that the creation does not affect the performance of the index source table.

Örnek

Şöyle yaparız

CREATE INDEX CONCURRENTLY index_name ON table_name USING btree (column);

1. Index Tipleri

Index tipleri şöyle

- Balanced Tree (B-Tree)

- Hash

- Generalized Inverted Index (GIN) : GIN INDEX yazısına taşıdım

- Generalized Inverted Search Tree (GiST)

- Space partitioned GiST (SP-GiST)

- Block Range Indexes (BRIN)

Açıklaması şöyle

You should know your indexes, although 99% of the time, you’re going to use B-Tree indexes, there’s that 1% that can make a huge difference if used right.

Balanced Tree (B-Tree)

Balanced Tree (B-Tree) yazısına taşıdım

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.

text_pattern_ops 

Ne zaman fuzzy search kullanmak gerekir?

Eğer bu iki maddeden birine takılıyorsak pg_trgm Module - Pattern Matching Full Text Search yazısına bakınız. Açıklaması şöyle

The BTree index can only search smaller strings with either direct match or prefix/suffix match with slightly better performance. But in the real world, users often misspell words, and it gets pretty hard to search for them. This is where the fuzzy search capabilities of PostgreSQL come in. They can search strings with similar words and do not have the size limitations of BTree indexes.

1. Eğer Full Text Search Yapıyorsak Takılırız

Şöyle yaparız. Burada prefix/suffix arama yapılmıyor ve index kullanılsa da yavaş olduğu görülecektir.

SELECT
  DISTINCT(event_type)
FROM
  storm_events
WHERE
  event_type ILIKE '%winter%'

2. Eğer Index Yaratamıyorsak Takılırız

B-Tree index 'in büyüklüğü belli bir değeri aşamaz. Şöyle yaparız.

CREATE INDEX anchored_search ON storm_events (event_type text_pattern_ops)

Çıktı olarak şunu alırız

index row size 4144 exceeds btree version 4 maximum 2704 for index "..."

Kullanım 

Örnek - Text Alan + LIKE

Açıklaması şöyle.

The B-Tree index is one of the simplest yet commonly used indexes in the PostgreSQL world. But when it comes to text, it cannot handle searches.

Görmek için şöyle yaparız. Sequential Scan yaptığı görülebilir.

EXPLAIN ANALYZE SELECT
  event_type
FROM
  storm_events
WHERE
  event_type LIKE '%Storm'

Index'i yaratmak için pattern_ops kullanılmalı. Şöyle yaparız. 

CREATE INDEX anchored_search ON storm_events (event_type text_pattern_ops)

Örnek - Text Alan + REGEX

Şöyle yaparız. S veya H ile başlayan event_type'ları seçer.

SELECT
  DISTINCT(event_type)
FROM
  storm_events
WHERE
  event_type ~ '^(S|H)'

2. Partial Index

Partial Index yazısına taşıdım