🥇 Utimur omnibus lineamentis indices in PostgreSQL

In mundo Postgres, indices necessarii sunt ad efficiendam navigationem repositionis datorum (dicitur "acervus"). Postgres non gregatim pro eo adiuvat, et architectura MVCC te facit ut cum multis versionibus eiusdem tuplae finias. Ideo magni momenti est ut indices efficientes creare et manutenere possint applicationes ad sustentationem.

Hic sunt aliquae apices ad optimizing et emendandi usum indices.

Nota: interrogationes infra ostensae de opere immodificato pagila sample database.

Using Cover Indices

Inspice petitionem ut electronica electronica excerpant pro pigris usoribus. Mensa customer ibi est columna activeac simplex quaesitum est;

pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on customer  (cost=0.00..16.49 rows=15 width=32)
   Filter: (active = 0)
(2 rows)

Quaestio invocat plenam mensam scan sequentia customer. Faciamus in columna indicem active:

pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using idx_cust1 on customer  (cost=0.28..12.29 rows=15 width=32)
   Index Cond: (active = 0)
(2 rows)

Profuit, sequens scan vertitur in "index scan». Hoc significat quod Postgres indicem lustrabit "idx_cust1", ac deinde pergere quaerere acervum tabulae ad valores aliarum columnarum (in hoc casu, columna" email) interrogatione indiget.

Indices tegentes inducuntur in PostgreSQL XI. Permittunt te unum vel plures columnas in ipso indice additas comprehendere - eorum bona in indice notitia copia reposita sunt.

Si usus hoc pluma et valorem electronicum intra indicem addidit, Postgres non opus erat ut acervum tabulae pro valore quaereret. email. Videamus si hoc faciet;

pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Only Scan using idx_cust2 on customer  (cost=0.28..12.29 rows=15 width=32)
   Index Cond: (active = 0)
(2 rows)

«Index Only Scan' indicat interrogationem nunc solum indice indigere, qui iuvat vitare omnem orbem I/O ad tumulum mensae legere.

Indices tegentes nunc tantum praesto sunt pro B-arboribus. Sed in hoc casu altior labor sustentationis erit.

Using partiales Indices

Indices partiales index tantum subset versuum in tabula. Haec magnitudinem indices servat et citius lustrat.

Dicamus nos velle indicem inscriptionum electronicarum apud Californiam nostrorum clientium accipere. Postulatio talis erit:

SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                              QUERY PLAN
----------------------------------------------------------------------
 Hash Join  (cost=15.65..32.22 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=15.54..15.54 rows=9 width=4)
         ->  Seq Scan on address a  (cost=0.00..15.54 rows=9 width=4)
               Filter: (district = 'California'::text)
(6 rows)

Quos indices ordinarii nobis dabit;

pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Hash Join  (cost=12.98..29.55 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=12.87..12.87 rows=9 width=4)
         ->  Bitmap Heap Scan on address a  (cost=4.34..12.87 rows=9 width=4)
               Recheck Cond: (district = 'California'::text)
               ->  Bitmap Index Scan on idx_address1  (cost=0.00..4.34 rows=9 width=0)
                     Index Cond: (district = 'California'::text)
(8 rows)

scan address substituitur index scan idx_address1et lustrabat lumine tumulum address.

Cum hoc crebra interrogatione et necessitate optimized sit, indice partiali uti possumus, qui indices illos tantum ordines cum inscriptionibus in quibus pagi ‘California’:

pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Hash Join  (cost=12.38..28.96 rows=9 width=32)
   Hash Cond: (c.address_id = a.address_id)
   ->  Seq Scan on customer c  (cost=0.00..14.99 rows=599 width=34)
   ->  Hash  (cost=12.27..12.27 rows=9 width=4)
         ->  Index Only Scan using idx_address2 on address a  (cost=0.14..12.27 rows=9 width=4)
(5 rows)

Nunc quaesitum solum legit idx_address2 et non tangit mensam address.

Multi- eae per Indices

Aliquae columnae indicentur non possunt scalare genus notae continere. Types columna similis jsonb, arrays и tsvector quae composita vel plures valores. Si tales columnas indicare debes, per omnes valores singulares in illis columnis inquirere solere.

Experiamur invenire titulos omnium membranarum continentium ab incisis infelicis capit. Mensa film ibi est illud columna nomine special_features. Si pellicula hanc "proprietatem specialem" habet, tunc columna elementum contineat sicut aciem textus Behind The Scenes. Ut perscrutemur omnes tales membranas, necesse est omnes ordines cum "Post Scenes" eligere, cum nihil ordinata values special_features:

SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';

Operator nidificans @> si dextra latus sistit, restant a latere sinistro.

Petitio consilii:

pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)
   Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)

Quae pleno petit acervo raucis cum dispendio 67 .

Videamus an index ordinarius B-arboris nos adiuvet;

pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on film  (cost=0.00..67.50 rows=5 width=15)
   Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)

Ne index quidem consideratus est. In indice B-arbor non cognoscit exsistentiam singulorum elementorum in valoribus indicatis.

GIN indice opus est.

pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
                                QUERY PLAN
---------------------------------------------------------------------------
 Bitmap Heap Scan on film  (cost=8.04..23.58 rows=5 width=15)
   Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
   ->  Bitmap Index Scan on idx_film2  (cost=0.00..8.04 rows=5 width=0)
         Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)

Index GIN subsidia destinata singularibus valoribus contra valores compositos recensitos, inde per quaesitum consilium sumptus plus quam dimidiatum est.

Tollendi duplicata indices

Indices tempus cumulant, interdum novus index eandem definitionem ac unum ex superioribus continere potest. Catalogum visum uti potes ut SQL definitiones indices humana-readabiles accipias. pg_indexes. Etiam definitiones identicae facile invenire potes:

 SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=#   SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-#     FROM pg_indexes
pagila-# GROUP BY defn
pagila-#   HAVING count(*) > 1;
                                indexes                                 |                                defn
------------------------------------------------------------------------+------------------------------------------------------------------
 {payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX  ON public.payment_p2017_01 USING btree (customer_id
 {payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX  ON public.payment_p2017_02 USING btree (customer_id
 {payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX  ON public.payment_p2017_03 USING btree (customer_id
 {idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_04 USING btree (customer_id
 {payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX  ON public.payment_p2017_05 USING btree (customer_id
 {idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX  ON public.payment_p2017_06 USING btree (customer_id
(6 rows)

Superset Indexes

Fieri potest ut multis indicibus terminetur, e quibus unus index superset columnarum qui alios indices indicent. Hoc sit vel non optabile: supersetum inveniatur in indicibus solum scans, quod est bonum, sed potest nimis spatium capere, vel interrogatione superset destinata ad optimize non amplius adhibetur.

Si definitionem horum indices automate debes, incipere potes pg_index de mensa pg_catalog.

insueta indexes

Sicut applicationes evolutionis databases utuntur, ita interrogationes utuntur. Indices antea additi nulla interrogatione iam uti possunt. Quoties index lustratur, ab statisticis procuratori notatur, et in catalogue electronica sententia pg_stat_user_indexes potes videre valorem idx_scanquod est cumulativo. Hoc valore per aliquod temporis spatium (dic a mensis) utilem dabit rationem cuius indices usus non sunt nec omitti possunt.

Hic quaesitum est ut schedulae hodiernae omnium indices in schemate comprehendantur ‘public’:

SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM   pg_catalog.pg_stat_user_indexes
pagila-# WHERE  schemaname = 'public'
pagila-# LIMIT  10;
    relname    |    indexrelname    | idx_scan
---------------+--------------------+----------
 customer      | customer_pkey      |    32093
 actor         | actor_pkey         |     5462
 address       | address_pkey       |      660
 category      | category_pkey      |     1000
 city          | city_pkey          |      609
 country       | country_pkey       |      604
 film_actor    | film_actor_pkey    |        0
 film_category | film_category_pkey |        0
 film          | film_pkey          |    11043
 inventory     | inventory_pkey     |    16048
(10 rows)

Reedificandi indices cum paucioribus seras

Indices saepe reaedificari oportet, exempli gratia cum tumescant et reaedificando scan accelerare possunt. Item indices corrupti possunt. Mutans indicem parametri etiam postulare potest eam reaedificandi.

Admitte parallela creatio

In PostgreSQL 11, index concurrit in creando B-Arborem. Ad processum creationis accelerandum, plures operarii paralleli adhiberi possunt. Sed fac ut hae optiones configurationes recte ponantur;

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

Valores default nimis parvae sunt. Specimen, hi numeri una cum numero coros processus augere debent. Read more in documentum.

Background index creationis

Potes creare indicem in background utens optione CONCURRENTLY imperium CREATE INDEX:

pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEX

Index creationis hic a consueto processu differt quod in mensa seram non requirit, et ideo operationes scribere non impedit. E contra, plus temporis sumit et plus consumit opes.

Postgres multum praebet flexibilitatem ad indices et vias creandas ad quosvis casus speciales solvendos, necnon vias ad modum datorum administrandi in casu tuo applicationis explodinge crescit. Speramus his apicibus auxiliabitur tibi quaestionibus tuis ieiunium et datorum ad scalam paratos recipias.

Source: www.habr.com

Omnibus notis indices in PostgreSQL usus