explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZuYP

Settings
# exclusive inclusive rows x rows loops node
1. 0.546 95,753.790 ↑ 2.7 74 1
→

HashAggregate (cost=20,060.47..20,062.97 rows=200 width=620) (actual time=95,753.716..95,753.790 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.064 95,753.244 ↑ 6.2 147 1
→

Append (cost=12,066.37..20,046.79 rows=912 width=620) (actual time=11,069.956..95,753.244 rows=147 loops=1)

3. 0.050 11,070.157 ↑ 12.3 74 1
→

Subquery Scan on *SELECT* 1 (cost=12,066.37..12,091.36 rows=909 width=620) (actual time=11,069.956..11,070.157 rows=74 loops=1)

4. 46.020 11,070.107 ↑ 12.3 74 1
→

HashAggregate (cost=12,066.37..12,082.27 rows=909 width=563) (actual time=11,069.953..11,070.107 rows=74 loops=1)

  • Group Key: cs.card_number, socs.name, cs.holder, cm.nomenclature_id
5. 10.786 11,024.087 ↓ 6.4 5,839 1
→

Nested Loop (cost=0.91..12,055.00 rows=909 width=563) (actual time=36.866..11,024.087 rows=5,839 loops=1)

6. 0.484 85.189 ↓ 1.4 48 1
→

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

  • Join Filter: (cs.state = socs.id)
  • Rows Removed by Join Filter: 288
7. 84.417 84.417 ↓ 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.754..84.417 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.277 0.288 ↑ 1.0 7 48
→

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

9. 0.011 0.011 ↑ 1.0 7 1
→

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

10. 10,928.112 10,928.112 ↓ 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=11.828..227.669 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.045 84,683.023 ↓ 24.3 73 1
→

Subquery Scan on *SELECT* 2 (cost=7,955.34..7,955.42 rows=3 width=621) (actual time=84,682.859..84,683.023 rows=73 loops=1)

12. 274.505 84,682.978 ↓ 24.3 73 1
→

HashAggregate (cost=7,955.34..7,955.39 rows=3 width=564) (actual time=84,682.856..84,682.978 rows=73 loops=1)

  • Group Key: cs_1.card_number, socs_1.name, cs_1.holder, tn.written_off_nomenclature_id
13. 180.629 84,408.473 ↓ 3,157.3 18,944 1
→

Nested Loop Left Join (cost=1.10..7,955.27 rows=6 width=564) (actual time=41.000..84,408.473 rows=18,944 loops=1)

  • Join Filter: (cs_1.state = socs_1.id)
  • Rows Removed by Join Filter: 113,664
14. 61.775 84,152.068 ↓ 3,157.3 18,944 1
→

Nested Loop (cost=1.10..7,953.55 rows=6 width=50) (actual time=40.939..84,152.068 rows=18,944 loops=1)

15. 2.837 2.837 ↓ 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.059..2.837 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. 84,087.456 84,087.456 ↓ 395.0 395 48
→

Index Scan using transactions_new_entity_number_written_off_nomenclature_id_idx on transactions_new tn (cost=0.62..222.38 rows=1 width=24) (actual time=24.696..1,751.822 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[])))
  • Filter: ((state_id = ANY ('{1,3}'::integer[])) AND (transaction_type = ANY ('{1,2}'::integer[])) AND (transaction_date <= '2019-11-29'::date))
17. 75.761 75.776 ↑ 1.0 7 18,944
→

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

18. 0.015 0.015 ↑ 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.011..0.015 rows=7 loops=1)