explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DJkJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.604 14,795.009 ↑ 1.5 223 1

HashAggregate (cost=150,746.10..150,750.36 rows=341 width=620) (actual time=14,794.950..14,795.009 rows=223 loops=1)

  • Group Key: "*SELECT* 1".card_number, "*SELECT* 1".state, "*SELECT* 1".holder, "*SELECT* 1".recalculation, "*SELECT* 1".nomenclature_id
2. 0.038 14,794.405 ↑ 7.9 430 1

Append (cost=45,047.06..150,694.97 rows=3,409 width=620) (actual time=895.816..14,794.405 rows=430 loops=1)

3. 0.034 896.014 ↑ 15.1 223 1

Subquery Scan on *SELECT* 1 (cost=45,047.06..45,139.63 rows=3,366 width=620) (actual time=895.816..896.014 rows=223 loops=1)

4. 18.308 895.980 ↑ 15.1 223 1

HashAggregate (cost=45,047.06..45,105.97 rows=3,366 width=563) (actual time=895.814..895.980 rows=223 loops=1)

  • Group Key: cs.card_number, socs.name, cs.holder, cm.nomenclature_id
5. 3.790 877.672 ↓ 3.5 11,674 1

Nested Loop (cost=1.08..45,004.99 rows=3,366 width=563) (actual time=1.354..877.672 rows=11,674 loops=1)

6. 0.459 32.814 ↓ 1.4 183 1

Nested Loop Left Join (cost=0.65..1,480.83 rows=132 width=551) (actual time=0.109..32.814 rows=183 loops=1)

  • Join Filter: (cs.state = socs.id)
  • Rows Removed by Join Filter: 1098
7. 31.989 31.989 ↓ 1.4 183 1

Index Scan using cards_card_number_idx on cards cs (cost=0.65..1,465.88 rows=132 width=37) (actual time=0.069..31.989 rows=183 loops=1)

  • Index Cond: ((card_number)::text = ANY ('{0109400716,0194012476,0238151086,0576777269,0881432384,0881433147,0881433954,0881435651,0881437322,0881438202,0881438997,0881439006,0881440822,0881440839,0881442800,0881442877,0881443337,0881444283,0881444309,0881444598,0881447561,0881448517,0881449758,0881449780,0881449849,0881451985,0881453073,0881453770,0881455035,0881455084,0881455798,0881456524,0881457009,0881460181,0881460331,0881460921,0881462892,0881465492,0881467390,0881468972,0881469693,0881470444,0881471900,0881475102,0882037881,0882040322,0882041068,0882044526,0882047124,0882052098,0882062843,0882069821,0882074908,0882078610,0882079402,0882271297,0882273380,0882273844,0882273869,0882273968,0882275187,0882278204,0882278471,0882279146,0882280491,0882282214,0882282529,0882284101,0882289922,0882291099,0882292516,0882293650,0882299296,0882301113,0882301196,0882302972,0882303642,0882306362,0882306609,0882307134,0882309126,0882309362,0882310857,0882312656,0882312980,0882312988,1047790090,1321257162,1321438218,3056885598,3056885950,3056886686,3056894958,3056895502,3056903710,3056903838,3056912606,3056914990,3056940654,3056968718,3056968942,3056969550,3056980830,3056980878,3056981038,3057025646,3057039374,3057049918,3057050126,3166147961,3166153465,3166176057,3166178201,3166182473,3166184681,3166191449,3166193801,3166198537,3166200985,3166213561,3166224121,3166246153,3166257577,3166281017,3166284057,3166292985,3166323129,3166329129,3166359465,3166383561,3166407609,3166416537,3166425385,3166445609,3166459609,3166491065,3166498393,3166500729,3166509929,3166518521,3166526345,3166528265,3166541065,3647266937,3647275977,3647333353,3647391529,3647418297,3653931065,3653949561,3653951641,3653964489,3653973769,3653978569,3654094665,3654133913,3654155065,3654185193,3654223145,3654242713,3654312681,3655145785,3655151689,3655165721,3655225193,3655277673,3655284377,3655296473,3655301721,3655319865,3655329833,3655371977,3655396953,3655436537,3655453225,3655476569,3655509177,3655509945,3655511081,3655515513,3655518777,3655518921,3655526073}'::text[]))
  • Filter: ((work_scheme = 1) AND (deleted = 0))
