explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xCDX

Settings
# exclusive inclusive rows x rows loops node
1. 0.203 4,359.087 ↑ 2.7 74 1

HashAggregate (cost=20,507.45..20,509.95 rows=200 width=620) (actual time=4,359.052..4,359.087 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.018 4,358.884 ↑ 6.1 147 1

Append (cost=12,281.48..20,494.02 rows=895 width=620) (actual time=14.850..4,358.884 rows=147 loops=1)

3. 0.010 14.908 ↑ 12.1 74 1

Subquery Scan on *SELECT* 1 (cost=12,281.48..12,306.01 rows=892 width=620) (actual time=14.850..14.908 rows=74 loops=1)

4. 4.911 14.898 ↑ 12.1 74 1

HashAggregate (cost=12,281.48..12,297.09 rows=892 width=563) (actual time=14.848..14.898 rows=74 loops=1)

  • Group Key: cs.card_number, socs.name, cs.holder, cm.nomenclature_id
5. 0.966 9.987 ↓ 6.6 5,843 1

Nested Loop (cost=0.91..12,270.33 rows=892 width=563) (actual time=0.083..9.987 rows=5,843 loops=1)

6. 0.080 0.813 ↓ 1.4 48 1

Nested Loop Left Join (cost=0.48..402.04 rows=35 width=551) (actual time=0.041..0.813 rows=48 loops=1)

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

Index Scan using cards_card_number_idx on cards cs (cost=0.48..397.28 rows=35 width=37) (actual time=0.024..0.685 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.041 0.048 ↑ 1.0 7 48

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

9. 0.007 0.007 ↑ 1.0 7 1

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

10. 8.208 8.208 ↓ 1.7 122 48

Index Scan using card_moneybag_history_card_number_idx on card_moneybag_history cm (cost=0.43..338.36 rows=73 width=23) (actual time=0.023..0.171 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.015 4,343.958 ↓ 24.3 73 1

Subquery Scan on *SELECT* 2 (cost=8,187.93..8,188.02 rows=3 width=621) (actual time=4,343.904..4,343.958 rows=73 loops=1)

12. 45.100 4,343.943 ↓ 24.3 73 1

HashAggregate (cost=8,187.93..8,187.99 rows=3 width=564) (actual time=4,343.903..4,343.943 rows=73 loops=1)

  • Group Key: cs_1.card_number, socs_1.name, cs_1.holder, tn.written_off_nomenclature_id
13. 32.624 4,298.843 ↓ 3,158.3 18,950 1

Nested Loop Left Join (cost=1.10..8,187.86 rows=6 width=564) (actual time=0.134..4,298.843 rows=18,950 loops=1)

  • Join Filter: (cs_1.state = socs_1.id)
  • Rows Removed by Join Filter: 113700
14. 9.905 4,247.269 ↓ 3,158.3 18,950 1

Nested Loop (cost=1.10..8,186.14 rows=6 width=50) (actual time=0.117..4,247.269 rows=18,950 loops=1)

15. 1.076 1.076 ↓ 1.4 48 1

Index Scan using cards_card_number_idx on cards cs_1 (cost=0.48..397.28 rows=35 width=37) (actual time=0.022..1.076 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. 4,236.288 4,236.288 ↓ 395.0 395 48

Index Scan using transactions_new_entity_number_written_off_nomenclature_id_idx on transactions_new tn (cost=0.62..222.53 rows=1 width=24) (actual time=0.525..88.256 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. 18.944 18.950 ↑ 1.0 7 18,950

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

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

Planning time : 2.772 ms