explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PndV

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Custom Scan (HypertableInsert) (cost=25,990,877.21..26,005,801.07 rows=426,396 width=358) (actual rows= loops=)

2.          

CTE raw

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=6,764,117.17..20,262,164.79 rows=1,065,986 width=305) (actual rows= loops=)

  • Group Key: (time_bucket('00:15:00'::interval, dns."time")), dns.orgid, dns.networkid, dns.client_id, dns.client_type, dns.domain, dns.local_ipv4_address, dns.local_ipv6_address, dns.client_mac, dns.method
4.          

Initplan (forGroupAggregate)

5. 0.000 0.000 ↓ 0.0

Seq Scan on rollup_meta (cost=0.00..1.12 rows=1 width=8) (actual rows= loops=)

  • Filter: (rollup_name = 'rollup_top_domains'::name)
6. 0.000 0.000 ↓ 0.0

Sort (cost=6,764,116.04..6,790,765.69 rows=10,659,858 width=272) (actual rows= loops=)

  • Sort Key: (time_bucket('00:15:00'::interval, dns."time")), dns.orgid, dns.networkid, dns.client_id, dns.client_type, dns.domain, dns.local_ipv4_address, dns.local_ipv6_address, dns.client_mac, dns.method
7. 0.000 0.000 ↓ 0.0

Result (cost=0.00..1,475,512.12 rows=10,659,858 width=272) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Append (cost=0.00..1,342,263.89 rows=10,659,858 width=272) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on dnsquery dns (cost=0.00..0.00 rows=1 width=332) (actual rows= loops=)

  • Filter: (id > $4)
10. 0.000 0.000 ↓ 0.0

Seq Scan on _hyper_1_1_chunk dns_1 (cost=0.00..1,892.18 rows=15,605 width=265) (actual rows= loops=)

  • Filter: (id > $4)
11. 0.000 0.000 ↓ 0.0

Seq Scan on _hyper_1_2_chunk dns_2 (cost=0.00..913,537.20 rows=7,556,672 width=265) (actual rows= loops=)

  • Filter: (id > $4)
12. 0.000 0.000 ↓ 0.0

Seq Scan on _hyper_1_37_chunk dns_3 (cost=0.00..15,054.00 rows=124,347 width=289) (actual rows= loops=)

  • Filter: (id > $4)
13. 0.000 0.000 ↓ 0.0

Seq Scan on _hyper_1_3_chunk dns_4 (cost=0.00..358,481.22 rows=2,963,233 width=289) (actual rows= loops=)

  • Filter: (id > $4)
14.          

SubPlan (forGroupAggregate)

15. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.78..3.03 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest(((('{'::text || string_agg(DISTINCT array_to_string(dns.blockallowcats, ','::text), ','::text)) || '}'::text))::integer[])
16. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.78..3.03 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest(((('{'::text || string_agg(DISTINCT array_to_string(dns.blockcats, ','::text), ','::text)) || '}'::text))::integer[])
19. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.78..3.03 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest(((('{'::text || string_agg(DISTINCT array_to_string(dns.secallowcats, ','::text), ','::text)) || '}'::text))::integer[])
22. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.78..3.03 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest(((('{'::text || string_agg(DISTINCT array_to_string(dns.seccats, ','::text), ','::text)) || '}'::text))::integer[])
25. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

27.          

CTE data

28. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=463,253.94..5,709,523.46 rows=426,396 width=394) (actual rows= loops=)

  • Group Key: raw.rollup_time, raw.orgid, raw.networkid, raw.client_id, raw.client_type, raw.domain, raw.local_ipv4_address, raw.local_ipv6_address, raw.client_mac, raw.security, raw.allowed
29. 0.000 0.000 ↓ 0.0

