explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pk6d : 9

Settings
# exclusive inclusive rows x rows loops node
1. 11.939 53,323.274 ↑ 1.0 1 1

Aggregate (cost=1,575,976.35..1,575,976.36 rows=1 width=88) (actual time=53,323.274..53,323.274 rows=1 loops=1)

  • Buffers: shared hit=22683214
2. 9.569 53,311.335 ↑ 11.6 5,967 1

GroupAggregate (cost=1,389,708.58..1,574,422.68 rows=69,052 width=544) (actual time=53,233.086..53,311.335 rows=5,967 loops=1)

  • Group Key: c.fid
  • Buffers: shared hit=22683204
3. 13.838 53,236.129 ↑ 3.2 21,820 1

Sort (cost=1,389,708.58..1,389,881.21 rows=69,052 width=28) (actual time=53,232.948..53,236.129 rows=21,820 loops=1)

  • Sort Key: c.fid
  • Sort Method: quicksort Memory: 2473kB
  • Buffers: shared hit=22657749
4. 3.887 53,222.291 ↑ 3.2 21,820 1

Result (cost=0.00..1,384,158.38 rows=69,052 width=28) (actual time=92.481..53,222.291 rows=21,820 loops=1)

  • Buffers: shared hit=22657746
5. 2.961 53,218.404 ↑ 3.2 21,820 1

Append (cost=0.00..1,383,467.86 rows=69,052 width=30) (actual time=92.480..53,218.404 rows=21,820 loops=1)

  • Buffers: shared hit=22657746
6. 0.000 0.010 ↓ 0.0 0 1

Seq Scan on sc c (cost=0.00..0.00 rows=1 width=30) (actual time=0.010..0.010 rows=0 loops=1)

  • Filter: ((fid IS NOT NULL) AND (nav_id IS NOT NULL) AND (slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (((qid = ANY ('{67,25,84,53}'::integer[])) AND (NOT (alternatives: SubPlan 2 or hashed SubPlan 3)) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone) AND (NOT o)) OR (o AND (qid = 564) AND (NOT (alternatives: SubPlan 4 or hashed SubPlan 5)) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone))))
7.          

SubPlan (forSeq Scan)

8. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..21.75 rows=4 width=0) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..21.71 rows=4 width=14) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Seq Scan on sc c_4 (cost=0.00..0.00 rows=1 width=14) (never executed)

  • Filter: ((fid IS NOT NULL) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone) AND (c.fid = fid) AND (slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
11. 0.000 0.000 ↓ 0.0 0

Index Scan using sc_o_d_slml_expr_fid_idx2 on sc_o_d c_5 (cost=0.42..4.96 rows=1 width=14) (never executed)

  • Index Cond: ((slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (fid IS NOT NULL) AND (c.fid = fid))
  • Filter: ((s_date >= '2019-05-23 00:00:00'::timestamp without time zone) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
12. 0.000 0.000 ↓ 0.0 0

Index Scan using sc_2019_hj_1_d_slml_expr_f_i_idx2 on sc_2019_hj_1_d c_6 (cost=0.56..14.23 rows=1 width=14) (never executed)

  • Index Cond: ((slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (fid IS NOT NULL) AND (c.fid = fid))
  • Filter: ((s_date >= '2019-05-23 00:00:00'::timestamp without time zone) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
13. 0.000 0.000 ↓ 0.0 0

Index Scan using sc_2019_hj_2_d_slml_qid_aus_idx on sc_2019_hj_2_d c_7 (cost=0.14..2.51 rows=1 width=14) (never executed)

  • Index Cond: ((slml = 5) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone))
  • Filter: ((fid IS NOT NULL) AND (c.fid = fid) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
14. 2.468 115.728 ↑ 1.8 13,779 2

Result (cost=0.00..14,262.83 rows=24,224 width=4) (actual time=0.258..57.864 rows=13,779 loops=2)

  • Buffers: shared hit=32587
15. 2.100 113.260 ↑ 1.8 13,779 2

Append (cost=0.00..14,020.59 rows=24,224 width=14) (actual time=0.257..56.630 rows=13,779 loops=2)

  • Buffers: shared hit=32587
16. 0.002 0.002 ↓ 0.0 0 2

Seq Scan on sc c_8 (cost=0.00..0.00 rows=1 width=14) (actual time=0.001..0.001 rows=0 loops=2)

  • Filter: ((fid IS NOT NULL) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone) AND (slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
17. 0.388 0.388 ↓ 0.0 0 2

Index Scan using sc_o_d_qid_s_date_idx on sc_o_d c_9 (cost=0.42..164.08 rows=1 width=14) (actual time=0.193..0.194 rows=0 loops=2)

  • Index Cond: ((qid = ANY ('{26,15,35,74,83}'::integer[])) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone))
  • Filter: ((fid IS NOT NULL) AND (slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])))
  • Buffers: shared hit=653
18. 110.754 110.754 ↑ 1.8 13,779 2

Index Scan using sc_2019_hj_1_d_slml_expr_q__idx1 on sc_2019_hj_1_d c_10 (cost=0.56..13,732.89 rows=24,221 width=14) (actual time=0.062..55.377 rows=13,779 loops=2)

  • Index Cond: ((slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone))
  • Filter: (fid IS NOT NULL)
  • Rows Removed by Filter: 2390
  • Buffers: shared hit=31932
19. 0.016 0.016 ↓ 0.0 0 2

Index Scan using sc_2019_hj_2_d_slml_qid_aus_idx on sc_2019_hj_2_d c_11 (cost=0.14..2.50 rows=1 width=14) (actual time=0.007..0.008 rows=0 loops=2)

  • Index Cond: ((slml = 5) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone))
  • Filter: ((fid IS NOT NULL) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
  • Buffers: shared hit=2
20. 49.194 52,834.356 ↑ 16.0 1 49,194

Result (cost=0.00..50.41 rows=16 width=0) (actual time=1.074..1.074 rows=1 loops=49,194)

  • Buffers: shared hit=22558915
21. 98.199 52,785.162 ↑ 16.0 1 49,194

Append (cost=0.00..50.25 rows=16 width=14) (actual time=1.073..1.073 rows=1 loops=49,194)

  • Buffers: shared hit=22558915
22. 0.000 0.000 ↓ 0.0 0 49,194

Seq Scan on sc c_12 (cost=0.00..0.00 rows=1 width=14) (actual time=0.000..0.000 rows=0 loops=49,194)

  • Filter: ((fid IS NOT NULL) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone) AND (c.fid = fid) AND (slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
23. 10,478.322 10,478.322 ↓ 0.0 0 49,194

Index Scan using sc_2018_hj_1_d_slml_expr_f_i_idx2 on sc_2018_hj_1_d c_13 (cost=0.56..12.95 rows=1 width=14) (actual time=0.213..0.213 rows=0 loops=49,194)

  • Index Cond: ((slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (fid IS NOT NULL) AND (c.fid = fid))
  • Filter: ((s_date >= '2018-06-06 00:00:00'::timestamp without time zone) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
  • Rows Removed by Filter: 173
  • Buffers: shared hit=6237680
24. 22,752.574 22,752.574 ↓ 0.0 0 28,229

Index Scan using sc_2018_hj_2_d_slml_expr_f_i_idx2 on sc_2018_hj_2_d c_14 (cost=0.56..15.53 rows=6 width=14) (actual time=0.806..0.806 rows=0 loops=28,229)

  • Index Cond: ((slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (fid IS NOT NULL) AND (c.fid = fid))
  • Filter: ((s_date >= '2018-06-06 00:00:00'::timestamp without time zone) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
  • Rows Removed by Filter: 500
  • Buffers: shared hit=8517265
25. 882.291 882.291 ↓ 0.0 0 16,647

Index Scan using sc_o_d_slml_expr_fid_idx2 on sc_o_d c_15 (cost=0.42..4.96 rows=1 width=14) (actual time=0.053..0.053 rows=0 loops=16,647)

  • Index Cond: ((slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (fid IS NOT NULL) AND (c.fid = fid))
  • Filter: ((s_date >= '2018-06-06 00:00:00'::timestamp without time zone) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
  • Rows Removed by Filter: 34
  • Buffers: shared hit=529215
26. 18,561.405 18,561.405 ↓ 0.0 0 16,647

Index Scan using sc_2019_hj_1_d_slml_expr_f_i_idx2 on sc_2019_hj_1_d c_16 (cost=0.56..14.23 rows=6 width=14) (actual time=1.115..1.115 rows=0 loops=16,647)

  • Index Cond: ((slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (fid IS NOT NULL) AND (c.fid = fid))
  • Filter: ((s_date >= '2018-06-06 00:00:00'::timestamp without time zone) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
  • Rows Removed by Filter: 639
  • Buffers: shared hit=7262384
27. 12.371 12.371 ↓ 0.0 0 12,371

Index Scan using sc_2019_hj_2_d_slml_qid_aus_idx on sc_2019_hj_2_d c_17 (cost=0.14..2.51 rows=1 width=14) (actual time=0.001..0.001 rows=0 loops=12,371)

  • Index Cond: ((slml = 5) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone))
  • Filter: ((fid IS NOT NULL) AND (c.fid = fid) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
  • Buffers: shared hit=12371
28. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..350,950.21 rows=893,172 width=4) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..342,018.49 rows=893,172 width=14) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on sc c_18 (cost=0.00..0.00 rows=1 width=14) (never executed)

  • Filter: ((fid IS NOT NULL) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone) AND (slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
31. 0.000 0.000 ↓ 0.0 0

Index Scan using sc_2018_hj_1_d_slml_expr_q__idx1 on sc_2018_hj_1_d c_19 (cost=0.56..31,135.03 rows=62,466 width=14) (never executed)

  • Index Cond: ((slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone))
  • Filter: (fid IS NOT NULL)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using sc_2018_hj_2_d_slml_expr_q__idx1 on sc_2018_hj_2_d c_20 (cost=0.56..158,342.82 rows=430,796 width=14) (never executed)

  • Index Cond: ((slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone))
  • Filter: (fid IS NOT NULL)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using sc_o_d_qid_s_date_idx on sc_o_d c_21 (cost=0.42..164.08 rows=1 width=14) (never executed)

  • Index Cond: ((qid = ANY ('{26,15,35,74,83}'::integer[])) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone))
  • Filter: ((fid IS NOT NULL) AND (slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])))
34. 0.000 0.000 ↓ 0.0 0

Index Scan using sc_2019_hj_1_d_slml_expr_q__idx1 on sc_2019_hj_1_d c_22 (cost=0.56..147,908.20 rows=399,907 width=14) (never executed)

  • Index Cond: ((slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone))
  • Filter: (fid IS NOT NULL)
35. 0.000 0.000 ↓ 0.0 0

Index Scan using sc_2019_hj_2_d_slml_qid_aus_idx on sc_2019_hj_2_d c_23 (cost=0.14..2.50 rows=1 width=14) (never executed)

  • Index Cond: ((slml = 5) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone))
  • Filter: ((fid IS NOT NULL) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{26,15,35,74,83}'::integer[])))
36. 53,062.698 53,062.698 ↑ 3.9 12,371 1

Index Scan using sc_o_d_slml_expr_qid__idx2 on sc_o_d c_1 (cost=0.42..897,351.95 rows=48,443 width=30) (actual time=92.469..53,062.698 rows=12,371 loops=1)

  • Index Cond: ((slml = 5) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])))
  • Filter: ((fid IS NOT NULL) AND (nav_id IS NOT NULL) AND (((qid = ANY ('{67,25,84,53}'::integer[])) AND (NOT (alternatives: SubPlan 2 or hashed SubPlan 3)) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone) AND (NOT o)) OR (o AND (qid = 564) AND (NOT (alternatives: SubPlan 4 or hashed SubPlan 5)) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone))))
  • Rows Removed by Filter: 36823
  • Buffers: shared hit=22617426
37. 143.214 152.720 ↑ 2.2 9,449 1

Bitmap Heap Scan on sc_2019_hj_1_d c_2 (cost=1,749.30..485,759.23 rows=20,607 width=30) (actual time=43.306..152.720 rows=9,449 loops=1)

  • Recheck Cond: ((((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{67,25,84,53}'::integer[])) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone)) OR ((slml = 5) AND o AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[]))))
  • Filter: ((fid IS NOT NULL) AND (nav_id IS NOT NULL) AND (slml = 5) AND (((qid = ANY ('{67,25,84,53}'::integer[])) AND (NOT (alternatives: SubPlan 2 or hashed SubPlan 3)) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone) AND (NOT o)) OR (o AND (qid = 564) AND (NOT (alternatives: SubPlan 4 or hashed SubPlan 5)) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone))))
  • Rows Removed by Filter: 25190
  • Heap Blocks: exact=22679
  • Buffers: shared hit=40319
38. 0.003 9.506 ↓ 0.0 0 1

BitmapOr (cost=1,749.30..1,749.30 rows=48,052 width=0) (actual time=9.506..9.506 rows=0 loops=1)

  • Buffers: shared hit=1350
39. 9.463 9.463 ↑ 1.1 42,836 1

Bitmap Index Scan on sc_2019_hj_1_d_expr_qid_s_date_idx1 (cost=0.00..1,735.91 rows=48,052 width=0) (actual time=9.463..9.463 rows=42,836 loops=1)

  • Index Cond: (((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (qid = ANY ('{67,25,84,53}'::integer[])) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=1345
40. 0.040 0.040 ↓ 0.0 0 1

Bitmap Index Scan on sc_2019_hj_1_d_slml_o_expr_idx1 (cost=0.00..3.09 rows=1 width=0) (actual time=0.040..0.040 rows=0 loops=1)

  • Index Cond: ((slml = 5) AND (o = true) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])))
  • Buffers: shared hit=5
41. 0.015 0.015 ↓ 0.0 0 1

Index Scan using sc_2019_hj_2_d_slml_qid_aus_idx on sc_2019_hj_2_d c_3 (cost=0.14..11.43 rows=1 width=30) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: (slml = 5)
  • Filter: ((fid IS NOT NULL) AND (nav_id IS NOT NULL) AND ((COALESCE((fid)::bigint, fk) % '12'::bigint) = ANY ('{9,9}'::bigint[])) AND (((qid = ANY ('{67,25,84,53}'::integer[])) AND (NOT (alternatives: SubPlan 2 or hashed SubPlan 3)) AND (s_date >= '2019-05-23 00:00:00'::timestamp without time zone) AND (NOT o)) OR (o AND (qid = 564) AND (NOT (alternatives: SubPlan 4 or hashed SubPlan 5)) AND (s_date >= '2018-06-06 00:00:00'::timestamp without time zone))))
  • Buffers: shared hit=1
42.          

SubPlan (forGroupAggregate)

43. 65.637 65.637 ↑ 1.0 1 5,967

Index Scan using id_idx on f (cost=0.43..2.65 rows=1 width=24) (actual time=0.010..0.011 rows=1 loops=5,967)

  • Index Cond: (id = c.fid)
  • Buffers: shared hit=25455
Planning time : 223.194 ms
Execution time : 53,324.657 ms