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
Using Cover Indices
Inspice petitionem ut electronica electronica excerpant pro pigris usoribus. Mensa customer
ibi est columna active
ac 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_address1
et 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_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_scan
quod 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
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