explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1XCl

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.625 7,491.051 ↓ 1.7 4,749 1

Unique (cost=2,006.99..2,020.87 rows=2,776 width=423) (actual time=7,488.883..7,491.051 rows=4,749 loops=1)

  • Buffers: shared hit=200,538 read=470,234 written=156,620
2. 20.934 7,489.426 ↓ 1.7 4,804 1

Sort (cost=2,006.99..2,013.93 rows=2,776 width=423) (actual time=7,488.879..7,489.426 rows=4,804 loops=1)

  • Sort Key: (COALESCE(NULLIF((i.match_uid)::text, ''::text), (i.identity_id)::text))
  • Sort Method: quicksort Memory: 3,946kB
  • Buffers: shared hit=200,538 read=470,234 written=156,620
3. 11.831 7,468.492 ↓ 1.7 4,804 1

Nested Loop (cost=71.24..1,848.22 rows=2,776 width=423) (actual time=1,929.84..7,468.492 rows=4,804 loops=1)

  • Buffers: shared hit=200,538 read=470,234 written=156,620
4. 0.028 0.028 ↑ 1.0 1 1

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

  • Filter: (g.username = 'cmaoi'::text)
  • Buffers: shared hit=1
5. 13.208 7,456.633 ↓ 1,201.0 4,804 1

Nested Loop (cost=71.24..1,756.91 rows=4 width=501) (actual time=1,929.785..7,456.633 rows=4,804 loops=1)

  • Buffers: shared hit=200,537 read=470,234 written=156,620
6. 5,393.577 7,179.205 ↓ 1,501.2 6,005 1

Bitmap Heap Scan on identities i (cost=59.42..1,737.21 rows=4 width=213) (actual time=1,915.263..7,179.205 rows=6,005 loops=1)

  • Filter: ((i.last_modified > '2020-09-10 13:23:05'::timestamp without time zone) AND _st_intersects(i.last_geopoint, g.geometry))
  • Heap Blocks: exact=473,783
  • Buffers: shared hit=57,455 read=468,544 written=155,586
7. 1,785.628 1,785.628 ↓ 9,717.9 3,887,162 1

Bitmap Index Scan on identities_last_geopoint_idx (cost=0..59.41 rows=400 width=0) (actual time=1,785.628..1,785.628 rows=3,887,162 loops=1)

  • Index Cond: (i.last_geopoint && g.geometry)
  • Buffers: shared hit=3,426 read=48,790 written=20,758
8. 54.045 264.220 ↑ 1.0 1 6,005

Hash Join (cost=11.83..13.79 rows=1 width=288) (actual time=0.036..0.044 rows=1 loops=6,005)

  • Buffers: shared hit=143,082 read=1,690 written=1,034
9. 24.020 24.020 ↓ 1.0 70 4,804

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

  • Buffers: shared hit=4,804
10. 0.000 186.155 ↑ 1.0 1 6,005

Hash (cost=11.82..11.82 rows=1 width=269) (actual time=0.031..0.031 rows=1 loops=6,005)

  • Buffers: shared hit=138,278 read=1,690 written=1,034
11. 6.005 186.155 ↑ 1.0 1 6,005

Subquery Scan on cr (cost=2.54..11.82 rows=1 width=269) (actual time=0.03..0.031 rows=1 loops=6,005)

  • Buffers: shared hit=138,278 read=1,690 written=1,034
12. 6.005 180.150 ↑ 1.0 1 6,005

Limit (cost=2.54..11.81 rows=1 width=329) (actual time=0.03..0.03 rows=1 loops=6,005)

  • Buffers: shared hit=138,278 read=1,690 written=1,034
13. 42.035 174.145 ↑ 34.0 1 6,005

Merge Append (cost=2.54..317.45 rows=34 width=329) (actual time=0.029..0.029 rows=1 loops=6,005)

  • Buffers: shared hit=138,278 read=1,690 written=1,034
14. 126.105 126.105 ↑ 22.0 1 6,005

Index Scan using contact_reports_p2020_09_10_identity_id_report_date_time_idx on contact_reports_p2020_09_10 c (cost=0.56..218.28 rows=22 width=329) (actual time=0.021..0.021 rows=1 loops=6,005)

  • 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=66,218 read=1,690 written=1,034
15. 6.005 6.005 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
16. 0.000 0.000 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
17. 0.000 0.000 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
18. 0.000 0.000 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
19. 0.000 0.000 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
20. 0.000 0.000 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
21. 0.000 0.000 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
22. 0.000 0.000 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
23. 0.000 0.000 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
24. 0.000 0.000 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
25. 0.000 0.000 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
26. 0.000 0.000 ↓ 0.0 0 6,005

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=6,005)

  • 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=6,005
Planning time : 46.728 ms
Execution time : 7,497.362 ms