explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TrNt

Settings
# exclusive inclusive rows x rows loops node
1. 0.498 59,633.332 ↑ 2.7 74 1

HashAggregate (cost=20,052.46..20,054.96 rows=200 width=620) (actual time=59,633.262..59,633.332 rows=74 loops=1)

  • Group Key: "*SELECT* 1".card_number, "*SELECT* 1".state, "*SELECT* 1".holder, "*SELECT* 1".recalculation, "*SELECT* 1".nomenclature_id
2. 0.058 59,632.834 ↑ 6.2 147 1

Append (cost=12,066.37..20,038.78 rows=912 width=620) (actual time=8,780.102..59,632.834 rows=147 loops=1)

3. 0.037 8,780.252 ↑ 12.3 74 1

Subquery Scan on *SELECT* 1 (cost=12,066.37..12,091.36 rows=909 width=620) (actual time=8,780.101..8,780.252 rows=74 loops=1)

4. 39.237 8,780.215 ↑ 12.3 74 1

HashAggregate (cost=12,066.37..12,082.27 rows=909 width=563) (actual time=8,780.099..8,780.215 rows=74 loops=1)

  • Group Key: cs.card_number, socs.name, cs.holder, cm.nomenclature_id
5. 9.081 8,740.978 ↓ 6.4 5,839 1

Nested Loop (cost=0.91..12,055.00 rows=909 width=563) (actual time=36.296..8,740.978 rows=5,839 loops=1)

6. 0.599 49.225 ↓ 1.4 48 1

Nested Loop Left Join (cost=0.48..396.91 rows=34 width=551) (actual time=7.222..49.225 rows=48 loops=1)

  • Join Filter: (cs.state = socs.id)
  • Rows Removed by Join Filter: 288
7. 48.338 48.338 ↓ 1.4 48 1

Index Scan using cards_card_number_idx on cards cs (cost=0.48..392.25 rows=34 width=37) (actual time=7.163..48.338 rows=48 loops=1)

  • Index Cond: ((card_number)::text = ANY ('{0881433564,0881451369,0881464198,0881471098,0882287932,0882290338,2618480057,2618491497,2618502169,2618502761,2618504377,2618512633,3166173993,3166514281,3647260793,3647365977,3655132633,3655136889,3655146425,3655151033,3655160777,3655169785,3655184457,3655189593,3655193753,3655203433,3655204857,3655212985,3655216889,3655218249,3655219641,3655223817,3655226729,3655267385,3655279337,3655286681,3655365721,3655371257,3655430649,3655451145,3655455177,3655463049,3655468233,3655481241,3655487753,3655501449,3655512937,3655527065}'::text[]))
  • Filter: ((work_scheme = 1) AND (deleted = 0))
8. 0.271 0.288 ↑ 1.0 7 48

Materialize (cost=0.00..1.10 rows=7 width=518) (actual time=0.002..0.006 rows=7 loops=48)

9. 0.017 0.017 ↑ 1.0 7 1

Seq Scan on set_of_card_statuses socs (cost=0.00..1.07 rows=7 width=518) (actual time=0.009..0.017 rows=7 loops=1)

10. 8,682.672 8,682.672 ↓ 1.6 122 48

Index Scan using card_moneybag_history_card_number_idx on card_moneybag_history cm (cost=0.43..342.13 rows=76 width=23) (actual time=10.562..180.889 rows=122 loops=48)

  • Index Cond: ((card_number)::text = (cs.card_number)::text)
  • Filter: ((action = ANY ('{9,10}'::integer[])) AND (date_start <= '2019-11-29'::date))
  • Rows Removed by Filter: 2
11. 0.039 50,852.524 ↓ 24.3 73 1

Subquery Scan on *SELECT* 2 (cost=7,947.34..7,947.42 rows=3 width=621) (actual time=50,852.362..50,852.524 rows=73 loops=1)

12. 176.509 50,852.485 ↓ 24.3 73 1

HashAggregate (cost=7,947.34..7,947.39 rows=3 width=564) (actual time=50,852.359..50,852.485 rows=73 loops=1)

  • Group Key: cs_1.card_number, socs_1.name, cs_1.holder, tn.written_off_nomenclature_id
13. 140.874 50,675.976 ↓ 3,157.3 18,944 1

Nested Loop Left Join (cost=1.10..7,947.26 rows=6 width=564) (actual time=7.775..50,675.976 rows=18,944 loops=1)

  • Join Filter: (cs_1.state = socs_1.id)
  • Rows Removed by Join Filter: 113664
14. 40.678 50,478.270 ↓ 3,157.3 18,944 1

Nested Loop (cost=1.10..7,945.54 rows=6 width=50) (actual time=7.720..50,478.270 rows=18,944 loops=1)

15. 2.168 2.168 ↓ 1.4 48 1

Index Scan using cards_card_number_idx on cards cs_1 (cost=0.48..392.25 rows=34 width=37) (actual time=0.046..2.168 rows=48 loops=1)

  • Index Cond: ((card_number)::text = ANY ('{0881433564,0881451369,0881464198,0881471098,0882287932,0882290338,2618480057,2618491497,2618502169,2618502761,2618504377,2618512633,3166173993,3166514281,3647260793,3647365977,3655132633,3655136889,3655146425,3655151033,3655160777,3655169785,3655184457,3655189593,3655193753,3655203433,3655204857,3655212985,3655216889,3655218249,3655219641,3655223817,3655226729,3655267385,3655279337,3655286681,3655365721,3655371257,3655430649,3655451145,3655455177,3655463049,3655468233,3655481241,3655487753,3655501449,3655512937,3655527065}'::text[]))
  • Filter: ((work_scheme = 1) AND (deleted = 0))
16. 50,435.424 50,435.424 ↓ 395.0 395 48

Index Scan using transactions_new_entity_number_transaction_date_idx on transactions_new tn (cost=0.62..222.15 rows=1 width=24) (actual time=4.866..1,050.738 rows=395 loops=48)

  • Index Cond: (((entity_number)::text = (cs_1.card_number)::text) AND ((entity_number)::text = ANY ('{0881433564,0881451369,0881464198,0881471098,0882287932,0882290338,2618480057,2618491497,2618502169,2618502761,2618504377,2618512633,3166173993,3166514281,3647260793,3647365977,3655132633,3655136889,3655146425,3655151033,3655160777,3655169785,3655184457,3655189593,3655193753,3655203433,3655204857,3655212985,3655216889,3655218249,3655219641,3655223817,3655226729,3655267385,3655279337,3655286681,3655365721,3655371257,3655430649,3655451145,3655455177,3655463049,3655468233,3655481241,3655487753,3655501449,3655512937,3655527065}'::text[])) AND (transaction_date <= '2019-11-29'::date))
  • Filter: ((state_id = ANY ('{1,3}'::integer[])) AND (transaction_type = ANY ('{1,2}'::integer[])))
17. 56.816 56.832 ↑ 1.0 7 18,944

Materialize (cost=0.00..1.10 rows=7 width=518) (actual time=0.001..0.003 rows=7 loops=18,944)

18. 0.016 0.016 ↑ 1.0 7 1

Seq Scan on set_of_card_statuses socs_1 (cost=0.00..1.07 rows=7 width=518) (actual time=0.010..0.016 rows=7 loops=1)

Planning time : 77.853 ms