explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vOmy

Settings
# exclusive inclusive rows x rows loops node
1. 0.712 34,858.617 ↓ 14.8 74 1
→

HashAggregate (cost=612.01..612.07 rows=5 width=621) (actual time=34,858.541..34,858.617 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.053 34,857.905 ↓ 18.8 94 1
→

Append (cost=8.98..611.93 rows=5 width=621) (actual time=30.029..34,857.905 rows=94 loops=1)

3. 0.068 147.762 ↓ 24.7 74 1
→

Subquery Scan on *SELECT* 1 (cost=8.98..79.47 rows=3 width=621) (actual time=30.027..147.762 rows=74 loops=1)

4. 0.530 147.694 ↓ 24.7 74 1
→

Nested Loop (cost=8.98..79.44 rows=3 width=563) (actual time=30.026..147.694 rows=74 loops=1)

5. 0.136 11.350 ↓ 14.0 14 1
→

Nested Loop Left Join (cost=4.53..59.30 rows=1 width=551) (actual time=10.774..11.350 rows=14 loops=1)

  • Join Filter: (cs.state = socs.id)
  • Rows Removed by Join Filter: 84
6. 0.420 11.116 ↓ 14.0 14 1
→

Bitmap Heap Scan on cards cs (cost=4.53..58.15 rows=1 width=37) (actual time=10.752..11.116 rows=14 loops=1)

  • Recheck Cond: ((customer_id)::text = 'КЭМКТ00365'::text)
  • Filter: ((work_scheme = 2) AND (deleted = 0) AND (state = ANY ('{1,2,3,4,6}'::smallint[])) AND ((card_number)::text = ANY ('{0193921676,0236906414,0236959150,0241204398,0242825134,0243019182,0246297774,0246469294,0248238766,0248712622,0250662318,0251511726,3057125390,3060967022}'::text[])))
  • Heap Blocks: exact=13
7. 10.696 10.696 ↑ 1.0 14 1
→

Bitmap Index Scan on cards_customer_id_idx (cost=0.00..4.53 rows=14 width=0) (actual time=10.696..10.696 rows=14 loops=1)

  • Index Cond: ((customer_id)::text = 'КЭМКТ00365'::text)
8. 0.098 0.098 ↑ 1.0 7 14
→

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

9. 74.102 135.814 ↓ 1.2 5 14
→

Bitmap Heap Scan on card_moneybag cm (cost=4.45..20.08 rows=4 width=23) (actual time=7.071..9.701 rows=5 loops=14)

  • Recheck Cond: ((card_number)::text = (cs.card_number)::text)
  • Heap Blocks: exact=27
10. 61.712 61.712 ↓ 1.2 5 14
→

Bitmap Index Scan on card_moneybag_pkey (cost=0.00..4.45 rows=4 width=0) (actual time=4.408..4.408 rows=5 loops=14)

  • Index Cond: ((card_number)::text = (cs.card_number)::text)
11. 0.007 2,029.622 ↓ 10.0 10 1
→

Subquery Scan on *SELECT* 2 (cost=405.12..405.14 rows=1 width=620) (actual time=2,029.602..2,029.622 rows=10 loops=1)

12. 0.437 2,029.615 ↓ 10.0 10 1
→

HashAggregate (cost=405.12..405.13 rows=1 width=563) (actual time=2,029.599..2,029.615 rows=10 loops=1)

  • Group Key: cs_1.card_number, socs_1.name, cs_1.holder, cm_1.nomenclature_id
13. 0.363 2,029.178 ↓ 24.0 24 1
→

Nested Loop Left Join (cost=4.96..405.10 rows=1 width=563) (actual time=392.989..2,029.178 rows=24 loops=1)

  • Join Filter: (cs_1.state = socs_1.id)
  • Rows Removed by Join Filter: 144
14. 0.199 2,028.623 ↓ 24.0 24 1
→

Nested Loop (cost=4.96..403.95 rows=1 width=49) (actual time=392.959..2,028.623 rows=24 loops=1)

15. 0.274 0.328 ↓ 14.0 14 1
→

Bitmap Heap Scan on cards cs_1 (cost=4.53..58.06 rows=1 width=37) (actual time=0.075..0.328 rows=14 loops=1)

  • Recheck Cond: ((customer_id)::text = 'КЭМКТ00365'::text)
  • Filter: ((work_scheme = 2) AND (deleted = 0) AND ((card_number)::text = ANY ('{0193921676,0236906414,0236959150,0241204398,0242825134,0243019182,0246297774,0246469294,0248238766,0248712622,0250662318,0251511726,3057125390,3060967022}'::text[])))
  • Heap Blocks: exact=13
16. 0.054 0.054 ↑ 1.0 14 1
→

Bitmap Index Scan on cards_customer_id_idx (cost=0.00..4.53 rows=14 width=0) (actual time=0.054..0.054 rows=14 loops=1)

  • Index Cond: ((customer_id)::text = 'КЭМКТ00365'::text)
17. 2,028.096 2,028.096 ↓ 2.0 2 14
→

Index Scan using card_moneybag_history_card_number_idx on card_moneybag_history cm_1 (cost=0.43..345.88 rows=1 width=23) (actual time=142.238..144.864 rows=2 loops=14)

  • Index Cond: ((card_number)::text = (cs_1.card_number)::text)
  • Filter: ((date_start >= '2019-11-01'::date) AND (date_start <= '2019-11-29'::date) AND (action = 3))
  • Rows Removed by Filter: 61
18. 0.192 0.192 ↑ 1.0 7 24
→

Seq Scan on set_of_card_statuses socs_1 (cost=0.00..1.07 rows=7 width=518) (actual time=0.006..0.008 rows=7 loops=24)

19. 0.006 32,680.468 ↓ 10.0 10 1
→

Subquery Scan on *SELECT* 3 (cost=127.29..127.32 rows=1 width=621) (actual time=32,680.445..32,680.468 rows=10 loops=1)

20. 1.886 32,680.462 ↓ 10.0 10 1
→

HashAggregate (cost=127.29..127.31 rows=1 width=564) (actual time=32,680.444..32,680.462 rows=10 loops=1)

  • Group Key: cs_2.card_number, socs_2.name, cs_2.holder, tn.written_off_nomenclature_id
21. 1.974 32,678.576 ↓ 106.0 106 1
→

Nested Loop Left Join (cost=5.10..127.28 rows=1 width=564) (actual time=3,345.470..32,678.576 rows=106 loops=1)

  • Join Filter: (cs_2.state = socs_2.id)
  • Rows Removed by Join Filter: 636
22. 1.057 32,675.330 ↓ 106.0 106 1
→

Nested Loop (cost=5.10..126.12 rows=1 width=50) (actual time=3,345.439..32,675.330 rows=106 loops=1)

23. 0.282 0.373 ↓ 14.0 14 1
→

Bitmap Heap Scan on cards cs_2 (cost=4.53..58.06 rows=1 width=37) (actual time=0.122..0.373 rows=14 loops=1)

  • Recheck Cond: ((customer_id)::text = 'КЭМКТ00365'::text)
  • Filter: ((work_scheme = 2) AND (deleted = 0) AND ((card_number)::text = ANY ('{0193921676,0236906414,0236959150,0241204398,0242825134,0243019182,0246297774,0246469294,0248238766,0248712622,0250662318,0251511726,3057125390,3060967022}'::text[])))
  • Heap Blocks: exact=13
24. 0.091 0.091 ↑ 1.0 14 1
→

Bitmap Index Scan on cards_customer_id_idx (cost=0.00..4.53 rows=14 width=0) (actual time=0.091..0.091 rows=14 loops=1)

  • Index Cond: ((customer_id)::text = 'КЭМКТ00365'::text)
25. 32,673.900 32,673.900 ↓ 8.0 8 14
→

Index Scan using transactions_new_entity_number_written_off_nomenclature_id_idx on transactions_new tn (cost=0.58..68.05 rows=1 width=24) (actual time=2,015.610..2,333.850 rows=8 loops=14)

  • Index Cond: (((entity_number)::text = (cs_2.card_number)::text) AND ((entity_number)::text = ANY ('{0193921676,0236906414,0236959150,0241204398,0242825134,0243019182,0246297774,0246469294,0248238766,0248712622,0250662318,0251511726,3057125390,3060967022}'::text[])))
  • Filter: ((state_id = ANY ('{1,3}'::integer[])) AND (transaction_type = ANY ('{1,2}'::integer[])) AND (transaction_date >= '2019-11-01'::date) AND (transaction_date <= '2019-11-29'::date))
  • Rows Removed by Filter: 507
26. 1.272 1.272 ↑ 1.0 7 106
→

Seq Scan on set_of_card_statuses socs_2 (cost=0.00..1.07 rows=7 width=518) (actual time=0.010..0.012 rows=7 loops=106)

Planning time : 247.163 ms