explain.depesz.com

PostgreSQL's explain analyze made readable

Result: x2ji

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 82.100 ↓ 0.0 0 1

Nested Loop Left Join (cost=17,042.36..17,080.95 rows=1 width=1,454) (actual time=82.100..82.100 rows=0 loops=1)

2.          

CTE dd

3. 0.095 73.641 ↓ 11.0 11 1

Nested Loop (cost=1,005.89..17,013.41 rows=1 width=47) (actual time=3.171..73.641 rows=11 loops=1)

  • Join Filter: ((b_1.inet_contract = it.contractid) OR (b_1.cardid = it.contractid))
  • Rows Removed by Join Filter: 727
4. 3.848 70.315 ↓ 3.0 3 1

Gather (cost=1,000.00..15,497.77 rows=1 width=12) (actual time=1.960..70.315 rows=3 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 66.467 66.467 ↑ 1.0 1 3

Parallel Seq Scan on internet_tbl it (cost=0.00..14,497.67 rows=1 width=12) (actual time=43.596..66.467 rows=1 loops=3)

  • Filter: (ponsn IS NOT NULL)
  • Rows Removed by Filter: 333332
6. 0.525 3.231 ↑ 1.1 246 3

Nested Loop (cost=5.89..1,511.76 rows=259 width=58) (actual time=0.057..1.077 rows=246 loops=3)

7. 0.114 0.186 ↑ 1.0 168 3

Bitmap Heap Scan on abaddress_tbl adres_1 (cost=5.60..294.80 rows=169 width=4) (actual time=0.030..0.062 rows=168 loops=3)

  • Recheck Cond: (cityid = 4561)
  • Heap Blocks: exact=18
8. 0.072 0.072 ↑ 1.0 168 3

Bitmap Index Scan on abaddress_tbl_cityid_idx (cost=0.00..5.56 rows=169 width=0) (actual time=0.024..0.024 rows=168 loops=3)

  • Index Cond: (cityid = 4561)
9. 2.520 2.520 ↑ 2.0 1 504

Index Scan using abservices_tbl_addressid_idx on abservices_tbl b_1 (cost=0.29..7.18 rows=2 width=58) (actual time=0.002..0.005 rows=1 loops=504)

  • Index Cond: (addressid = adres_1.recid)
  • Filter: ((cardid !~~* '00000%'::text) AND (abonatid <> 0))
10.          

CTE rr

11. 0.001 82.094 ↓ 0.0 0 1

GroupAggregate (cost=26.59..26.66 rows=1 width=84) (actual time=82.094..82.094 rows=0 loops=1)

  • Group Key: a.cardid, a.abonatid, a.contractid, a.addressid
12. 0.017 82.093 ↓ 0.0 0 1

Sort (cost=26.59..26.60 rows=1 width=80) (actual time=82.093..82.093 rows=0 loops=1)

  • Sort Key: a.cardid, a.abonatid, a.contractid, a.addressid
  • Sort Method: quicksort Memory: 25kB
13. 0.004 82.076 ↓ 0.0 0 1

Nested Loop Anti Join (cost=4.34..26.58 rows=1 width=80) (actual time=82.076..82.076 rows=0 loops=1)

14. 73.665 73.665 ↑ 1.0 1 1

CTE Scan on dd a (cost=0.00..0.08 rows=1 width=80) (actual time=71.942..73.665 rows=1 loops=1)

  • Filter: ((servid = ANY ('{1,3}'::integer[])) AND (last_tresorid <> ALL ('{1192,8905,9307,7330,5655,9308,8906,9306,735,9722,9305,8789,1784,455,1400,9302,1109,8910,9304,863,7575,8912,3
  • Rows Removed by Filter: 10
15. 0.038 8.407 ↑ 1.0 1 1

Bitmap Heap Scan on abservices_tbl absm (cost=4.34..27.19 rows=1 width=4) (actual time=8.407..8.407 rows=1 loops=1)

  • Recheck Cond: (abonatid = a.abonatid)
  • Filter: (servid = ANY ('{35,36,38,39,40}'::integer[]))
  • Rows Removed by Filter: 14
  • Heap Blocks: exact=9
16. 8.369 8.369 ↓ 14.2 85 1

Bitmap Index Scan on abserv_abonatid_idx (cost=0.00..4.33 rows=6 width=0) (actual time=8.369..8.369 rows=85 loops=1)

  • Index Cond: (abonatid = a.abonatid)
17.          

CTE qq

18. 82.095 82.095 ↓ 0.0 0 1

CTE Scan on rr (cost=0.00..0.03 rows=1 width=84) (actual time=82.095..82.095 rows=0 loops=1)

  • Filter: ((last_date >= '1538341201'::double precision) AND (last_date <= '1541023199'::double precision))
19. 0.000 82.099 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.97..23.74 rows=1 width=1,345) (actual time=82.099..82.099 rows=0 loops=1)

20. 0.001 82.099 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.70..23.44 rows=1 width=1,321) (actual time=82.099..82.099 rows=0 loops=1)

21. 0.000 82.098 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.42..23.09 rows=1 width=1,268) (actual time=82.098..82.098 rows=0 loops=1)

22. 0.000 82.098 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.12..22.75 rows=1 width=1,227) (actual time=82.098..82.098 rows=0 loops=1)