8. 0.342 0.366 ↑ 1.0 7 183

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

9. 0.024 0.024 ↑ 1.0 7 1

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

10. 841.068 841.068 ↑ 1.1 64 183

Index Scan using card_moneybag_history_card_number_idx on card_moneybag_history cm (cost=0.43..329.00 rows=73 width=23) (actual time=0.517..4.596 rows=64 loops=183)

  • 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: 1
11. 0.048 13,898.353 ↓ 4.8 207 1

Subquery Scan on *SELECT* 2 (cost=105,554.16..105,555.34 rows=43 width=621) (actual time=13,898.165..13,898.353 rows=207 loops=1)

12. 137.277 13,898.305 ↓ 4.8 207 1

HashAggregate (cost=105,554.16..105,554.91 rows=43 width=564) (actual time=13,898.163..13,898.305 rows=207 loops=1)

  • Group Key: cs_1.card_number, socs_1.name, cs_1.holder, tn.written_off_nomenclature_id
13. 94.586 13,761.028 ↓ 498.2 45,334 1

Nested Loop Left Join (cost=1.44..105,553.02 rows=91 width=564) (actual time=1.903..13,761.028 rows=45,334 loops=1)

  • Join Filter: (cs_1.state = socs_1.id)
  • Rows Removed by Join Filter: 272004
14. 59.666 13,621.108 ↓ 498.2 45,334 1

Nested Loop (cost=1.44..105,542.38 rows=91 width=50) (actual time=1.884..13,621.108 rows=45,334 loops=1)

15. 5.168 5.168 ↓ 1.4 183 1

Index Scan using cards_card_number_idx on cards cs_1 (cost=0.65..1,465.88 rows=132 width=37) (actual time=0.023..5.168 rows=183 loops=1)

  • Index Cond: ((card_number)::text = ANY ('{0109400716,0194012476,0238151086,0576777269,0881432384,0881433147,0881433954,0881435651,0881437322,0881438202,0881438997,0881439006,0881440822,0881440839,0881442800,0881442877,0881443337,0881444283,0881444309,0881444598,0881447561,0881448517,0881449758,0881449780,0881449849,0881451985,0881453073,0881453770,0881455035,0881455084,0881455798,0881456524,0881457009,0881460181,0881460331,0881460921,0881462892,0881465492,0881467390,0881468972,0881469693,0881470444,0881471900,0881475102,0882037881,0882040322,0882041068,0882044526,0882047124,0882052098,0882062843,0882069821,0882074908,0882078610,0882079402,0882271297,0882273380,0882273844,0882273869,0882273968,0882275187,0882278204,0882278471,0882279146,0882280491,0882282214,0882282529,0882284101,0882289922,0882291099,0882292516,0882293650,0882299296,0882301113,0882301196,0882302972,0882303642,0882306362,0882306609,0882307134,0882309126,0882309362,0882310857,0882312656,0882312980,0882312988,1047790090,1321257162,1321438218,3056885598,3056885950,3056886686,3056894958,3056895502,3056903710,3056903838,3056912606,3056914990,3056940654,3056968718,3056968942,3056969550,3056980830,3056980878,3056981038,3057025646,3057039374,3057049918,3057050126,3166147961,3166153465,3166176057,3166178201,3166182473,3166184681,3166191449,3166193801,3166198537,3166200985,3166213561,3166224121,3166246153,3166257577,3166281017,3166284057,3166292985,3166323129,3166329129,3166359465,3166383561,3166407609,3166416537,3166425385,3166445609,3166459609,3166491065,3166498393,3166500729,3166509929,3166518521,3166526345,3166528265,3166541065,3647266937,3647275977,3647333353,3647391529,3647418297,3653931065,3653949561,3653951641,3653964489,3653973769,3653978569,3654094665,3654133913,3654155065,3654185193,3654223145,3654242713,3654312681,3655145785,3655151689,3655165721,3655225193,3655277673,3655284377,3655296473,3655301721,3655319865,3655329833,3655371977,3655396953,3655436537,3655453225,3655476569,3655509177,3655509945,3655511081,3655515513,3655518777,3655518921,3655526073}'::text[]))
  • Filter: ((work_scheme = 1) AND (deleted = 0))
