By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE
INSERT 0 9999
SET
VACUUM
lc_collate |
---|
en_GB.UTF-8 |
SHOW
CREATE INDEX
name |
---|
BaNSCpGSM |
DrJEfYMJcrvs`nDILx |
Drmpxc]NM |
DrXmwqHtJO |
Drx]RaxC |
DrYJyOKuMqRTBz |
SELECT 6
QUERY PLAN |
---|
Sort (cost=206.99..207.00 rows=2 width=11) (actual time=1.349..1.350 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..206.98 rows=2 width=11) (actual time=0.162..1.342 rows=6 loops=1) |
Filter: ((name ~~ 'Ba%'::text) OR (name ~~ 'Dr%'::text)) |
Rows Removed by Filter: 9993 |
Planning Time: 0.052 ms |
Execution Time: 1.360 ms |
EXPLAIN
name |
---|
BaNSCpGSM |
DrJEfYMJcrvs`nDILx |
Drmpxc]NM |
DrXmwqHtJO |
Drx]RaxC |
DrYJyOKuMqRTBz |
SELECT 6
QUERY PLAN |
---|
Sort (cost=182.00..182.00 rows=2 width=11) (actual time=1.294..1.295 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..181.99 rows=2 width=11) (actual time=0.157..1.288 rows=6 loops=1) |
Filter: (name ~~ ANY ('{Ba%,Dr%}'::text[])) |
Rows Removed by Filter: 9993 |
Planning Time: 0.046 ms |
Execution Time: 1.304 ms |
EXPLAIN
name |
---|
BaNSCpGSM |
DrJEfYMJcrvs`nDILx |
Drmpxc]NM |
DrXmwqHtJO |
Drx]RaxC |
DrYJyOKuMqRTBz |
SELECT 6
QUERY PLAN |
---|
Sort (cost=206.99..207.00 rows=2 width=11) (actual time=2.807..2.807 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..206.98 rows=2 width=11) (actual time=0.342..2.800 rows=6 loops=1) |
Filter: ((name ^@ 'Ba'::text) OR (name ^@ 'Dr'::text)) |
Rows Removed by Filter: 9993 |
Planning Time: 0.074 ms |
Execution Time: 2.817 ms |
EXPLAIN
name |
---|
BaNSCpGSM |
DrJEfYMJcrvs`nDILx |
Drmpxc]NM |
DrXmwqHtJO |
Drx]RaxC |
DrYJyOKuMqRTBz |
SELECT 6
QUERY PLAN |
---|
Sort (cost=182.00..182.00 rows=2 width=11) (actual time=2.828..2.828 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..181.99 rows=2 width=11) (actual time=0.350..2.820 rows=6 loops=1) |
Filter: (name ^@ ANY ('{Ba,Dr}'::text[])) |
Rows Removed by Filter: 9993 |
Planning Time: 0.059 ms |
Execution Time: 2.837 ms |
EXPLAIN
name |
---|
BaNSCpGSM |
DrJEfYMJcrvs`nDILx |
Drmpxc]NM |
DrXmwqHtJO |
Drx]RaxC |
DrYJyOKuMqRTBz |
SELECT 6
QUERY PLAN |
---|
Sort (cost=182.00..182.00 rows=1 width=11) (actual time=2.915..2.916 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..181.99 rows=1 width=11) (actual time=0.375..2.907 rows=6 loops=1) |
Filter: (name ~ '^(Ba|Dr).*'::text) |
Rows Removed by Filter: 9993 |
Planning Time: 0.071 ms |
Execution Time: 2.925 ms |
EXPLAIN
name |
---|
B_AmFvKGX_anWc |
BaNSCpGSM |
BAPfy[e\ |
BAyLlf |
BbaxaDlJb |
BbEdsU[jhMl |
BBrPCxKPTB |
BbsVRNXPu |
BBtorTMcbHjUaG |
BBxo |
BbY`kWIbBG |
BCb[zrNKvJU |
BcCfaOI |
BCcWtz |
BCHPl |
BcnLxsdZ |
BCQ]BWD]fL`ZubhR |
B]cRBdgud |
BCRIijFlow |
BcTNQc |
BC^xalNJqq` |
BDb[MddBRiKA_q |
BDC[S_MlEbn |
BDNgdhmn\j]Nh |
BdpScN]L |
BeRAVC |
BESupYgb |
BfeAjYGAATEraT |
BFkn_BxV |
BFkV |
BfmSTZmvf |
BfpejqJ]`O |
BFWrpSpzgQmzkW |
BGBgMhE_ |
Bg\_dRPIMbYsC |
BGfVa |
BGplOfLB\r |
BGtRhjn |
B`g^Ub |
BgXMlxxNPxZ |
BHBaEqbyLfJ\ |
B_HBlLaMlBWj[X |
BH`DDc |
BhEsIxIgwIBe |
B_HHBHJ |
B_HjiFViADs |
BHMyEcsl |
B[hTGQhsWgpWm |
Bhv\Z_sIm` |
BhXQisv_[P]l |
B`hzwkIoijPqy |
B]IBINxQTBYvWVQb |
BicRQrmBnkCRo |
BIlD^Ll^ |
BIMwn |
BiQntLYz |
BIR\iLPchE |
Bis[jNIHVr |
BitdYZKq_ygs |
B_i\uex |
BIVWcBIn |
BJgAB[]SajD |
BJGiqnDLlB |
BjnvXWPUlspgIs |
BJQ_YbcqGTnop |
BJRUicy^NRlH |
BJWbCZn |
BJyr |
BKCKiL |
BkDsKuWwATTe |
BkrQk |
BKSiKCp |
BKUI |
BkylHyTV |
BKyrSJKi]D |
BlbYVKy`p^WmO |
B_lFbVj |
BlfrdPj |
BlIEaMH |
BL`SMKs |
BlVyu_I`lobyKZ |
BmfsOJyhfG |
B[MkpURJxcL |
BMqCGW_`UL |
BMuRwDMCztgao |
BMyKAkmjUMbD[X |
BnFaA^LeK |
BNqjAurgRLF |
BNSfa_X`Dgg |
Bn_Sk]jnJNS^_ |
BNyf_IOXz^nS |
BO\fmN]q`A[wuwDa |
BOiHiRUadTeh |
BoLX^\jh |
BOqYgV |
B`oRyYH |
BoTmf |
Bo\WxH^xB |
BOxxcA |
BOZSUBCX |
BPdakFlhV |
Bp]EzOqE |
B`PjLz\Q |
BPNOUwSp |
BpO^HiRYiwjemPZ |
B_PpEZ |
Bp]qIk[ |
BpQrluTmV |
BPWAinxthINW |
BpWPWmvBgAjb |
BQlbU_ |
BQQoWntGb |
BQsdQJZhYd |
BQSmA`vj |
BQ[sZdvir |
B]QYR_eokuZb]V |
BqySkYSMGwoOE_s |
BrAr`VAnzEJlqms |
BRgJN`IrcKyVM |
B\riF[Rlfriw` |
BrK\dKHOeGiqT |
B[r\VcguMdcP |
B\scwz]EPNRB[MLly |
B`sFlWf |
BSkLbWW |
BSkwl\VeiqWWa |
BS`mRi |
BSsuLeryP |
BSUv^znL`l |
B_SVleV |
BSXKouiYKB\ql |
Bt |
BthSTLK |
B[tjXSdIse |
Btmnszyz |
B`tn |
B`]trPkTCp |
BtwyKwDtmc |
B^\Tz`ydfB |
BuBJSGNGwL |
BU]ckqvW` |
B]UeMeIP`DCNcJ |
Bup^TNkJz\RNqD |
BU_UQMjyd |
BVdXDjkQMfxWC |
BvFBemebTVS |
BvFig^FZo |
BVf^PQJOe]m |
BvhpBJk`dJx\DFc |
BVIpoJBjS |
BVMixgdyBxk |
BvoTuTbojSIktb |
BVQU |
B]vRfSzbv\Yewj^ZI |
BvswahmFInzu |
BVTLHwarRwQdpQ |
BVZtGATu |
BW_d |
BWeO_tu |
BwoC_U[V |
BwOK |
B^WQ\agNocsQx[V |
Bw[Q]svaAgupRb |
BWT^iYyXXc |
BWvOtjtcohb |
BwXHfwQRKWNlIdv |
BWXmFMDmf |
BXdzIrivx |
BXExDCAajT |
BxgDmbwi |
BxKIyhHoAapz |
BXqHvKXHMIpE |
BxscE_V |
BX`VLrCg^I |
ByatDh] |
BybD\f]Pw_ImZ\BVZ |
ByBjT[tptPAhqYC |
ByElyB[FEy |
BykDHDOQXX |
Byk^Zwv |
ByMGmLhH |
ByNfEtVLf |
BzAgNfND |
Bze]foE^\ |
BzEyWOMVNOcDfH |
B]zu^GQiIk |
BZ_UR |
B^zvmErhWpO |
BzWXeOpA |
DAhaCT |
DalcGVjwmxP |
DanBSupZvxb |
DANIwNORNh[ |
DaNlnZL |
DArE\UQDN |
DAr[``oT]pfnYle` |
DATN_[tdJNWL |
DbgKFD |
DBGo_KXRZ |
DBJ\Od[ |
DBobNgAxL |
DBpgLl_Dd |
DBQrV\f |
DbvSFjB`X |
DckfmkTzxYn\RU |
D\cKTFGbzoHJPvS |
DcPRkUMQo |
DC^qWHmEXHl |
DCvVxFReE |
DCzilif]qONlXW |
DDI |
DDIPZviOZFad |
DDsx_fIk |
DDzGPQiznZ |
Dea`c^^X |
DeBIajSitVfuzMa |
DEBlkUGkyUv |
DEIMCbNy |
D[Eipt^ublaHu\ |
D[^eNjj_LuTO |
DeslgWUmmmwJhT |
DeV\sS`MEtqRYBs |
DEXZXrn |
DeY]iWGSc |
DfDZlEIGQ |
DFev`obZuc |
D]FiQlExdXm` |
Df^Jkata_]YYz |
DF^STqayENT |
DfUshQUPRC^gK |
D_fvZzyz`xeiw |
DFwwnlWKo |
D]`GCsE^VAhh |
DG\[Of]CFC |
DGpyNnbXdyMtMZ |
DGyNv |
DgYTv^QRk\KPzHUD |
Dh\nWUtZK[R |
D`HrWMrUC |
DiaXVKgnIMxXp |
D[Ic`QdXN |
DJdD]urV]P |
DJGDMb_JoM |
DJisjyIXuQN |
DJJbthttK` |
DjNwk |
DjODRlVvftrS |
DJweaS |
DJzk[rL |
DKB_DrSU]T[lw_QWy |
DKcayvSByh |
DKdoOWysUi]VSb |
DkLKXtqZuQlP |
DKltKUlih |
DKmHtnsikgs |
DKsELmctZNm |
D^LAb]\l |
DlEMOkyGafMfPlz |
DlN^XztNHOzcR |
DL^u_G |
DLXSarvytF |
DlyySJwJcGdcH |
DMbt]kQNvYC |
D[mI |
DMlM[FFKNn |
DmloQOYYpb\ |
DMnEpXn |
DMQxmgxQglIwP`rb]h |
DmZeTed |
DnCaPCsMCJec |
D]OAWu[mQIB |
D^oDlqHw_B`PavT |
DofxxVWuL[Tw |
DOH |
D[oI^ZKngjrD]W |
DOl]Av`thfC |
DoMTxDgv]vEb |
D]oyTtSdZO |
DPdiGrKWc |
D]pM[Q |
DPnr^ |
DPsgv |
DPSkZQGmnFUC |
DpxTuke]LACD |
DPyFvn |
DqAZ`hbT\pN |
DqBUqRuDjqt |
DqngD_KKVVr |
D\qOnXvuQPny |
DqpgEF]\X |
DQsBrjFIUszKlgse |
DQstPn |
DqzWH]w |
DRhioo_JYhU_ |
DrJEfYMJcrvs`nDILx |
DRjTiGFExvwYd |
Drmpxc]NM |
DRoxwObd |
DRqRGzBTghpQ |
DrXmwqHtJO |
Drx]RaxC |
DRYJVIiS |
DrYJyOKuMqRTBz |
Ds]DVJbumPZGf[h |
DSEHUn |
DSHJK]QXV[W |
DSllAQFl^V |
D\sVDzDZgDIQk |
DsW[iyy` |
DS_YtAPU |
DsY\urx |
DTaKRALwEba |
Dt\HjQSm |
Dt_IkOtCcfIC |
DttsNFujM |
DuaBBKr[BgOF |
DuamOUq |
DUGUpTzD^ |
DUJcFYRMpO |
DupMxppbC |
DuqV`wR |
DuTILQKTmXk |
DUVJvlEvno |
Du`ZEfST_Hb |
DvhUe] |
DVhXIYYB |
DVJtNMw |
DvMW]UWBJ_ |
DVOtDFxs |
DVoWvrg |
DVRtewKHyLmrFf |
DVRulmpEbL |
DvWZW[nvgNfi |
DwavxIV\Dao |
DwfU\owgK` |
DwGh^hu^kqpjn |
DwizmYQ`CkXqj`MA |
DW\IzV]w_Ri |
DwZbAcWku |
DxEKNgLz |
DX^kOx |
DXlgYTy |
DxrFVlkDjR |
DYbfjDRjzc^ |
DyBxRQurQv]Hs |
DYDEdtU]`UlHMl |
DyNmvc\FwOZTM |
DyrnoNNHl |
D^zadqfpZNJ |
DZAhEGnavp_tf |
DZCCLesHvIHIao |
DzE^wguQIFUcm |
DZmvAtNLBW |
DZO |
DZ\OWE^scqFYUg |
DzpJRjgs[ |
DzT_D]n_Z |
D^ZvpCc[T |
DZvpUoK |
SELECT 359
QUERY PLAN |
---|
Sort (cost=199.48..200.49 rows=404 width=11) (actual time=3.202..3.218 rows=359 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 44kB |
-> Seq Scan on spelers (cost=0.00..181.99 rows=404 width=11) (actual time=0.021..2.844 rows=359 loops=1) |
Filter: (name ~ '^[BD].*'::text) |
Rows Removed by Filter: 9640 |
Planning Time: 0.063 ms |
Execution Time: 3.238 ms |
EXPLAIN
name |
---|
BaNSCpGSM |
DrJEfYMJcrvs`nDILx |
Drmpxc]NM |
DrXmwqHtJO |
Drx]RaxC |
DrYJyOKuMqRTBz |
SELECT 6
QUERY PLAN |
---|
Sort (cost=256.99..257.00 rows=1 width=11) (actual time=1.528..1.529 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..256.98 rows=1 width=11) (actual time=0.191..1.521 rows=6 loops=1) |
Filter: (((name ~>=~ 'Ba'::text) AND (name ~<~ 'Bb'::text)) OR ((name ~>=~ 'Dr'::text) AND (name ~<~ 'Ds'::text))) |
Rows Removed by Filter: 9993 |
Planning Time: 0.057 ms |
Execution Time: 1.539 ms |
EXPLAIN
DROP INDEX
CREATE INDEX
QUERY PLAN |
---|
Sort (cost=3.92..3.92 rows=2 width=11) (actual time=0.070..0.071 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Bitmap Heap Scan on spelers (cost=2.79..3.91 rows=2 width=11) (actual time=0.050..0.058 rows=6 loops=1) |
Recheck Cond: ((name ~~ 'Ba%'::text) OR (name ~~ 'Dr%'::text)) |
Filter: ((name ~~ 'Ba%'::text) OR (name ~~ 'Dr%'::text)) |
Heap Blocks: exact=6 |
-> BitmapOr (cost=2.79..2.79 rows=1 width=0) (actual time=0.042..0.043 rows=0 loops=1) |
-> Bitmap Index Scan on spelers_name_c_idx (cost=0.00..1.40 rows=1 width=0) (actual time=0.027..0.028 rows=1 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.013..0.013 rows=5 loops=1) |
Index Cond: ((name >= 'Dr'::text) AND (name < 'Ds'::text)) |
Planning Time: 0.250 ms |
Execution Time: 0.093 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=182.00..182.00 rows=2 width=11) (actual time=1.400..1.401 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..181.99 rows=2 width=11) (actual time=0.171..1.393 rows=6 loops=1) |
Filter: (name ~~ ANY ('{Ba%,Dr%}'::text[])) |
Rows Removed by Filter: 9993 |
Planning Time: 0.050 ms |
Execution Time: 1.411 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=3.92..3.92 rows=2 width=11) (actual time=0.026..0.026 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Bitmap Heap Scan on spelers (cost=2.79..3.91 rows=2 width=11) (actual time=0.014..0.020 rows=6 loops=1) |
Recheck Cond: ((name ^@ 'Ba'::text) OR (name ^@ 'Dr'::text)) |
Filter: ((name ^@ 'Ba'::text) OR (name ^@ 'Dr'::text)) |
Heap Blocks: exact=6 |
-> BitmapOr (cost=2.79..2.79 rows=1 width=0) (actual time=0.009..0.009 rows=0 loops=1) |
-> Bitmap Index Scan on spelers_name_c_idx (cost=0.00..1.40 rows=1 width=0) (actual time=0.005..0.005 rows=1 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=5 loops=1) |
Index Cond: ((name >= 'Dr'::text) AND (name < 'Ds'::text)) |
Planning Time: 0.124 ms |
Execution Time: 0.057 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=182.00..182.00 rows=2 width=11) (actual time=2.913..2.914 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..181.99 rows=2 width=11) (actual time=0.357..2.905 rows=6 loops=1) |
Filter: (name ^@ ANY ('{Ba,Dr}'::text[])) |
Rows Removed by Filter: 9993 |
Planning Time: 0.060 ms |
Execution Time: 2.923 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=182.00..182.00 rows=1 width=11) (actual time=3.049..3.050 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..181.99 rows=1 width=11) (actual time=0.353..3.037 rows=6 loops=1) |
Filter: (name ~ '^(Ba|Dr).*'::text) |
Rows Removed by Filter: 9993 |
Planning Time: 0.074 ms |
Execution Time: 3.063 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=199.48..200.49 rows=404 width=11) (actual time=3.236..3.252 rows=359 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 44kB |
-> Seq Scan on spelers (cost=0.00..181.99 rows=404 width=11) (actual time=0.023..2.898 rows=359 loops=1) |
Filter: (name ~ '^[BD].*'::text) |
Rows Removed by Filter: 9640 |
Planning Time: 0.098 ms |
Execution Time: 3.275 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=256.99..257.00 rows=1 width=11) (actual time=1.521..1.522 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..256.98 rows=1 width=11) (actual time=0.184..1.514 rows=6 loops=1) |
Filter: (((name ~>=~ 'Ba'::text) AND (name ~<~ 'Bb'::text)) OR ((name ~>=~ 'Dr'::text) AND (name ~<~ 'Ds'::text))) |
Rows Removed by Filter: 9993 |
Planning Time: 0.066 ms |
Execution Time: 1.533 ms |
EXPLAIN
DROP INDEX
CREATE INDEX
QUERY PLAN |
---|
Sort (cost=4.72..4.72 rows=2 width=11) (actual time=0.063..0.064 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Bitmap Heap Scan on spelers (cost=2.52..4.71 rows=2 width=11) (actual time=0.030..0.039 rows=6 loops=1) |
Recheck Cond: ((name ~~ 'Ba%'::text) OR (name ~~ 'Dr%'::text)) |
Filter: ((name ~~ 'Ba%'::text) OR (name ~~ 'Dr%'::text)) |
Heap Blocks: exact=6 |
-> BitmapOr (cost=2.52..2.52 rows=2 width=0) (actual time=0.020..0.020 rows=0 loops=1) |
-> Bitmap Index Scan on spelers_name_spgist_idx (cost=0.00..1.26 rows=1 width=0) (actual time=0.010..0.011 rows=1 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.008..0.008 rows=5 loops=1) |
Index Cond: (name ^@ 'Dr'::text) |
Planning Time: 0.299 ms |
Execution Time: 0.095 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=182.00..182.00 rows=2 width=11) (actual time=1.578..1.579 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..181.99 rows=2 width=11) (actual time=0.204..1.562 rows=6 loops=1) |
Filter: (name ~~ ANY ('{Ba%,Dr%}'::text[])) |
Rows Removed by Filter: 9993 |
Planning Time: 0.060 ms |
Execution Time: 1.590 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=4.72..4.72 rows=2 width=11) (actual time=0.024..0.025 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Bitmap Heap Scan on spelers (cost=2.52..4.71 rows=2 width=11) (actual time=0.014..0.019 rows=6 loops=1) |
Recheck Cond: ((name ^@ 'Ba'::text) OR (name ^@ 'Dr'::text)) |
Heap Blocks: exact=6 |
-> BitmapOr (cost=2.52..2.52 rows=2 width=0) (actual time=0.011..0.011 rows=0 loops=1) |
-> Bitmap Index Scan on spelers_name_spgist_idx (cost=0.00..1.26 rows=1 width=0) (actual time=0.006..0.006 rows=1 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=5 loops=1) |
Index Cond: (name ^@ 'Dr'::text) |
Planning Time: 0.066 ms |
Execution Time: 0.045 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=4.71..4.72 rows=2 width=11) (actual time=0.024..0.025 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Bitmap Heap Scan on spelers (cost=2.52..4.70 rows=2 width=11) (actual time=0.015..0.019 rows=6 loops=1) |
Recheck Cond: (name ^@ ANY ('{Ba,Dr}'::text[])) |
Heap Blocks: exact=6 |
-> Bitmap Index Scan on spelers_name_spgist_idx (cost=0.00..2.52 rows=2 width=0) (actual time=0.012..0.012 rows=6 loops=1) |
Index Cond: (name ^@ ANY ('{Ba,Dr}'::text[])) |
Planning Time: 0.061 ms |
Execution Time: 0.042 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=182.00..182.00 rows=1 width=11) (actual time=3.000..3.000 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Seq Scan on spelers (cost=0.00..181.99 rows=1 width=11) (actual time=0.371..2.992 rows=6 loops=1) |
Filter: (name ~ '^(Ba|Dr).*'::text) |
Rows Removed by Filter: 9993 |
Planning Time: 0.077 ms |
Execution Time: 3.009 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=199.48..200.49 rows=404 width=11) (actual time=3.220..3.236 rows=359 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 44kB |
-> Seq Scan on spelers (cost=0.00..181.99 rows=404 width=11) (actual time=0.031..2.858 rows=359 loops=1) |
Filter: (name ~ '^[BD].*'::text) |
Rows Removed by Filter: 9640 |
Planning Time: 0.060 ms |
Execution Time: 3.256 ms |
EXPLAIN
QUERY PLAN |
---|
Sort (cost=3.65..3.66 rows=1 width=11) (actual time=0.039..0.041 rows=6 loops=1) |
Sort Key: name |
Sort Method: quicksort Memory: 25kB |
-> Bitmap Heap Scan on spelers (cost=2.52..3.64 rows=1 width=11) (actual time=0.025..0.031 rows=6 loops=1) |
Recheck Cond: (((name ~>=~ 'Ba'::text) AND (name ~<~ 'Bb'::text)) OR ((name ~>=~ 'Dr'::text) AND (name ~<~ 'Ds'::text))) |
Heap Blocks: exact=6 |
-> BitmapOr (cost=2.52..2.52 rows=1 width=0) (actual time=0.021..0.021 rows=0 loops=1) |
-> Bitmap Index Scan on spelers_name_spgist_idx (cost=0.00..1.26 rows=1 width=0) (actual time=0.012..0.012 rows=1 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.008..0.008 rows=5 loops=1) |
Index Cond: ((name ~>=~ 'Dr'::text) AND (name ~<~ 'Ds'::text)) |
Planning Time: 0.108 ms |
Execution Time: 0.063 ms |
EXPLAIN
QUERY PLAN |
---|
Bitmap Heap Scan on spelers (cost=2.52..4.71 rows=2 width=11) (actual time=0.019..0.028 rows=6 loops=1) |
Recheck Cond: ((name ~ '^(?:Ba.*)$'::text) OR (name ~ '^(?:Dr.*)$'::text)) |
Filter: ((name ~ '^(?:Ba.*)$'::text) OR (name ~ '^(?:Dr.*)$'::text)) |
Heap Blocks: exact=6 |
-> BitmapOr (cost=2.52..2.52 rows=2 width=0) (actual time=0.012..0.012 rows=0 loops=1) |
-> Bitmap Index Scan on spelers_name_spgist_idx (cost=0.00..1.26 rows=1 width=0) (actual time=0.006..0.006 rows=1 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=5 loops=1) |
Index Cond: (name ^@ 'Dr'::text) |
Planning Time: 0.162 ms |
Execution Time: 0.047 ms |
EXPLAIN