23. 82.098 82.098 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.98..22.07 rows=1 width=1,201) (actual time=82.098..82.098 rows=0 loops=1)

24. 0.000 75.089 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.57..8.76 rows=1 width=1,189) (actual time=75.089..75.089 rows=0 loops=1)

25. 0.001 75.089 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.29..8.32 rows=1 width=1,172) (actual time=75.088..75.089 rows=0 loops=1)

26. 75.088 75.088 ↓ 0.0 0 1

CTE Scan on qq q (cost=0.00..0.02 rows=1 width=84) (actual time=75.088..75.088 rows=0 loops=1)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using abonats_tbl_pkey on abonats_tbl abonat (cost=0.29..8.30 rows=1 width=1,088) (never executed)

  • Index Cond: (q.abonatid = abonatid)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using cities_id_idx on cities ab_grad (cost=0.28..0.42 rows=1 width=21) (never executed)

  • Index Cond: (abonat.cityid = id)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using abservices_cardservst_idx on abservices_tbl b (cost=0.41..11.53 rows=1 width=24) (never executed)

  • Index Cond: (q.cardid = cardid)
  • Filter: (q.last_date = CASE WHEN (char_length(((last_lmonth)::character varying(100))::text) = 6) THEN date_part('epoch'::text, ((to_timestamp((((''::text || (last_lmonth)::text) || '01'::text) || ''::text), 'yyyymmdd hh24:mi:ss'::text) + '1 mon'::interval) - '00:00:01'::interval)) ELSE (last_lmonth)::double precision END)
30. 0.000 0.000 ↓ 0.0 0

Seq Scan on contract_noms offers (cost=0.00..1.31 rows=31 width=20) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using nomenklaturi_id_idx on nomenklaturi oferta (cost=0.14..0.68 rows=1 width=34) (never executed)

  • Index Cond: (offers.nomid = id)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using abaddress_tbl_pkey on abaddress_tbl adres (cost=0.29..0.34 rows=1 width=41) (never executed)

  • Index Cond: (b.addressid = recid)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using cities_id_idx on cities mgrad (cost=0.28..0.34 rows=1 width=57) (never executed)

  • Index Cond: (adres.cityid = id)
34. 0.000 0.000 ↓ 0.0 0

Index Scan using quarts_pkey on quarts mquart (cost=0.28..0.29 rows=1 width=28) (never executed)

  • Index Cond: (adres.quartid = id)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using streets_pkey on streets mstreet (cost=0.29..0.35 rows=1 width=30) (never executed)

  • Index Cond: (adres.streetid = id)
36.          

SubPlan (forNested Loop Left Join)

37. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8.44..8.45 rows=1 width=32) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Index Scan using abservices_cardservst_idx on abservices_tbl (cost=0.41..8.43 rows=1 width=2) (never executed)

  • Index Cond: ((cardid = q.cardid) AND (servid = 5))
39. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=8.31..8.32 rows=1 width=32) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Index Scan using phones_abonatid_idx on phones (cost=0.29..8.30 rows=1 width=10) (never executed)

  • Index Cond: (abonatid = q.abonatid)
  • Filter: (status = 1)
  • Join Filter: ((q.cardid = offers.contractid) AND (q.abonatid = offers.abonatid))
41. 82.097 82.097 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.98..20.29 rows=1 width=1,197) (actual time=82.097..82.097 rows=0 loops=1)