explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BXC4

Settings
# exclusive inclusive rows x rows loops node
1. 0.012 2,948.910 ↑ 1.0 10 1

Limit (cost=161,015.00..479,161.59 rows=10 width=1,392) (actual time=1,393.791..2,948.910 rows=10 loops=1)

  • Buffers: shared hit=24,566, temp read=480 written=4,937
2. 0.122 2,948.898 ↑ 9.4 10 1

Nested Loop Left Join (cost=161,015.00..3,151,592.96 rows=94 width=1,392) (actual time=1,393.789..2,948.898 rows=10 loops=1)

  • Buffers: shared hit=24,566, temp read=480 written=4,937
3. 0.081 1,097.526 ↑ 9.4 10 1

Nested Loop (cost=140,121.36..1,187,587.98 rows=94 width=40) (actual time=1,097.238..1,097.526 rows=10 loops=1)

  • Buffers: shared hit=21,848, temp read=480 written=4,937
4. 0.017 1,097.245 ↑ 42,708.6 10 1

Unique (cost=140,120.79..147,313.91 rows=427,086 width=32) (actual time=1,097.181..1,097.245 rows=10 loops=1)

  • Buffers: shared hit=21,797, temp read=480 written=4,937
5. 827.949 1,097.228 ↑ 95,908.3 10 1

Sort (cost=140,120.79..142,518.50 rows=959,083 width=32) (actual time=1,097.176..1,097.228 rows=10 loops=1)

  • Sort Key: mdl_engage_actions_table_p1_yaddr_v4.organization_uuid, mdl_engage_actions_table_p1_yaddr_v4.person_uuid
  • Sort Method: external merge Disk: 39,432kB
  • Buffers: shared hit=21,797, temp read=480 written=4,937
6. 269.279 269.279 ↑ 1.0 959,083 1

Seq Scan on mdl_engage_actions_table_p1_yaddr_v4 (cost=0.00..31,387.83 rows=959,083 width=32) (actual time=0.020..269.279 rows=959,083 loops=1)

  • Buffers: shared hit=21,797
7. 0.200 0.200 ↑ 1.0 1 10

Index Scan using "t_person_dim$idx1" on t_person_dim_backend p (cost=0.56..2.43 rows=1 width=40) (actual time=0.020..0.020 rows=1 loops=10)

  • Index Cond: ((organization_uuid = mdl_engage_actions_table_p1_yaddr_v4.organization_uuid) AND (person_uuid = mdl_engage_actions_table_p1_yaddr_v4.person_uuid))
  • Filter: ((longitude IS NOT NULL) AND (latitude IS NOT NULL))
  • Buffers: shared hit=51
8. 1,826.940 1,851.250 ↑ 1.0 1 10

Aggregate (cost=20,893.64..20,893.65 rows=1 width=1,360) (actual time=185.124..185.125 rows=1 loops=10)

  • Buffers: shared hit=2,718
9. 24.310 24.310 ↑ 4.3 178 10

Index Scan using "mdl_engage_actions_table_p1_yaddr_v4$idx3" on mdl_engage_actions_table_p1_yaddr_v4 s (cost=0.67..864.84 rows=763 width=21) (actual time=0.061..2.431 rows=178 loops=10)

  • Index Cond: ((cube_enlarge((ll_to_earth((p.latitude)::double precision, (p.longitude)::double precision))::cube, '3999.99993444938'::double precision, 3) @> (ll_to_earth((latitude)::double precision, (longitude)::double precision))::cube) AND (activity_date > (now() - '2 years'::interval)) AND (p.person_uuid <> person_uuid))
  • Buffers: shared hit=2,715
Planning time : 31.704 ms
Execution time : 2,960.140 ms