explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 57aX

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 234,699.065 ↑ 1.0 1 1

Aggregate (cost=781,720.13..781,720.14 rows=1 width=32) (actual time=234,699.065..234,699.065 rows=1 loops=1)

2.          

CTE p

3. 0.006 115,486.347 ↑ 1.0 1 1

Aggregate (cost=390,854.20..390,854.21 rows=1 width=32) (actual time=115,486.346..115,486.347 rows=1 loops=1)

4. 1,039.828 115,486.341 ↓ 0.0 0 1

Nested Loop (cost=3,013.49..390,854.20 rows=1 width=5) (actual time=115,486.341..115,486.341 rows=0 loops=1)

5. 553.570 10,423.381 ↓ 216,264.3 2,811,436 1

Hash Join (cost=3,013.07..390,764.17 rows=13 width=21) (actual time=3,823.349..10,423.381 rows=2,811,436 loops=1)

  • Hash Cond: (t.foundersid = t3.id)
6. 869.872 9,869.788 ↓ 2,304.5 2,811,436 1

Nested Loop (cost=3,009.86..390,756.25 rows=1,220 width=37) (actual time=3,823.306..9,869.788 rows=2,811,436 loops=1)

7. 2.466 2.466 ↓ 6.0 6 1

Seq Scan on dic_bkd t1 (cost=0.00..432.05 rows=1 width=16) (actual time=0.244..2.466 rows=6 loops=1)

  • Filter: (code ~~ '10102%'::text)
  • Rows Removed by Filter: 3918
8. 7,732.338 8,997.450 ↓ 17.5 468,573 6

