Indeksi Postgres nuk përdoret

Unë kam një pyetje mbi të 30-tat që duket kështu

select 
     coalesce(sum("paidInstallment"-(plafond/tenor*frequency)),0) 
from 
    account_transaction_debit atd 
    join r_account_transaction_debit ratd on atd.id = ratd."accountTransactionDebitId" and atd."deletedAt" isnull and ratd."deletedAt" isnulland atd."type" = 'INSTALLMENT'
    join account a on a.id = ratd."accountId" and a."deletedAt" isnull and a.id = 4735
    join r_account_transaction_debit_installment ratdi ratd."accountTransactionDebitId" =  atd.id and ratdi."deletedAt" isnull
    join installment i on i.id = ratdi."installmentId" and i."deletedAt" isnull
    join r_loan_installment rli on i.id = rli."installmentId" rli."deletedAt" isnull
    join loan l on l.id = rli."loanId" and l."deletedAt" isnull
where 
   atd."createdAt"::date between date_trunc('week',current_date) and current_date;

Ky është indeksi që pritet të përdoret (n.b.: lloji "createdAt" është vula kohore me zonën kohore)

CREATE UNIQUE INDEX 
account_transaction_debit_deletedAt_type_createdAtDate_id_idx 
ON account_transaction_debit ("deletedAt","type",my_to_char("createdAt"),id);

dhe funksioni my_to_char duket si

CREATE OR REPLACE FUNCTION my_to_char(some_time timestamptz) 
  RETURNS text
AS
$BODY$
    select to_char($1, 'yyyy-mm-dd');
$BODY$
LANGUAGE sql
IMMUTABLE;

dhe në fund rezultatet e shpjegimit (analizoj, verbose, buffers).

