Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE spelers (
  spelers_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, name text
);

-- ~ 10000 random names
INSERT INTO spelers(name)
SELECT string_agg(chr(c % 58 + 65), '')
FROM   generate_series (1, 100000) c
GROUP  BY (random() * 10000)::int;

SET random_page_cost = 1.1;  -- more realistic (favors index)
-- SET enable_seqscan = off;    -- for test with small table; forces index where possible

CREATE TABLE

INSERT 0 9998

SET

VACUUM ANALYZE spelers;
-- Searching for 'Ba' & 'Dr' instead of 'B' & 'D' to make it more selective (more realistic)

VACUUM

----- 1. B-tree index with default collation -----
-- none of the queries can use it!
SHOW lc_collate;
CREATE INDEX spelers_name_idx ON spelers (name);

lc_collate

en_GB.UTF-8

SHOW

CREATE INDEX

-- 1.1 LIKE OR LIKE
SELECT name FROM spelers WHERE name LIKE 'Ba%' OR name LIKE 'Dr%' ORDER BY 1;
EXPLAIN ANALYZE
SELECT name FROM spelers WHERE name LIKE 'Ba%' OR name LIKE 'Dr%' ORDER BY 1;

name

Bal[RfHRNx

Bawt_RNQ

BazqBf\Tvvpuey

DrefN]Gi]Fuhfpsr

DrhIWJstkg^YAW

Dr[leyA

DrVYaoW

SELECT 7

QUERY PLAN

Sort  (cost=210.37..210.63 rows=102 width=11) (actual time=1.580..1.581 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..206.97 rows=102 width=11) (actual time=0.625..1.571 rows=7 loops=1)

        Filter: ((name ~~ 'Ba%'::text) OR (name ~~ 'Dr%'::text))

        Rows Removed by Filter: 9991

Planning Time: 0.061 ms

Execution Time: 1.767 ms

EXPLAIN

-- 1.2 LIKE ANY
SELECT name FROM spelers WHERE name LIKE ANY ('{Ba%,Dr%}') ORDER BY 1;
EXPLAIN ANALYZE
SELECT name FROM spelers WHERE name LIKE ANY ('{Ba%,Dr%}') ORDER BY 1;

name

Bal[RfHRNx

Bawt_RNQ

BazqBf\Tvvpuey

DrefN]Gi]Fuhfpsr

DrhIWJstkg^YAW

Dr[leyA

DrVYaoW

SELECT 7

QUERY PLAN

Sort  (cost=185.38..185.63 rows=102 width=11) (actual time=1.620..1.621 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..181.98 rows=102 width=11) (actual time=0.611..1.611 rows=7 loops=1)

        Filter: (name ~~ ANY ('{Ba%,Dr%}'::text[]))

        Rows Removed by Filter: 9991

Planning Time: 0.059 ms

Execution Time: 1.634 ms

EXPLAIN

-- 1.3 ^@ OR ^@
SELECT name FROM spelers WHERE name ^@ 'Ba' OR name ^@ 'Dr' ORDER BY 1;
EXPLAIN ANALYZE
SELECT name FROM spelers WHERE name ^@ 'Ba' OR name ^@ 'Dr' ORDER BY 1;

name

Bal[RfHRNx

Bawt_RNQ

BazqBf\Tvvpuey

DrefN]Gi]Fuhfpsr

DrhIWJstkg^YAW

Dr[leyA

DrVYaoW

SELECT 7

QUERY PLAN

Sort  (cost=210.37..210.63 rows=102 width=11) (actual time=3.561..3.562 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..206.97 rows=102 width=11) (actual time=1.353..3.553 rows=7 loops=1)

        Filter: ((name ^@ 'Ba'::text) OR (name ^@ 'Dr'::text))

        Rows Removed by Filter: 9991

Planning Time: 0.093 ms

Execution Time: 3.573 ms

EXPLAIN

-- 1.4 ^@ ANY
SELECT name FROM spelers WHERE name ^@ ANY ('{Ba,Dr}') ORDER BY 1;
EXPLAIN ANALYZE
SELECT name FROM spelers WHERE name ^@ ANY ('{Ba,Dr}') ORDER BY 1;

name

Bal[RfHRNx

Bawt_RNQ

BazqBf\Tvvpuey

DrefN]Gi]Fuhfpsr