16. 13,556.274 13,556.274 ↓ 124.0 248 183

Index Scan using transactions_new_entity_number_written_off_nomenclature_id_idx on transactions_new tn (cost=0.79..788.44 rows=2 width=24) (actual time=0.987..74.078 rows=248 loops=183)

  • Index Cond: (((entity_number)::text = (cs_1.card_number)::text) AND ((entity_number)::text = ANY ('{0109400716,0194012476,0238151086,0576777269,0881432384,0881433147,0881433954,0881435651,0881437322,0881438202,0881438997,0881439006,0881440822,0881440839,0881442800,0881442877,0881443337,0881444283,0881444309,0881444598,0881447561,0881448517,0881449758,0881449780,0881449849,0881451985,0881453073,0881453770,0881455035,0881455084,0881455798,0881456524,0881457009,0881460181,0881460331,0881460921,0881462892,0881465492,0881467390,0881468972,0881469693,0881470444,0881471900,0881475102,0882037881,0882040322,0882041068,0882044526,0882047124,0882052098,0882062843,0882069821,0882074908,0882078610,0882079402,0882271297,0882273380,0882273844,0882273869,0882273968,0882275187,0882278204,0882278471,0882279146,0882280491,0882282214,0882282529,0882284101,0882289922,0882291099,0882292516,0882293650,0882299296,0882301113,0882301196,0882302972,0882303642,0882306362,0882306609,0882307134,0882309126,0882309362,0882310857,0882312656,0882312980,0882312988,1047790090,1321257162,1321438218,3056885598,3056885950,3056886686,3056894958,3056895502,3056903710,3056903838,3056912606,3056914990,3056940654,3056968718,3056968942,3056969550,3056980830,3056980878,3056981038,3057025646,3057039374,3057049918,3057050126,3166147961,3166153465,3166176057,3166178201,3166182473,3166184681,3166191449,3166193801,3166198537,3166200985,3166213561,3166224121,3166246153,3166257577,3166281017,3166284057,3166292985,3166323129,3166329129,3166359465,3166383561,3166407609,3166416537,3166425385,3166445609,3166459609,3166491065,3166498393,3166500729,3166509929,3166518521,3166526345,3166528265,3166541065,3647266937,3647275977,3647333353,3647391529,3647418297,3653931065,3653949561,3653951641,3653964489,3653973769,3653978569,3654094665,3654133913,3654155065,3654185193,3654223145,3654242713,3654312681,3655145785,3655151689,3655165721,3655225193,3655277673,3655284377,3655296473,3655301721,3655319865,3655329833,3655371977,3655396953,3655436537,3655453225,3655476569,3655509177,3655509945,3655511081,3655515513,3655518777,3655518921,3655526073}'::text[])))
  • Filter: ((state_id = ANY ('{1,3}'::integer[])) AND (transaction_type = ANY ('{1,2}'::integer[])) AND (transaction_date <= '2019-11-29'::date))
17. 45.325 45.334 ↑ 1.0 7 45,334

Materialize (cost=0.00..1.10 rows=7 width=518) (actual time=0.000..0.001 rows=7 loops=45,334)

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

Planning time : 33.410 ms