Aggregate  (cost=17943983.42..17943983.43 rows=1 width=32) (actual time=3020772.135..3020772.135 rows=1 loops=1)
  Output: COALESCE(sum((i."paidInstallment" - ((l.plafond / (l.tenor)::numeric) * (i.frequency)::numeric))), '0'::numeric)
  Buffers: shared hit=84541, temp read=25220 written=25092
  ->  Nested Loop  (cost=157262.23..8369328.64 rows=638310318 width=18) (actual time=3520.565..593472.501 rows=3168056628 loops=1)
        Output: i."paidInstallment", l.plafond, l.tenor, i.frequency
        Buffers: shared hit=84541, temp read=25220 written=25092
        ->  Nested Loop  (cost=125695.82..280946.58 rows=1384621 width=22) (actual time=1266.622..6543.090 rows=1496484 loops=1)
              Output: i."paidInstallment", i.frequency, l.plafond, l.tenor, a.id
              Buffers: shared hit=53058, temp read=25012 written=24886
              ->  Index Scan using account_id_idx on public.account a  (cost=0.29..8.31 rows=1 width=4) (actual time=0.012..0.518 rows=1 loops=1)
                    Output: a.id, a."totalDebit", a."totalCredit", a."totalBalance", a."createdAt", a."updatedAt", a."deletedAt", a."oldId", a.threshold
                    Index Cond: (a.id = 4735)
                    Filter: (a."deletedAt" IS NULL)
                    Buffers: shared hit=93
              ->  Hash Join  (cost=125695.53..267092.07 rows=1384621 width=18) (actual time=1266.608..6240.859 rows=1496484 loops=1)
                    Output: i."paidInstallment", i.frequency, l.plafond, l.tenor
                    Hash Cond: (rli."loanId" = l.id)
                    Buffers: shared hit=52965, temp read=25012 written=24886
                    ->  Hash Join  (cost=120652.07..228476.25 rows=1434437 width=14) (actual time=1224.901..4429.943 rows=1496484 loops=1)
                          Output: i."paidInstallment", i.frequency, rli."loanId"
                          Hash Cond: (ratdi."installmentId" = i.id)
                          Buffers: shared hit=49833, temp read=21586 written=21462
                          ->  Hash Join  (cost=52321.11..118507.38 rows=1493212 width=12) (actual time=552.083..1704.683 rows=1496484 loops=1)
                                Output: ratdi."installmentId", rli."installmentId", rli."loanId"
                                Hash Cond: (ratdi."installmentId" = rli."installmentId")
                                Buffers: shared hit=21624, temp read=9403 written=9341
                                ->  Seq Scan on public.r_account_transaction_debit_installment ratdi  (cost=0.00..25809.68 rows=1511094 width=4) (actual time=0.010..303.506 rows=1496491 loops=1)
                                      Output: ratdi."installmentId"
                                      Filter: (ratdi."deletedAt" IS NULL)
                                      Rows Removed by Filter: 8502
                                      Buffers: shared hit=10606
                                ->  Hash  (cost=26774.97..26774.97 rows=1557051 width=8) (actual time=551.360..551.360 rows=1529141 loops=1)
                                      Output: rli."installmentId", rli."loanId"
                                      Buckets: 131072  Batches: 32  Memory Usage: 2897kB
                                      Buffers: shared hit=11018, temp written=5047
                                      ->  Seq Scan on public.r_loan_installment rli  (cost=0.00..26774.97 rows=1557051 width=8) (actual time=0.010..267.452 rows=1529141 loops=1)
                                            Output: rli."installmentId", rli."loanId"
                                            Filter: (rli."deletedAt" IS NULL)
                                            Rows Removed by Filter: 17031
                                            Buffers: shared hit=11018
                          ->  Hash  (cost=43020.87..43020.87 rows=1456007 width=14) (actual time=672.617..672.617 rows=1510902 loops=1)
                                Output: i."paidInstallment", i.frequency, i.id
                                Buckets: 131072  Batches: 32  Memory Usage: 3244kB
                                Buffers: shared hit=28209, temp written=6413
                                ->  Seq Scan on public.installment i  (cost=0.00..43020.87 rows=1456007 width=14) (actual time=0.010..360.023 rows=1510902 loops=1)
                                      Output: i."paidInstallment", i.frequency, i.id
                                      Filter: (i."deletedAt" IS NULL)
                                      Rows Removed by Filter: 24993
                                      Buffers: shared hit=28209
                    ->  Hash  (cost=3845.53..3845.53 rows=68875 width=12) (actual time=41.500..41.500 rows=69253 loops=1)
                          Output: l.plafond, l.tenor, l.id
                          Buckets: 131072  Batches: 2  Memory Usage: 2655kB
                          Buffers: shared hit=3132, temp written=151
                          ->  Seq Scan on public.loan l  (cost=0.00..3845.53 rows=68875 width=12) (actual time=0.005..25.918 rows=69253 loops=1)
                                Output: l.plafond, l.tenor, l.id
                                Filter: (l."deletedAt" IS NULL)
                                Rows Removed by Filter: 2611
                                Buffers: shared hit=3132
        ->  Materialize  (cost=31566.41..109504.70 rows=461 width=4) (actual time=0.002..0.130 rows=2117 loops=1496484)
              Output: ratd."accountId"
              Buffers: shared hit=31483, temp read=208 written=206
              ->  Hash Join  (cost=31566.41..109502.39 rows=461 width=4) (actual time=2253.937..2295.318 rows=2117 loops=1)
                    Output: ratd."accountId"
                    Hash Cond: ((atd.id)::double precision = ratd."accountTransactionDebitId")
                    Buffers: shared hit=31483, temp read=208 written=206
                    ->  Seq Scan on public.account_transaction_debit atd  (cost=0.00..77463.04 rows=7467 width=4) (actual time=2048.273..2078.574 rows=20319 loops=1)
                          Output: atd.id
                          Filter: ((atd."deletedAt" IS NULL) AND (atd.type = 'INSTALLMENT'::text) AND ((atd."createdAt")::date <= ('now'::cstring)::date) AND ((atd."createdAt")::date >= date_trunc('week'::text, (('now'::cstring)::date)::timestamp with time zone)))
                          Rows Removed by Filter: 1496536
                          Buffers: shared hit=20688
                    ->  Hash  (cost=30009.74..30009.74 rows=94854 width=8) (actual time=205.009..205.009 rows=102568 loops=1)
                          Output: ratd."accountTransactionDebitId", ratd."accountId"
                          Buckets: 131072  Batches: 2  Memory Usage: 3021kB
                          Buffers: shared hit=10795, temp written=175
                          ->  Seq Scan on public.r_account_transaction_debit ratd  (cost=0.00..30009.74 rows=94854 width=8) (actual time=17.923..180.127 rows=102568 loops=1)
                                Output: ratd."accountTransactionDebitId", ratd."accountId"
                                Filter: ((ratd."deletedAt" IS NULL) AND (ratd."accountId" = 4735))
                                Rows Removed by Filter: 1414286
                                Buffers: shared hit=10795
Planning time: 1.181 ms
Execution time: 3020772.395 ms

Kam kërkuar zgjidhjen, por asgjë nuk e zgjidh problemin tim, a ka ndonjë opsion që duhet të provoj?

postgresql indexing

29.11.2017

bërë @a_horse_with_no_name :) 29.11.2017
Skanimi Seq në account_transaction_debit për të cilin jeni të shqetësuar zgjat vetëm 2 sekonda. Problemi aktual është hapi Materialize që përmbledh deri në 192 sekonda. (sepse është bërë 1496484 herë). Cili është cilësimi juaj work_mem dhe temp_buffers? 29.11.2017
4MB dhe 8MB respektivisht @a_horse_with_no_name 29.11.2017
Përpiquni t'i rrisni ato vlera përpara se të ekzekutoni deklaratën. Diçka si set session work_mem='64MB'; E njëjta gjë për temp_buffers 29.11.2017

Përgjigjet:

Në përgjithësi, një indeks përdoret vetëm nëse shprehja në klauzolën where përputhet saktësisht me shprehjen e indeksit. Preferohen plus kolonat kryesore të indeksit.