DrhIWJstkg^YAW

Dr[leyA

DrVYaoW

SELECT 7

QUERY PLAN

Sort  (cost=185.38..185.63 rows=102 width=11) (actual time=3.106..3.107 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..181.98 rows=102 width=11) (actual time=1.202..3.098 rows=7 loops=1)

        Filter: (name ^@ ANY ('{Ba,Dr}'::text[]))

        Rows Removed by Filter: 9991

Planning Time: 0.062 ms

Execution Time: 3.116 ms

EXPLAIN

-- 1.5 ~ with branches
SELECT name FROM spelers WHERE name ~ '^(Ba|Dr).*' ORDER BY 1;
EXPLAIN ANALYZE
SELECT name FROM spelers WHERE name ~ '^(Ba|Dr).*' ORDER BY 1;

name

Bal[RfHRNx

Bawt_RNQ

BazqBf\Tvvpuey

DrefN]Gi]Fuhfpsr

DrhIWJstkg^YAW

Dr[leyA

DrVYaoW

SELECT 7

QUERY PLAN

Sort  (cost=185.34..185.59 rows=101 width=11) (actual time=3.234..3.235 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..181.98 rows=101 width=11) (actual time=1.232..3.226 rows=7 loops=1)

        Filter: (name ~ '^(Ba|Dr).*'::text)

        Rows Removed by Filter: 9991

Planning Time: 0.099 ms

Execution Time: 3.246 ms

EXPLAIN

-- 1.6 ~ with character class
SELECT name FROM spelers WHERE name ~ '^[BD].*' ORDER BY 1;
EXPLAIN ANALYZE
SELECT name FROM spelers WHERE name ~ '^[BD].*' ORDER BY 1;

name

BA`cNLoD_Qz

BAjTOeD[f

Bal[RfHRNx

Bawt_RNQ

BAym\UkzEgkW

BazqBf\Tvvpuey

BBRzEtusiAk\uW

BBUeD

BBXF

BCJbMNiV

BcMP\nZQOy

BcpgFV

BcsGIXHfkwNHQlQyO

BDCAeUgMNEF^O

BdGexJOZpwbQZk

BdkPfhOOhO\

BdK`uAwTavGitU

BDmkeWVxL

B]Dp`_SZD_\DKH[

Bdr[eiM\Hdn

BDRwwe]P

BdTtrQpQhuwI

BeakQ

BEe_V]z

B[EgmZklcNUuYwye

BELNxzJ\Q

BePa\CX

BeRjvF[

BeW^]]v^wu`_^

BF_LbMlV

B\FnDZE

BfVFFfX

BfZGm[r[aqD

BgFarFEwiHX^RQ

BGGK\VyZNt

BGGtyhVcF

Bg^J[k

BGJRZ

Bgk^CHEGcs^T

BgmH\YAcx

BgOJVLvmP

BGSDcfC

BgyGmgQNgLKC

BhCkiUuEF

BHheBZpEEI

BhIR_E_Gbrq

BHlVUmciT

BHoIWlkfQPjTV

Bhotjvsa\JBaf`

BHtmWrHL^Gn

BhxSmdwGW

Biffuya^

BigaourST

Biv

BI\VXIv`H

BiZQtVOOa\Q

BJAju

BJBVZAX

BjgWiKQb

BjHXDdSiQ

BjleeE^k]]

BJnkmDGYMXC

BjPsis`

BJTky

BJx\AKJLMo^_ma

B_kA

BK\GIj

BKLOmRp

BklusQ`L

BKnmh\bgUM

BKnu]ngnTf

BkTXsqpE

BKx[w_D

BlESLFYn

BLFb_L

BLkQfSNiUjQ

BlOh

Blsbj^zO\zX]

