explain.depesz.com

PostgreSQL's explain analyze made readable

Result: krhM : Optimization for: Optimization for: Optimization for: plan #1XCl; plan #HHnL; plan #3hbt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1.413 6,910.259 ↑ 1.5 5,551 1

Unique (cost=2,487.16..2,528.8 rows=8,328 width=423) (actual time=6,908.458..6,910.259 rows=5,551 loops=1)

  • Buffers: shared hit=254,573 read=489,830 dirtied=663 written=115,475
2. 29.914 6,908.846 ↑ 1.5 5,611 1

Sort (cost=2,487.16..2,507.98 rows=8,328 width=423) (actual time=6,908.455..6,908.846 rows=5,611 loops=1)

  • Sort Key: (COALESCE(NULLIF((i.match_uid)::text, ''::text), (i.identity_id)::text))
  • Sort Method: quicksort Memory: 4,631kB
  • Buffers: shared hit=254,573 read=489,830 dirtied=663 written=115,475
3. 14.139 6,878.932 ↑ 1.5 5,611 1

Nested Loop (cost=71.6..1,944.85 rows=8,328 width=423) (actual time=1,372.985..6,878.932 rows=5,611 loops=1)

  • Buffers: shared hit=254,573 read=489,830 dirtied=663 written=115,475
4. 0.029 0.029 ↑ 1.0 1 1

Seq Scan on geoserver_acl g (cost=0..1.05 rows=1 width=175) (actual time=0.018..0.029 rows=1 loops=1)

  • Filter: (g.username = 'cmaoi'::text)
  • Buffers: shared hit=1
5. 19.352 6,864.764 ↓ 467.6 5,611 1

Nested Loop (cost=71.6..1,673.02 rows=12 width=501) (actual time=1,372.918..6,864.764 rows=5,611 loops=1)

  • Buffers: shared hit=254,572 read=489,830 dirtied=663 written=115,475
6. 5,104.597 6,307.804 ↓ 668.7 8,024 1

Bitmap Heap Scan on identities i (cost=59.42..1,637.21 rows=12 width=213) (actual time=1,362.129..6,307.804 rows=8,024 loops=1)

  • Filter: (i.last_modified > '2020-09-10 13:23:05'::timestamp without time zone)
  • Heap Blocks: exact=482,115
  • Buffers: shared hit=62,716 read=471,818 dirtied=663 written=106,917
7. 1,203.207 1,203.207 ↓ 9,866.8 3,946,729 1

Bitmap Index Scan on identities_last_geopoint_idx (cost=0..59.41 rows=400 width=0) (actual time=1,203.207..1,203.207 rows=3,946,729 loops=1)

  • Index Cond: (i.last_geopoint && g.geometry)
  • Buffers: shared hit=3,309 read=49,110 written=709
8. 60.209 537.608 ↑ 1.0 1 8,024

Hash Join (cost=12.18..14.14 rows=1 width=288) (actual time=0.06..0.067 rows=1 loops=8,024)

  • Buffers: shared hit=191,856 read=18,012 written=8,558
9. 28.055 28.055 ↓ 1.0 70 5,611

Seq Scan on providers p (cost=0..1.69 rows=69 width=23) (actual time=0.001..0.005 rows=70 loops=5,611)

  • Buffers: shared hit=5,611
10. 8.024 449.344 ↑ 1.0 1 8,024

Hash (cost=12.17..12.17 rows=1 width=269) (actual time=0.056..0.056 rows=1 loops=8,024)

  • Buffers: shared hit=186,245 read=18,012 written=8,558
11. 8.024 441.320 ↑ 1.0 1 8,024

Subquery Scan on cr (cost=2.54..12.17 rows=1 width=269) (actual time=0.055..0.055 rows=1 loops=8,024)

  • Buffers: shared hit=186,245 read=18,012 written=8,558
12. 0.000 433.296 ↑ 1.0 1 8,024

Limit (cost=2.54..12.16 rows=1 width=329) (actual time=0.054..0.054 rows=1 loops=8,024)

  • Buffers: shared hit=186,245 read=18,012 written=8,558
13. 64.192 433.296 ↑ 34.0 1 8,024

Merge Append (cost=2.54..329.53 rows=34 width=329) (actual time=0.054..0.054 rows=1 loops=8,024)

  • Buffers: shared hit=186,245 read=18,012 written=8,558
14. 361.080 361.080 ↑ 22.0 1 8,024