Ju nuk përdorni my_to_char("createdAt") në klauzolën where por shprehjen "createdAt"::date.

Ju do të duhet të përcaktoni një indeks në "createdAt"::date - që është një zgjedhje më e mirë gjithsesi pasi kjo e bën indeksin më të vogël. Një date ruhet në 4 bajt, paraqitja e karaktereve merr 10 bajt.

Duke qenë se ju vetëm i referoheni "createdAt"::date në pyetjen tuaj, ajo shprehje duhet të jetë gjithashtu shprehja udhëheqëse e një indeksi. Edhe nëse e ndryshoni indeksin tuaj unik për të përdorur "createdAt"::date, me shumë mundësi nuk do të përdoret.

Indeksi i mëposhtëm duhet të ndihmojë:

CREATE INDEX ON account_transaction_debit ( ("createdAt"::date) );

Ndërsa keni kushte bashkimi në deletedAt dhe type, një indeks i pjesshëm mund të jetë edhe më i mirë:

CREATE INDEX ON account_transaction_debit ( ("createdAt"::date), type )
where "deletedAt" is null;

Modifiko

Keni harruar të përmendni se po përdorni një kolonë timestamp with time zone e cila i ndërlikon gjërat (që është një shembull i bukur pse është gjithmonë një ide e mirë të përfshihen deklaratat create table).

Ju ende mund të punoni me vlerën e datës, por do t'ju duhet të ndryshoni funksionin, pyetjen dhe indeksin tuaj:

CREATE OR REPLACE FUNCTION get_date(some_time timestamptz) 
  RETURNS date
AS
$BODY$
    select some_time::date
$BODY$
LANGUAGE sql
IMMUTABLE;

Pastaj mund të krijoni këtë indeks:

CREATE INDEX ON account_transaction_debit ( (get_date("createdAt")), type )
where "deletedAt" is null;

dhe më pas përdorni kushtin e mëposhtëm në pyetjen tuaj:

where get_date(atd."createdAt") between date_trunc('week',current_date) and current_date;

29.11.2017

Fatkeqësisht nuk mund të përdor CREATE INDEX ON account_transaction_debit ( ("createdAt"::date) ); pasi lloji i krijuarAt është vula kohore me zonën kohore, do të prodhonte ERROR: functions in index expression must be marked IMMUTABLE, a ka ndonjë zgjidhje? 29.11.2017

Materiale të reja

Masterclass Coroutines: Kapitulli-3: Anulimi i korutinave dhe trajtimi i përjashtimeve.

Mirë se vini në udhëzuesin gjithëpërfshirës mbi Kotlin Coroutines! Në këtë seri artikujsh, unë do t'ju çoj në një udhëtim magjepsës, duke filluar nga bazat dhe gradualisht duke u thelluar në..

Faketojeni derisa ta arrini me të dhënat false

A e gjeni ndonjëherë veten duke ndërtuar një aplikacion të ri dhe keni nevojë për të dhëna testimi që duken dhe duken më realiste ose një grup i madh të dhënash për performancën e ngarkesës...

Si të përdorni kërkesën API në Python

Kërkesë API në GitHub për të marrë depot e përdoruesve duke përdorur Python. Në këtë artikull, unë shpjegoj procesin hap pas hapi për të trajtuar një kërkesë API për të marrë të dhëna nga..

Një udhëzues hap pas hapi për të zotëruar React

Në këtë artikull, do të mësoni se si të krijoni aplikacionin React, do të mësoni se si funksionon React dhe konceptet thelbësore që duhet të dini për të ndërtuar aplikacione React. Learning..

AI dhe Psikologjia — Pjesa 2

Në pjesën 2 të serisë sonë të AI dhe Psikologji ne diskutojmë se si makineritë mbledhin dhe përpunojnë të dhëna për të mësuar emocione dhe ndjenja të ndryshme në mendjen e njeriut, duke ndihmuar..

Esencialet e punës ditore të kodit tim VS

Shtesat e mia të preferuara - Git Graph 💹 Kjo shtesë është vërtet e mahnitshme, e përdor përpara se të filloj të punoj për të kontrolluar dy herë ndryshimet dhe degët më të fundit, mund të..

Pse Python? Zbulimi i fuqisë së gjithanshme të një gjiganti programues

Në peizazhin gjithnjë në zhvillim të gjuhëve të programimit, Python është shfaqur si një forcë dominuese. Rritja e tij meteorike nuk është rastësi. Joshja e Python qëndron në thjeshtësinë,..

Etiketa

Machine Learning JavaScript Data Science Artificial Intelligence Python Web Development Software Development Coding Deep Learning AI React Programming Languages Software Engineering Java Nodejs Algorithms Front End Development Data Tech Computer Science Development AWS HTML CSS Neural Networks Angular ChatGPT Python Programming Learning To Code Developer Code Typescript Tutorial NLP Open Source Productivity Computer Vision Linear Regression