BLUNVZSspN

BLyIjIlu[Y

BmA`SRfNCLrxal

BMjQwVLa[ZrN

BNaEUBB`r

BNBrs^SuiY

BNDRj

BnEggQFj

B^nEmP

B\NEsP

BNIUarDcZ\e

BNKGvpa

BnkKoo[[Kkkig__fzrgS

B^nQC\w`GR

BnreE[`u`PB_A

Bn^_TTXQ_N]o

BnW`VYVnyVcf

BoAeHJ[rWCX

Bod\Bd`YDxX

BoELOfxJu]m[q

BOHUuIvnmylx

BOim]^iEG

B_OkRV[VFSO

BOMYMYKTfV

BoWkt\fGO_

BoyHNHaG

B`peri^

BPH\cfW

BPIHzlrq_

BPLlJO

BPVf`Df

BPy[WZfrg

B]p[zKqfOyWWmnPko

BQFtjHcfsk]NJ

BqIPaL

B`QLArD

BrbBHppqnfyEQ

BrDYgohLhnz

BrItEeDCdPnvIVE

BRltyC`A]

BR_`[rS_Q\C

BRSflExxg`k

BSeIset\XtIne

BshHrduUGpk

BslChSM`Nt

Bs[p_^qS

BsundTisT

BtFDWngkpwh

BtNPMabQJw

Btocbu`^J]_D]Q

BTrEcJb]whN

BtXCpbteRZ[^sa

BTXrEpQvvTCp

BUbFt

BUUXuK`qiYn

BvIDZpoiA

B\VjRVbaqVMm

Bv\Ln^Nbmw

BvnauZ

BVPOmwRwW

B`VuLGYYmG

BVy_IVsIUdX

BwLhrgrsMhjaddB[O

Bw`nYna^dR

BwSxNZPLV

BWzU

BXA]bBLRHGC^i

BXEkn[vhk

B^xeLKlgNo

BXG]r`buGBy

BXGXEk[v^ERyn

BXkftQa

BxKRgYvT]Z

BXkrQ

BxlKq`EI\H

BxLSO_lRwcoeLDn_Jr

B\X]MXwRLBPP]c

BxtrNKcFhp

BXwLbXtQpc\j

BX[yZxuj

BYfUhtHcP

BYuMuKK

BZBYRgghMTJhl

BZ\glZvZBvQp

BZmv_xTrokdcU

BzPJfAou

Da`gpDpOGxFD

DANzA\m

DaOZBF_

DAr_TXUMjbKQZPB

DAWwOHeTR

DBb[\d^\VF

DbBEh^cGEHE

DBkby^cD

D]`BkRZ[gRroo

DBtG_hk

DB`uJ`LsKh

DbXVM\cs

DCFqQy[

DchZhvas]xz

DCJ^bCyX

Dcjdm

DCJQc_hhThAXz`

DClF`GvIEYKKbqSO

DCmQR

D[csPYvpv

DcuMbppcOmEG]

DDi`PtO

DdsOpNEecvuL_d

DdsynN

D^dURBdGPxSa

D`eanSqaEe

DEbylAt

DEDpYpQhJiq

De`edqwhsPcO

Degdu]aCT

DeI[myVKB

DEIo[ZW\

DExAK\mZ

D[eXrHirQzF

DeXxr^wBPW_Rj

DEyuuyxfCqf

DFCTHfeeQUA

DfGeQvk_A

DF\lsaXuj]

DfVNFWPEF^PFIPe

DGbWRA_XpGWcs

DgeSueUSsf_Ga

DgJH

DglOgr_wL

DGlWlnyBo

DgmtZDIqqkXNn

DGOIAswl[vRn

DhEoP^Y[_

D_HFUivSkYM

Dhoifp

DhPcrYTEjIGiA

DHzISgRLBVGzbut

DI^ecjwEZ

Dieipv_J`UJ

DIEmqFcSmFZrG[L^

DINvkSUFL

DIOw_zD]^KnF

DI]yIMm

DJdje`tgXNH]\

DjguaO

DjMEnHp\RzRo[]

DjXQNubu]H^bXb^R

DJyUPr^vK

DK\DBtqvr_sEy[uZ[

DkHbA^i

DkLymTkNCrAiLd

DkOP]zbm

DKpa

DKPqsg^iabtNja

Dkr^a

DksybTwAE

D^LamJRK

Dl\ebYUUJ

DlIfxVc

DLmw_

DlnGBr

DlnL[laUjARM

Dlw]yRJin

DmCCj[\wyANkm

DM]GTA

DMHeddlyjn]m

Dml\ELWftKpQTP

DmLJnJiXLlOMo

D]MPfrKIsJh

Dmvuyit_uCjSCUsyjA

DmW^BrMMdQQmH\Xia

DmWwdWUZ

DMzwaiB

D]n[C`^NR

Dn]cs

DNGYfGMCJTrol

DN^kcuVZFU

DNQAJGtzVQe

DnQsCCH\

DntNXDONr

DnWmbnSRkIcQ

DnycQgzU`q

DNYiZixw

D\ObSjNzvqUE

DOIAKz

DOTgisuhK

DOUHEptw

DPAldLlWQ[wI

DPDro]NX[S

DPhZhwtve]

DpniIx

Dp_qp`at^_j

D`PtHzC

DqdXIs

DQ_HRwcohgM

DQLUCacuj[RB

D[rd[ml``F

DrefN]Gi]Fuhfpsr

DRFUpt

DrhIWJstkg^YAW

DRIqSVEKW

DRKjPXOZn

Dr[leyA

DRqApCIl`LI`C

DR\umEhqRZ

DrVYaoW

DSGLgB`A

DSirR`XG

DSI`yCHk

DSmd\`wFgzs

DsM]_KkYUJ

Ds]^mwxDoUdlzM

DSp_XK[iCUAdU_

DssnMAbjQ

DsyoVrQ

DtakoC

D\tJ[GUyUJo

D_tkHoGhC

DtLXOfw

DT\oCNH]QY

DtQcMVuie`

DTsISLGfV_

DtxTaxVGbMV`\fNmxI

DtYXU_Nf

DUagBuZ[rn

DuGeqY

DuiIr`zvLwR

DUM\xASkoQA

DU^UXgVqYVEwkj

DVPzzva

Dvx\w]QisFi

DwddWaXLqYU

DwFzH

DwIGK`eBOq^

DwJEIese

DwnL\YhC

D\wrDGh^cxleVA][

D_WspBbID

DWudULyJaua`o

DXCSMrG

DX[CWIAkZ^

DXnuGcd

DXRa`]Z

DXSjYg

D\Xuvktt`eX

DXvil

DXvzdauTMLCR^lO

DxxBUW

Dx_XCzK^WHmX

DXYl

DYBpwUbMxuYZA

DycZ]FZFj^

DYdvcqGV_

Dy[EROcrzH

DyEtPJakwGgI]a

DyNlto^j[Vq

DYopAAjn

DY_ScWjGOHHzU\fa

DYWxfkPAjzm

DYXQDD

Dz

DZewU\xzP^_p

DzIQAysaEiH

DzpVNJ^MuR

DZrp]aGki

DZ_Wvx

SELECT 345

QUERY PLAN

Sort  (cost=199.46..200.47 rows=404 width=11) (actual time=3.424..3.441 rows=345 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 43kB

  ->  Seq Scan on spelers  (cost=0.00..181.98 rows=404 width=11) (actual time=0.035..3.065 rows=345 loops=1)

        Filter: (name ~ '^[BD].*'::text)

        Rows Removed by Filter: 9653

Planning Time: 0.067 ms

Execution Time: 3.463 ms

EXPLAIN

-- 1.7 ~>=~ AND ~<~  -- fancy internal operators
SELECT name FROM spelers WHERE name ~>=~ 'Ba' AND name ~<~ 'Bb' OR name ~>=~ 'Dr' AND name ~<~ 'Ds' ORDER BY 1;
EXPLAIN ANALYZE
SELECT name FROM spelers WHERE name ~>=~ 'Ba' AND name ~<~ 'Bb' OR name ~>=~ 'Dr' AND name ~<~ 'Ds' ORDER BY 1;

name

Bal[RfHRNx

Bawt_RNQ

BazqBf\Tvvpuey

DrefN]Gi]Fuhfpsr

DrhIWJstkg^YAW

Dr[leyA

DrVYaoW

SELECT 7

QUERY PLAN

Sort  (cost=260.24..260.49 rows=99 width=11) (actual time=1.708..1.709 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..256.96 rows=99 width=11) (actual time=0.648..1.700 rows=7 loops=1)

        Filter: (((name ~>=~ 'Ba'::text) AND (name ~<~ 'Bb'::text)) OR ((name ~>=~ 'Dr'::text) AND (name ~<~ 'Ds'::text)))

        Rows Removed by Filter: 9991

Planning Time: 0.060 ms

Execution Time: 1.721 ms

EXPLAIN

----- 2. B-tree index using COLLATE "C" -----
DROP INDEX spelers_name_idx;
CREATE INDEX spelers_name_c_idx ON spelers (name COLLATE "C");

DROP INDEX

CREATE INDEX

-- 2.1 LIKE OR LIKE
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name LIKE 'Ba%' OR name LIKE 'Dr%' ORDER BY 1;

QUERY PLAN

Sort  (cost=61.90..62.15 rows=102 width=11) (actual time=0.121..0.124 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Bitmap Heap Scan on spelers  (cost=3.82..58.50 rows=102 width=11) (actual time=0.094..0.106 rows=7 loops=1)

        Recheck Cond: ((name ~~ 'Ba%'::text) OR (name ~~ 'Dr%'::text))

        Filter: ((name ~~ 'Ba%'::text) OR (name ~~ 'Dr%'::text))

        Heap Blocks: exact=7

        ->  BitmapOr  (cost=3.82..3.82 rows=99 width=0) (actual time=0.076..0.077 rows=0 loops=1)

              ->  Bitmap Index Scan on spelers_name_c_idx  (cost=0.00..2.38 rows=99 width=0) (actual time=0.056..0.056 rows=3 loops=1)

                    Index Cond: ((name >= 'Ba'::text) AND (name < 'Bb'::text))

              ->  Bitmap Index Scan on spelers_name_c_idx  (cost=0.00..1.40 rows=1 width=0) (actual time=0.019..0.019 rows=4 loops=1)

                    Index Cond: ((name >= 'Dr'::text) AND (name < 'Ds'::text))

Planning Time: 0.362 ms

Execution Time: 0.167 ms

EXPLAIN

-- 2.2 LIKE ANY
-- doesn't use index! Postgres can't (currently) verify all array elements are left-anchored patterns
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name LIKE ANY ('{Ba%,Dr%}') ORDER BY 1;

QUERY PLAN

Sort  (cost=185.38..185.63 rows=102 width=11) (actual time=1.736..1.737 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..181.98 rows=102 width=11) (actual time=0.678..1.726 rows=7 loops=1)

        Filter: (name ~~ ANY ('{Ba%,Dr%}'::text[]))

        Rows Removed by Filter: 9991

Planning Time: 0.064 ms

Execution Time: 1.749 ms

EXPLAIN

-- 2.3 ^@ OR ^@  -- requires Postgres 15 to use B-tree index!
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name ^@ 'Ba' OR name ^@ 'Dr' ORDER BY 1;

QUERY PLAN

Sort  (cost=61.90..62.15 rows=102 width=11) (actual time=0.034..0.035 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Bitmap Heap Scan on spelers  (cost=3.82..58.50 rows=102 width=11) (actual time=0.018..0.026 rows=7 loops=1)

        Recheck Cond: ((name ^@ 'Ba'::text) OR (name ^@ 'Dr'::text))

        Filter: ((name ^@ 'Ba'::text) OR (name ^@ 'Dr'::text))

        Heap Blocks: exact=7

        ->  BitmapOr  (cost=3.82..3.82 rows=99 width=0) (actual time=0.011..0.012 rows=0 loops=1)

              ->  Bitmap Index Scan on spelers_name_c_idx  (cost=0.00..2.38 rows=99 width=0) (actual time=0.008..0.008 rows=3 loops=1)

                    Index Cond: ((name >= 'Ba'::text) AND (name < 'Bb'::text))

              ->  Bitmap Index Scan on spelers_name_c_idx  (cost=0.00..1.40 rows=1 width=0) (actual time=0.003..0.003 rows=4 loops=1)

                    Index Cond: ((name >= 'Dr'::text) AND (name < 'Ds'::text))

Planning Time: 0.149 ms

Execution Time: 0.062 ms

EXPLAIN

-- 2.4 ^@ ANY 
-- also doesn't use index (might be improved?!)
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name ^@ ANY ('{Ba,Dr}') ORDER BY 1;

QUERY PLAN

Sort  (cost=185.38..185.63 rows=102 width=11) (actual time=3.728..3.729 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..181.98 rows=102 width=11) (actual time=1.440..3.717 rows=7 loops=1)

        Filter: (name ^@ ANY ('{Ba,Dr}'::text[]))

        Rows Removed by Filter: 9991

Planning Time: 0.078 ms

Execution Time: 3.741 ms

EXPLAIN

-- 2.5 ~ with branches
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name ~ '^(Ba|Dr).*' ORDER BY 1;

QUERY PLAN

Sort  (cost=185.34..185.59 rows=101 width=11) (actual time=3.241..3.242 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..181.98 rows=101 width=11) (actual time=1.251..3.232 rows=7 loops=1)

        Filter: (name ~ '^(Ba|Dr).*'::text)

        Rows Removed by Filter: 9991

Planning Time: 0.079 ms

Execution Time: 3.252 ms

EXPLAIN

-- 2.6 ~ with character class
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name ~ '^[BD].*' ORDER BY 1; -- only works with orig. 1-letter example

QUERY PLAN

Sort  (cost=199.46..200.47 rows=404 width=11) (actual time=3.471..3.488 rows=345 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 43kB

  ->  Seq Scan on spelers  (cost=0.00..181.98 rows=404 width=11) (actual time=0.036..3.118 rows=345 loops=1)

        Filter: (name ~ '^[BD].*'::text)

        Rows Removed by Filter: 9653

Planning Time: 0.070 ms

Execution Time: 3.510 ms

EXPLAIN

-- 2.7 ~>=~ AND ~<~
-- not using this B-tree index, either
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name ~>=~ 'Ba' AND name ~<~ 'Bb' OR name ~>=~ 'Dr' AND name ~<~ 'Ds' ORDER BY 1;

QUERY PLAN

Sort  (cost=260.24..260.49 rows=99 width=11) (actual time=1.678..1.679 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..256.96 rows=99 width=11) (actual time=0.645..1.670 rows=7 loops=1)

        Filter: (((name ~>=~ 'Ba'::text) AND (name ~<~ 'Bb'::text)) OR ((name ~>=~ 'Dr'::text) AND (name ~<~ 'Ds'::text)))

        Rows Removed by Filter: 9991

Planning Time: 0.070 ms

Execution Time: 1.692 ms

EXPLAIN

----- 3. SP-GiST index -----
DROP INDEX spelers_name_c_idx;
CREATE INDEX spelers_name_spgist_idx ON spelers USING spgist (name);

DROP INDEX

CREATE INDEX

-- 3.1 LIKE OR LIKE
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name LIKE 'Ba%' OR name LIKE 'Dr%' ORDER BY 1;

QUERY PLAN

Sort  (cost=62.39..62.65 rows=102 width=11) (actual time=0.252..0.254 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Bitmap Heap Scan on spelers  (cost=3.32..58.99 rows=102 width=11) (actual time=0.206..0.238 rows=7 loops=1)

        Recheck Cond: ((name ~~ 'Ba%'::text) OR (name ~~ 'Dr%'::text))

        Filter: ((name ~~ 'Ba%'::text) OR (name ~~ 'Dr%'::text))

        Heap Blocks: exact=7

        ->  BitmapOr  (cost=3.32..3.32 rows=102 width=0) (actual time=0.196..0.196 rows=0 loops=1)

              ->  Bitmap Index Scan on spelers_name_spgist_idx  (cost=0.00..2.01 rows=101 width=0) (actual time=0.189..0.190 rows=3 loops=1)

                    Index Cond: (name ^@ 'Ba'::text)

              ->  Bitmap Index Scan on spelers_name_spgist_idx  (cost=0.00..1.26 rows=1 width=0) (actual time=0.005..0.005 rows=4 loops=1)

                    Index Cond: (name ^@ 'Dr'::text)

Planning Time: 0.247 ms

Execution Time: 0.310 ms

EXPLAIN

-- 3.2 LIKE ANY
-- doesn't use SP-GiST index either!
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name LIKE ANY ('{Ba%,Dr%}') ORDER BY 1;

QUERY PLAN

Sort  (cost=185.38..185.63 rows=102 width=11) (actual time=1.454..1.455 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..181.98 rows=102 width=11) (actual time=0.577..1.446 rows=7 loops=1)

        Filter: (name ~~ ANY ('{Ba%,Dr%}'::text[]))

        Rows Removed by Filter: 9991

Planning Time: 0.053 ms

Execution Time: 1.465 ms

EXPLAIN

-- 3.3 ^@ OR ^@
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name ^@ 'Ba' OR name ^@ 'Dr' ORDER BY 1;

QUERY PLAN

Sort  (cost=62.39..62.65 rows=102 width=11) (actual time=0.028..0.029 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Bitmap Heap Scan on spelers  (cost=3.32..58.99 rows=102 width=11) (actual time=0.016..0.021 rows=7 loops=1)

        Recheck Cond: ((name ^@ 'Ba'::text) OR (name ^@ 'Dr'::text))

        Heap Blocks: exact=7

        ->  BitmapOr  (cost=3.32..3.32 rows=102 width=0) (actual time=0.012..0.013 rows=0 loops=1)

              ->  Bitmap Index Scan on spelers_name_spgist_idx  (cost=0.00..2.01 rows=101 width=0) (actual time=0.008..0.008 rows=3 loops=1)

                    Index Cond: (name ^@ 'Ba'::text)

              ->  Bitmap Index Scan on spelers_name_spgist_idx  (cost=0.00..1.26 rows=1 width=0) (actual time=0.004..0.004 rows=4 loops=1)

                    Index Cond: (name ^@ 'Dr'::text)

Planning Time: 0.075 ms

Execution Time: 0.052 ms

EXPLAIN

-- 3.4 ^@ ANY
-- can use SP-GiST index - as opposed to LIKE ANY
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name ^@ ANY ('{Ba,Dr}') ORDER BY 1;

QUERY PLAN

Sort  (cost=62.11..62.37 rows=102 width=11) (actual time=0.029..0.030 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Bitmap Heap Scan on spelers  (cost=3.29..58.71 rows=102 width=11) (actual time=0.017..0.022 rows=7 loops=1)

        Recheck Cond: (name ^@ ANY ('{Ba,Dr}'::text[]))

        Heap Blocks: exact=7

        ->  Bitmap Index Scan on spelers_name_spgist_idx  (cost=0.00..3.27 rows=102 width=0) (actual time=0.013..0.013 rows=7 loops=1)

              Index Cond: (name ^@ ANY ('{Ba,Dr}'::text[]))

Planning Time: 0.069 ms

Execution Time: 0.049 ms

EXPLAIN

-- 3.5 ~ with branches
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name ~ '^(Ba|Dr).*' ORDER BY 1;

QUERY PLAN

Sort  (cost=185.34..185.59 rows=101 width=11) (actual time=3.238..3.239 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Seq Scan on spelers  (cost=0.00..181.98 rows=101 width=11) (actual time=1.241..3.229 rows=7 loops=1)

        Filter: (name ~ '^(Ba|Dr).*'::text)

        Rows Removed by Filter: 9991

Planning Time: 0.108 ms

Execution Time: 3.251 ms

EXPLAIN

-- 3.6 ~ with character class
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name ~ '^[BD].*' ORDER BY 1; -- only works with orig. 1-letter example

QUERY PLAN

Sort  (cost=199.46..200.47 rows=404 width=11) (actual time=3.456..3.474 rows=345 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 43kB

  ->  Seq Scan on spelers  (cost=0.00..181.98 rows=404 width=11) (actual time=0.037..3.091 rows=345 loops=1)

        Filter: (name ~ '^[BD].*'::text)

        Rows Removed by Filter: 9653

Planning Time: 0.079 ms

Execution Time: 3.496 ms

EXPLAIN

-- 3.7 ~>=~ AND ~<~
-- can use SP-GiST index
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name ~>=~ 'Ba' AND name ~<~ 'Bb' OR name ~>=~ 'Dr' AND name ~<~ 'Ds' ORDER BY 1;

QUERY PLAN

Sort  (cost=62.00..62.25 rows=99 width=11) (actual time=0.034..0.035 rows=7 loops=1)

  Sort Key: name

  Sort Method: quicksort  Memory: 25kB

  ->  Bitmap Heap Scan on spelers  (cost=3.55..58.72 rows=99 width=11) (actual time=0.021..0.027 rows=7 loops=1)

        Recheck Cond: (((name ~>=~ 'Ba'::text) AND (name ~<~ 'Bb'::text)) OR ((name ~>=~ 'Dr'::text) AND (name ~<~ 'Ds'::text)))

        Heap Blocks: exact=7

        ->  BitmapOr  (cost=3.55..3.55 rows=99 width=0) (actual time=0.017..0.018 rows=0 loops=1)

              ->  Bitmap Index Scan on spelers_name_spgist_idx  (cost=0.00..2.24 rows=99 width=0) (actual time=0.010..0.010 rows=3 loops=1)

                    Index Cond: ((name ~>=~ 'Ba'::text) AND (name ~<~ 'Bb'::text))

              ->  Bitmap Index Scan on spelers_name_spgist_idx  (cost=0.00..1.26 rows=1 width=0) (actual time=0.006..0.007 rows=4 loops=1)

                    Index Cond: ((name ~>=~ 'Dr'::text) AND (name ~<~ 'Ds'::text))

Planning Time: 0.095 ms

Execution Time: 0.058 ms

EXPLAIN

-- SIMILAR TO (yuk!) is rewritten
EXPLAIN ANALYZE SELECT name FROM spelers WHERE name SIMILAR TO 'Ba%' OR name SIMILAR TO 'Dr%';

QUERY PLAN

Bitmap Heap Scan on spelers  (cost=3.32..58.99 rows=102 width=11) (actual time=0.019..0.031 rows=7 loops=1)

  Recheck Cond: ((name ~ '^(?:Ba.*)$'::text) OR (name ~ '^(?:Dr.*)$'::text))

  Filter: ((name ~ '^(?:Ba.*)$'::text) OR (name ~ '^(?:Dr.*)$'::text))

  Heap Blocks: exact=7

  ->  BitmapOr  (cost=3.32..3.32 rows=102 width=0) (actual time=0.012..0.012 rows=0 loops=1)

        ->  Bitmap Index Scan on spelers_name_spgist_idx  (cost=0.00..2.01 rows=101 width=0) (actual time=0.008..0.008 rows=3 loops=1)

              Index Cond: (name ^@ 'Ba'::text)

        ->  Bitmap Index Scan on spelers_name_spgist_idx  (cost=0.00..1.26 rows=1 width=0) (actual time=0.004..0.004 rows=4 loops=1)

              Index Cond: (name ^@ 'Dr'::text)

Planning Time: 0.201 ms

Execution Time: 0.053 ms

EXPLAIN