Ao amin'ny tontolon'ny Postgres, ny index dia tena ilaina amin'ny fitetezana mahomby amin'ny fitahirizana angon-drakitra (antsoina hoe "heap"). Ny Postgres dia tsy manohana ny fanangonana azy, ary ny maritrano MVCC dia mahatonga anao hiafara amin'ny dikan-teny maro amin'ny tuple mitovy. Noho izany, tena zava-dehibe ny fahafahana mamorona sy mitazona indexes mahomby hanohanana ny fampiharana.
Ireto misy toro-hevitra vitsivitsy amin'ny fanatsarana sy fanatsarana ny fampiasana ny index.
Fanamarihana: ireo fanontaniana aseho etsy ambany dia miasa amin'ny tsy novaina .
Mampiasa Indexes Covering
Andeha hojerentsika ny fangatahana hanesorana adiresy mailaka ho an'ireo mpampiasa tsy mavitrika. LOHA customer misy tsanganana active, ary tsotra ny fanontaniana:
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)
Ny fanontaniana dia miantso ny filaharan'ny scan table feno customer. Andao hamorona tondro eo amin'ny tsanganana iray 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)
Nanampy izany, ny scan manaraka dia nivadika ho "index scan". Midika izany fa ny Postgres dia hijery ny index "idx_cust1", ary avy eo tohizo ny fikarohana ny antontan-databatra mba hamakiana ny sandan'ny tsanganana hafa (amin'ity tranga ity, ny tsanganana email) izay ilain'ny fanontaniana.
Ny fanondroana mandrakotra dia ampidirina ao amin'ny PostgreSQL 11. Izy ireo dia mamela anao hampiditra tsanganana iray na maromaro fanampiny ao amin'ny index - ny sandany dia voatahiry ao amin'ny fivarotana data index.
Raha nanararaotra an'io endri-javatra io izahay ary nampiana ny sandan'ny mailaka ao anatin'ny index, dia tsy mila mikaroka ny sandany ny Postgres. email. Andeha hojerentsika raha mety izany:
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' dia milaza amintsika fa ny fangatahana izao dia mila ny index, izay manampy amin'ny fisorohana ny disk I/O rehetra hamakiana ny antontan-databatra.
Ny fanondroana mandrakotra dia tsy misy afa-tsy amin'ny hazo B amin'izao fotoana izao. Na izany aza, amin'ity tranga ity dia ho ambony kokoa ny ezaka fikojakojana.
Mampiasa Fanondroana ampahany
Ny fanondroan'ny ampahany ihany no manondro ampahany amin'ny andalana ao anaty latabatra. Izany dia mitahiry ny haben'ny indexes ary mahatonga ny scans haingana kokoa.
Andeha atao hoe te hahazo lisitry ny adiresin'ny mailaka an'ny mpanjifanay any California izahay. Ny fangatahana dia ho toy izao:
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)Inona no homen'ny index mahazatra antsika:
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)
Notarafina address nosoloina index scan idx_address1ary avy eo nojerena ny antontam-bato address.
Satria fanontaniana matetika izany ary mila amboarina, dia afaka mampiasa fanondroana ampahany isika, izay manondro ireo andalana misy adiresy misy ny distrika ihany. β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)
Ankehitriny dia mamaky fotsiny ny fanontaniana idx_address2 ary tsy mikasika ny latabatra address.
Fampiasana fanondroana sanda maro
Mety tsy misy karazana angona scalar ny tsanganana sasany hosoratana. Karazana tsanganana toy ny jsonb, arrays ΠΈ tsvector misy sanda mitambatra na maromaro. Raha mila manindrona tsanganana toy izany ianao dia matetika mila mikaroka ny soatoavina tsirairay ao amin'ireo tsanganana ireo.
Andao hiezaka hitady ny lohatenin'ny sarimihetsika rehetra misy fanapahana tsy nahomby. LOHA film misy tsanganana lahatsoratra antsoina hoe special_features. Raha manana an'io "fananana manokana" io ny sarimihetsika, dia misy ilay singa ho laharan-tsoratra ny tsanganana Behind The Scenes. Mba hikaroka ireo sarimihetsika toy izany dia mila misafidy ny laharana rehetra misy "Behind The Scenes" isika rehefa misy sanda array special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
OpΓ©rateur nesting @> manamarina raha ny ilany havanana dia ampahany amin'ny ilany havia.
Fangatahana drafitra:
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)Izay mangataka scan feno feno miaraka amin'ny vidiny 67.
Andao hojerentsika raha manampy antsika ny fanondroana hazo B mahazatra:
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)Tsy nojerena akory ny tondro. Ny index B-tree dia tsy mahafantatra ny fisian'ny singa tsirairay ao amin'ny soatoavina voarakitra.
Mila index GIN isika.
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)Ny fanondroana GIN dia manohana ny fanaovana sari-tany soatoavina tokana manohitra ny soatoavina mitambatra voarakitra, ka mahatonga ny vidin'ny drafitry ny fangatahana izay mihoatra ny antsasany.
Esory ny index duplicate
Ny fanondroana dia miangona rehefa mandeha ny fotoana, ary indraindray ny fanondro vaovao dia mety ahitana famaritana mitovy amin'ny iray amin'ireo teo aloha. Azonao atao ny mampiasa ny katalaogy mba hahazoana famaritana SQL azo vakiana amin'ny fanondroana. pg_indexes. Afaka mahita famaritana mitovy ihany koa ianao:
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
Mety hitranga ny hiafaranao amin'ny fanondroana maro, ny iray amin'izy ireo dia manondro superset amin'ny tsanganana izay manisy fanondroana hafa. Mety ho tiana izany na mety tsy ho tiana β ny superset dia mety hiteraka fanondroana fotsiny, izay tsara, nefa mety haka toerana be loatra, na tsy ampiasaina intsony ny fanontaniana izay nokasain'ny superset hohatsaraina.
Raha mila manao automatique ny famaritana ireo fanondroana toy izany ianao dia afaka manomboka amin'ny avy amin'ny latabatra pg_catalog.
Indexes tsy ampiasaina
Rehefa mivoatra ny fampiharana mampiasa angon-drakitra, dia mitombo koa ny fanontaniana ampiasainy. Tsy azo ampiasaina intsony ny fanondroana nampiana teo aloha. Isaky ny asiana fanondroana dia voamariky ny mpitantana ny antontan'isa izany, ary ao amin'ny fijerin'ny katalaogin'ny rafitra pg_stat_user_indexes hitanao ny sandany idx_scan, izay kaontera mitambatra. Ny fanaraha-maso an'io sanda io mandritra ny fe-potoana iray (milaza ny iray volana) dia hanome hevitra tsara hoe iza amin'ireo tondro tsy ampiasaina ary azo ariana.
Ity misy fanontaniana iray hahazoana ny isa scan amin'izao fotoana izao amin'ny tondro rehetra ao amin'ny skema β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)Manangana tondro misy hidy vitsy kokoa
Matetika ny fanondroana dia mila amboarina, ohatra rehefa mibontsina, ary ny fanamboarana dia afaka manafaingana ny scan. Mety ho simba koa ny index. Ny fanovana ny mari-pamantarana fanondro dia mety mitaky fanamboarana azy ihany koa.
Alefaso ny famoronana index parallèle
Ao amin'ny PostgreSQL 11, ny famoronana index B-Tree dia miaraka. Mba hanafainganana ny fizotran'ny famoronana, dia azo ampiasaina ny mpiasa mifanitsy maromaro. Na izany aza, ataovy azo antoka fa voapetraka tsara ireto safidy fanitsiana ireto:
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;Kely loatra ny soatoavina default. Ny tsara indrindra dia tokony hitombo ireo isa ireo miaraka amin'ny isan'ny cores processeur. Vakio bebe kokoa ao amin'ny .
Famoronana index background
Azonao atao ny mamorona index ao ambadika mampiasa ny safidy CONCURRENTLY didy CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXIty fomba famoronana index ity dia tsy mitovy amin'ny mahazatra satria tsy mitaky hidy eo amin'ny latabatra, ary noho izany dia tsy manakana ny asa fanoratana. Etsy ankilany, mila fotoana bebe kokoa izany ary mandany vola bebe kokoa.
Ny Postgres dia manome flexibilitΓ© be dia be amin'ny famoronana index sy fomba hiatrehana tranga manokana, ary koa fomba hitantanana ny angon-drakitra raha toa ka mitombo be ny fampiharanao. Manantena izahay fa hanampy anao hahazo haingana ny fanontanianao ireo toro-hevitra ireo ary ho vonona ny hanitatra ny angon-drakitrao.
Source: www.habr.com