explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HHnL : Optimization for: plan #1XCl

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.414 7,007.716 ↑ 25.0 4,778 1

Unique (cost=519,891.54..520,488.44 rows=119,379 width=461) (actual time=7,005.836..7,007.716 rows=4,778 loops=1)

  • Buffers: shared hit=4,584,868
2. 20.373 7,006.302 ↑ 24.7 4,834 1

Sort (cost=519,891.54..520,189.99 rows=119,379 width=461) (actual time=7,005.833..7,006.302 rows=4,834 loops=1)

  • Sort Key: (COALESCE(NULLIF((i.match_uid)::text, ''::text), (i.identity_id)::text))
  • Sort Method: quicksort Memory: 3,970kB
  • Buffers: shared hit=4,584,868
3. 13.078 6,985.929 ↑ 24.7 4,834 1

Nested Loop (cost=28.77..509,824.79 rows=119,379 width=461) (actual time=1.032..6,985.929 rows=4,834 loops=1)

  • Buffers: shared hit=4,584,868
4. 0.035 0.035 ↑ 1.0 1 1

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

  • Filter: (g.username = 'cmaoi'::text)
  • Buffers: shared hit=1
5. 249.690 6,972.816 ↑ 24.7 4,834 1

Nested Loop (cost=28.77..504,750.14 rows=119,379 width=440) (actual time=0.976..6,972.816 rows=4,834 loops=1)

  • Buffers: shared hit=4,584,867
6. 2,105.822 2,105.822 ↓ 1.0 121,508 1

Index Scan using identities_last_modified_idx1 on identities i (cost=0.43..269,694.39 rows=119,379 width=181) (actual time=0.037..2,105.822 rows=121,508 loops=1)

  • Index Cond: (i.last_modified > '2020-09-10 13:23:05'::timestamp without time zone)
  • Buffers: shared hit=399,005
7. 97.338 4,617.304 ↓ 0.0 0 121,508

Hash Join (cost=28.34..30.3 rows=1 width=259) (actual time=0.038..0.038 rows=0 loops=121,508)

  • Buffers: shared hit=4,185,862
8. 24.170 24.170 ↓ 1.0 70 4,834

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

  • Buffers: shared hit=4,834
9. 0.000 4,495.796 ↓ 0.0 0 121,508

Hash (cost=28.33..28.33 rows=1 width=240) (actual time=0.037..0.037 rows=0 loops=121,508)

  • Buffers: shared hit=4,181,028
10. 0.000 4,495.796 ↓ 0.0 0 121,508

Subquery Scan on cr (cost=2.54..28.33 rows=1 width=240) (actual time=0.037..0.037 rows=0 loops=121,508)

  • Buffers: shared hit=4,181,028
11. 121.508 4,495.796 ↓ 0.0 0 121,508

Limit (cost=2.54..28.32 rows=1 width=300) (actual time=0.037..0.037 rows=0 loops=121,508)

  • Buffers: shared hit=4,181,028
12. 729.048 4,374.288 ↓ 0.0 0 121,508

Merge Append (cost=2.54..337.65 rows=13 width=300) (actual time=0.036..0.036 rows=0 loops=121,508)

  • Buffers: shared hit=4,181,028
13. 3,645.240 3,645.240 ↓ 0.0 0 121,508

Index Scan using contact_reports_p2020_09_10_identity_id_report_date_time_idx on contact_reports_p2020_09_10 c (cost=0.56..235.94 rows=1 width=300) (actual time=0.03..0.03 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c.provider_id = ANY (g.providers)) AND _st_intersects(c.geopoint, g.geometry))
  • Buffers: shared hit=2,722,932
14. 0.000 0.000 ↓ 0.0 0 121,508

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.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_1.provider_id = ANY (g.providers)) AND _st_intersects(c_1.geopoint, g.geometry))
  • Buffers: shared hit=121,508
15. 0.000 0.000 ↓ 0.0 0 121,508

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.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_2.provider_id = ANY (g.providers)) AND _st_intersects(c_2.geopoint, g.geometry))
  • Buffers: shared hit=121,508
16. 0.000 0.000 ↓ 0.0 0 121,508

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.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_3.provider_id = ANY (g.providers)) AND _st_intersects(c_3.geopoint, g.geometry))
  • Buffers: shared hit=121,508
17. 0.000 0.000 ↓ 0.0 0 121,508

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.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_4.provider_id = ANY (g.providers)) AND _st_intersects(c_4.geopoint, g.geometry))
  • Buffers: shared hit=121,508
18. 0.000 0.000 ↓ 0.0 0 121,508

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.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_5.provider_id = ANY (g.providers)) AND _st_intersects(c_5.geopoint, g.geometry))
  • Buffers: shared hit=121,508
19. 0.000 0.000 ↓ 0.0 0 121,508

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.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_6.provider_id = ANY (g.providers)) AND _st_intersects(c_6.geopoint, g.geometry))
  • Buffers: shared hit=121,508
20. 0.000 0.000 ↓ 0.0 0 121,508

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.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_7.provider_id = ANY (g.providers)) AND _st_intersects(c_7.geopoint, g.geometry))
  • Buffers: shared hit=121,508
21. 0.000 0.000 ↓ 0.0 0 121,508

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.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_8.provider_id = ANY (g.providers)) AND _st_intersects(c_8.geopoint, g.geometry))
  • Buffers: shared hit=121,508
22. 0.000 0.000 ↓ 0.0 0 121,508

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.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_9.provider_id = ANY (g.providers)) AND _st_intersects(c_9.geopoint, g.geometry))
  • Buffers: shared hit=121,508
23. 0.000 0.000 ↓ 0.0 0 121,508

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.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_10.provider_id = ANY (g.providers)) AND _st_intersects(c_10.geopoint, g.geometry))
  • Buffers: shared hit=121,508
24. 0.000 0.000 ↓ 0.0 0 121,508

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.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_11.provider_id = ANY (g.providers)) AND _st_intersects(c_11.geopoint, g.geometry))
  • Buffers: shared hit=121,508
25. 0.000 0.000 ↓ 0.0 0 121,508

Index Scan using contact_reports_default_identity_id_report_date_time_idx on contact_reports_default c_12 (cost=0.14..8.43 rows=1 width=300) (actual time=0..0 rows=0 loops=121,508)

  • 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.geopoint && g.geometry) AND (c_12.provider_id = ANY (g.providers)) AND _st_intersects(c_12.geopoint, g.geometry))
  • Buffers: shared hit=121,508
Planning time : 69.263 ms
Execution time : 7,009.474 ms