Index Scan using contact_reports_p2020_09_10_identity_id_report_date_time_idx on contact_reports_p2020_09_10 c (cost=0.56..230.36 rows=22 width=329) (actual time=0.045..0.045 rows=1 loops=8,024)

  • Index Cond: ((c.identity_id = i.identity_id) AND (c.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c.provider_id = ANY (g.providers))
  • Buffers: shared hit=89,957 read=18,012 written=8,558
15. 8.024 8.024 ↓ 0.0 0 8,024

Index Scan using contact_reports_p2020_09_11_identity_id_report_date_time_idx on contact_reports_p2020_09_11 c_1 (cost=0.14..8.18 rows=1 width=329) (actual time=0.001..0.001 rows=0 loops=8,024)

  • Index Cond: ((c_1.identity_id = i.identity_id) AND (c_1.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_1.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
16. 0.000 0.000 ↓ 0.0 0 8,024

Index Scan using contact_reports_p2020_09_12_identity_id_report_date_time_idx on contact_reports_p2020_09_12 c_2 (cost=0.14..8.18 rows=1 width=329) (actual time=0..0 rows=0 loops=8,024)

  • Index Cond: ((c_2.identity_id = i.identity_id) AND (c_2.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_2.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
17. 0.000 0.000 ↓ 0.0 0 8,024

Index Scan using contact_reports_p2020_09_13_identity_id_report_date_time_idx on contact_reports_p2020_09_13 c_3 (cost=0.14..8.18 rows=1 width=329) (actual time=0..0 rows=0 loops=8,024)

  • Index Cond: ((c_3.identity_id = i.identity_id) AND (c_3.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_3.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
18. 0.000 0.000 ↓ 0.0 0 8,024

Index Scan using contact_reports_p2020_09_14_identity_id_report_date_time_idx on contact_reports_p2020_09_14 c_4 (cost=0.14..8.18 rows=1 width=329) (actual time=0..0 rows=0 loops=8,024)

  • Index Cond: ((c_4.identity_id = i.identity_id) AND (c_4.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_4.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
19. 0.000 0.000 ↓ 0.0 0 8,024

Index Scan using contact_reports_p2020_09_15_identity_id_report_date_time_idx on contact_reports_p2020_09_15 c_5 (cost=0.14..8.18 rows=1 width=329) (actual time=0..0 rows=0 loops=8,024)

  • Index Cond: ((c_5.identity_id = i.identity_id) AND (c_5.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_5.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
20. 0.000 0.000 ↓ 0.0 0 8,024

Index Scan using contact_reports_p2020_09_16_identity_id_report_date_time_idx on contact_reports_p2020_09_16 c_6 (cost=0.14..8.18 rows=1 width=329) (actual time=0..0 rows=0 loops=8,024)

  • Index Cond: ((c_6.identity_id = i.identity_id) AND (c_6.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_6.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
21. 0.000 0.000 ↓ 0.0 0 8,024

Index Scan using contact_reports_p2020_09_17_identity_id_report_date_time_idx on contact_reports_p2020_09_17 c_7 (cost=0.14..8.18 rows=1 width=329) (actual time=0..0 rows=0 loops=8,024)

  • Index Cond: ((c_7.identity_id = i.identity_id) AND (c_7.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_7.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
22. 0.000 0.000 ↓ 0.0 0 8,024

Index Scan using contact_reports_p2020_09_18_identity_id_report_date_time_idx on contact_reports_p2020_09_18 c_8 (cost=0.14..8.18 rows=1 width=329) (actual time=0..0 rows=0 loops=8,024)

  • Index Cond: ((c_8.identity_id = i.identity_id) AND (c_8.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_8.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
23. 0.000 0.000 ↓ 0.0 0 8,024

Index Scan using contact_reports_p2020_09_19_identity_id_report_date_time_idx on contact_reports_p2020_09_19 c_9 (cost=0.14..8.18 rows=1 width=329) (actual time=0..0 rows=0 loops=8,024)

  • Index Cond: ((c_9.identity_id = i.identity_id) AND (c_9.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_9.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
24. 0.000 0.000 ↓ 0.0 0 8,024

Index Scan using contact_reports_p2020_09_20_identity_id_report_date_time_idx on contact_reports_p2020_09_20 c_10 (cost=0.14..8.18 rows=1 width=329) (actual time=0..0 rows=0 loops=8,024)

  • Index Cond: ((c_10.identity_id = i.identity_id) AND (c_10.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_10.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
25. 0.000 0.000 ↓ 0.0 0 8,024

Index Scan using contact_reports_p2020_09_21_identity_id_report_date_time_idx on contact_reports_p2020_09_21 c_11 (cost=0.14..8.18 rows=1 width=329) (actual time=0..0 rows=0 loops=8,024)

  • Index Cond: ((c_11.identity_id = i.identity_id) AND (c_11.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_11.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
26. 0.000 0.000 ↓ 0.0 0 8,024

Index Scan using contact_reports_default_identity_id_report_date_time_idx on contact_reports_default c_12 (cost=0.14..8.18 rows=1 width=329) (actual time=0..0 rows=0 loops=8,024)

  • Index Cond: ((c_12.identity_id = i.identity_id) AND (c_12.report_date_time > '2020-09-10 11:23:05'::timestamp without time zone))
  • Filter: (c_12.provider_id = ANY (g.providers))
  • Buffers: shared hit=8,024
Planning time : 49.984 ms
Execution time : 6,917.637 ms