Bitmap Heap Scan on data_ufk t (cost=3,009.86..390,056.84 rows=26,736 width=53) (actual time=641.448..1,499.575 rows=468,573 loops=6)

  • Recheck Cond: (bkdid = t1.id)
  • Filter: ((date_otch >= '2019-01-01 00:00:00'::timestamp without time zone) AND (date_otch < '2020-01-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1838537
  • Heap Blocks: exact=1178315
9. 1,265.112 1,265.112 ↓ 17.6 2,307,109 6

Bitmap Index Scan on ix_data_ufk_bkdid (cost=0.00..3,003.18 rows=131,015 width=0) (actual time=210.852..210.852 rows=2,307,109 loops=6)

  • Index Cond: (bkdid = t1.id)
10. 0.003 0.023 ↑ 1.0 1 1

Hash (cost=3.20..3.20 rows=1 width=16) (actual time=0.023..0.023 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
11. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on "Founders" t3 (cost=0.00..3.20 rows=1 width=16) (actual time=0.011..0.020 rows=1 loops=1)

  • Filter: (code = '182'::text)
  • Rows Removed by Filter: 95
12. 104,023.132 104,023.132 ↓ 0.0 0 2,811,436

Index Scan using pk_company on company t2 (cost=0.42..6.92 rows=1 width=16) (actual time=0.037..0.037 rows=0 loops=2,811,436)

  • Index Cond: (id = t.companyid)
  • Filter: ((inn = ANY ('{2130098350,2420004523,2460201704,2464225590,2466224271,4325003121,4508001178,6601005477,6601006150,6601006262,6601006424,6601007450,6601007682,6601007700,6601007724,6601007731,6601007770,6601007788,6601007869,66 (...)
  • Rows Removed by Filter: 1
13.          

CTE q

14. 0.001 119,210.760 ↓ 0.0 0 1

Limit (cost=390,864.74..390,864.76 rows=1 width=43) (actual time=119,210.760..119,210.760 rows=0 loops=1)

15. 0.002 119,210.759 ↓ 0.0 0 1

GroupAggregate (cost=390,864.74..390,864.76 rows=1 width=43) (actual time=119,210.759..119,210.759 rows=0 loops=1)

  • Group Key: t2_1.inn
16. 0.012 119,210.757 ↓ 0.0 0 1

Sort (cost=390,864.74..390,864.75 rows=1 width=16) (actual time=119,210.757..119,210.757 rows=0 loops=1)

  • Sort Key: t2_1.inn
  • Sort Method: quicksort Memory: 25kB
17. 1,026.139 119,210.745 ↓ 0.0 0 1

Nested Loop (cost=3,010.28..390,864.73 rows=1 width=16) (actual time=119,210.745..119,210.745 rows=0 loops=1)

18. 288.767 11,350.038 ↓ 216,264.3 2,811,436 1

Nested Loop (cost=3,009.86..390,774.70 rows=13 width=21) (actual time=4,511.769..11,350.038 rows=2,811,436 loops=1)

  • Join Filter: (t_1.foundersid = t3_1.id)
19. 0.050 0.050 ↑ 1.0 1 1

Seq Scan on "Founders" t3_1 (cost=0.00..3.20 rows=1 width=16) (actual time=0.024..0.050 rows=1 loops=1)

  • Filter: (code = '182'::text)
  • Rows Removed by Filter: 95
20. 629.411 11,061.221 ↓ 2,304.5 2,811,436 1

Nested Loop (cost=3,009.86..390,756.25 rows=1,220 width=37) (actual time=4,511.740..11,061.221 rows=2,811,436 loops=1)

21. 2.004 2.004 ↓ 6.0 6 1

Seq Scan on dic_bkd t1_1 (cost=0.00..432.05 rows=1 width=16) (actual time=0.827..2.004 rows=6 loops=1)

  • Filter: (code ~~ '10102%'::text)
  • Rows Removed by Filter: 3918
22. 8,382.660 10,429.806 ↓ 17.5 468,573 6

Bitmap Heap Scan on data_ufk t_1 (cost=3,009.86..390,056.84 rows=26,736 width=53) (actual time=756.227..1,738.301 rows=468,573 loops=6)

  • Recheck Cond: (bkdid = t1_1.id)
  • Filter: ((date_otch >= '2019-01-01 00:00:00'::timestamp without time zone) AND (date_otch < '2020-01-01 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 1838537
  • Heap Blocks: exact=1178315
23. 2,047.146 2,047.146 ↓ 17.6 2,307,109 6

Bitmap Index Scan on ix_data_ufk_bkdid (cost=0.00..3,003.18 rows=131,015 width=0) (actual time=341.191..341.191 rows=2,307,109 loops=6)

  • Index Cond: (bkdid = t1_1.id)
24. 106,834.568 106,834.568 ↓ 0.0 0 2,811,436

Index Scan using pk_company on company t2_1 (cost=0.42..6.92 rows=1 width=27) (actual time=0.038..0.038 rows=0 loops=2,811,436)

  • Index Cond: (id = t_1.companyid)
  • Filter: ((inn = ANY ('{2130098350,2420004523,2460201704,2464225590,2466224271,4325003121,4508001178,6601005477,6601006150,6601006262,6601006424,6601007450,6601007682,6601007700,6601007724,6601007731,6601007770,6601007788,6 (...)
  • Rows Removed by Filter: 1
25.          

CTE qp

26. 0.015 234,697.131 ↑ 2.0 1 1

Sort (cost=0.05..0.06 rows=2 width=68) (actual time=234,697.130..234,697.131 rows=1 loops=1)

  • Sort Key: (0)
  • Sort Method: quicksort Memory: 25kB
27. 0.001 234,697.116 ↑ 2.0 1 1

Append (cost=0.00..0.04 rows=2 width=68) (actual time=234,697.114..234,697.116 rows=1 loops=1)

28. 119,210.761 119,210.761 ↓ 0.0 0 1

CTE Scan on q (cost=0.00..0.02 rows=1 width=68) (actual time=119,210.761..119,210.761 rows=0 loops=1)

29. 115,486.354 115,486.354 ↑ 1.0 1 1

CTE Scan on p (cost=0.00..0.02 rows=1 width=68) (actual time=115,486.352..115,486.354 rows=1 loops=1)

30.          

CTE f

31. 234,699.050 234,699.050 ↑ 2.0 1 1

CTE Scan on qp (cost=0.00..1.06 rows=2 width=64) (actual time=234,699.049..234,699.050 rows=1 loops=1)

32. 234,699.057 234,699.057 ↑ 2.0 1 1

CTE Scan on f (cost=0.00..0.04 rows=2 width=24) (actual time=234,699.057..234,699.057 rows=1 loops=1)

Planning time : 10.213 ms
Execution time : 234,699.489 ms