explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EBDV

Settings
# exclusive inclusive rows x rows loops node
1. 1.322 209,933.946 ↑ 1.6 223 1

HashAggregate (cost=148,818.15..148,822.58 rows=355 width=620) (actual time=209,933.789..209,933.946 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.165 209,932.624 ↑ 8.2 430 1

Append (cost=44,901.80..148,764.97 rows=3,545 width=620) (actual time=14,211.422..209,932.624 rows=430 loops=1)

3. 0.152 14,212.032 ↑ 15.7 223 1

Subquery Scan on *SELECT* 1 (cost=44,901.80..44,998.13 rows=3,503 width=620) (actual time=14,211.421..14,212.032 rows=223 loops=1)

4. 68.878 14,211.880 ↑ 15.7 223 1

HashAggregate (cost=44,901.80..44,963.10 rows=3,503 width=563) (actual time=14,211.419..14,211.880 rows=223 loops=1)

  • Group Key: cs.card_number, socs.name, cs.holder, cm.nomenclature_id
5. 16.392 14,143.002 ↓ 3.3 11,674 1

Nested Loop (cost=1.08..44,858.01 rows=3,503 width=563) (actual time=34.925..14,143.002 rows=11,674 loops=1)

6. 1.646 122.901 ↓ 1.4 183 1

Nested Loop Left Join (cost=0.65..1,413.13 rows=131 width=551) (actual time=5.407..122.901 rows=183 loops=1)

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

Index Scan using cards_card_number_idx on cards cs (cost=0.65..1,398.29 rows=131 width=37) (actual time=5.370..120.340 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.896 0.915 ↑ 1.0 7 183

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

9. 0.019 0.019 ↑ 1.0 7 1

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

10. 14,003.709 14,003.709 ↑ 1.2 64 183

Index Scan using card_moneybag_history_card_number_idx on card_moneybag_history cm (cost=0.43..330.88 rows=76 width=23) (actual time=6.717..76.523 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.100 195,720.427 ↓ 4.9 207 1

Subquery Scan on *SELECT* 2 (cost=103,765.69..103,766.84 rows=42 width=621) (actual time=195,720.073..195,720.427 rows=207 loops=1)

12. 632.274 195,720.327 ↓ 4.9 207 1

HashAggregate (cost=103,765.69..103,766.42 rows=42 width=564) (actual time=195,720.071..195,720.327 rows=207 loops=1)

  • Group Key: cs_1.card_number, socs_1.name, cs_1.holder, tn.written_off_nomenclature_id
13. 419.087 195,088.053 ↓ 515.0 45,317 1

Nested Loop Left Join (cost=1.44..103,764.59 rows=88 width=564) (actual time=38.166..195,088.053 rows=45,317 loops=1)

  • Join Filter: (cs_1.state = socs_1.id)
  • Rows Removed by Join Filter: 271902
14. 193.073 194,487.698 ↓ 515.0 45,317 1

Nested Loop (cost=1.44..103,754.26 rows=88 width=50) (actual time=38.107..194,487.698 rows=45,317 loops=1)

15. 11.760 11.760 ↓ 1.4 183 1

Index Scan using cards_card_number_idx on cards cs_1 (cost=0.65..1,398.29 rows=131 width=37) (actual time=0.058..11.760 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. 194,282.865 194,282.865 ↓ 124.0 248 183

Index Scan using transactions_new_entity_number_written_off_nomenclature_id_idx on transactions_new tn (cost=0.79..781.32 rows=2 width=24) (actual time=20.115..1,061.655 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. 181.251 181.268 ↑ 1.0 7 45,317

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

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

Planning time : 5.839 ms