Sort (cost=463,253.94..465,918.91 rows=1,065,986 width=342) (actual rows= loops=)

  • Sort Key: raw.rollup_time, raw.orgid, raw.networkid, raw.client_id, raw.client_type, raw.domain, raw.local_ipv4_address, raw.local_ipv6_address, raw.client_mac, raw.security, raw.allowed
30. 0.000 0.000 ↓ 0.0

CTE Scan on raw (cost=0.00..21,319.72 rows=1,065,986 width=342) (actual rows= loops=)

31.          

SubPlan (forGroupAggregate)

32. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.78..3.03 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest(((('{'::text || string_agg(DISTINCT array_to_string(CASE WHEN (array_length(raw.blockallowcats, 1) IS NULL) THEN NULL::integer[] ELSE raw.blockallowcats END, ','::text), ','::text)) || '}'::text))::integer[])
33. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.78..3.03 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest(((('{'::text || string_agg(DISTINCT array_to_string(CASE WHEN (array_length(raw.blockcats, 1) IS NULL) THEN NULL::integer[] ELSE raw.blockcats END, ','::text), ','::text)) || '}'::text))::integer[])
36. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.78..3.03 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest(((('{'::text || string_agg(DISTINCT array_to_string(CASE WHEN (array_length(raw.secallowcats, 1) IS NULL) THEN NULL::integer[] ELSE raw.secallowcats END, ','::text), ','::text)) || '}'::text))::integer[])
39. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.78..3.03 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest(((('{'::text || string_agg(DISTINCT array_to_string(CASE WHEN (array_length(raw.seccats, 1) IS NULL) THEN NULL::integer[] ELSE raw.seccats END, ','::text), ','::text)) || '}'::text))::integer[])
42. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

44.          

CTE upd

45. 0.000 0.000 ↓ 0.0

Update on rollup_meta rollup_meta_1 (cost=19,187.84..19,188.97 rows=1 width=78) (actual rows= loops=)

46.          

Initplan (forUpdate)

47. 0.000 0.000 ↓ 0.0

Aggregate (cost=9,593.91..9,593.92 rows=1 width=8) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

CTE Scan on data data_1 (cost=0.00..8,527.92 rows=426,396 width=8) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Aggregate (cost=9,593.91..9,593.92 rows=1 width=8) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

CTE Scan on data data_2 (cost=0.00..8,527.92 rows=426,396 width=8) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on rollup_meta rollup_meta_1 (cost=0.00..1.12 rows=1 width=78) (actual rows= loops=)

  • Filter: (rollup_name = 'rollup_top_domains'::name)
52. 0.000 0.000 ↓ 0.0

Insert on rollup_top_domains (cost=0.00..14,923.86 rows=426,396 width=358) (actual rows= loops=)

  • Conflict Resolution: UPDATE
  • Conflict Arbiter Indexes: rollup_top_domains_time_orgid_networkid_domain_security_all_idx
53. 0.000 0.000 ↓ 0.0

Custom Scan (ChunkDispatch) (cost=0.00..14,923.86 rows=426,396 width=358) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

CTE Scan on data (cost=0.00..14,923.86 rows=426,396 width=358) (actual rows= loops=)

55.          

SubPlan (forInsert)

56. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest((rollup_top_domains.blockcats || excluded.blockcats))
57. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest((rollup_top_domains.blockcats || excluded.blockcats))
60. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest((rollup_top_domains.blockallowcats || excluded.blockallowcats))
63. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest((rollup_top_domains.blockallowcats || excluded.blockallowcats))
66. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest((rollup_top_domains.seccats || excluded.seccats))
69. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest((rollup_top_domains.seccats || excluded.seccats))
72. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest((rollup_top_domains.secallowcats || excluded.secallowcats))
75. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest((rollup_top_domains.secallowcats || excluded.secallowcats))
78. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest((rollup_top_domains.methods || excluded.methods))
81. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.77..2.27 rows=100 width=4) (actual rows= loops=)

  • Group Key: unnest((rollup_top_domains.methods || excluded.methods))
84. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)