explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1.659 1,795.312 ↑ 1.5 5,537 1

Unique (cost=2,486.77..2,528.41 rows=8,328 width=422) (actual time=1,793.16..1,795.312 rows=5,537 loops=1)

  • Buffers: shared hit=740,226
2. 21.877 1,793.653 ↑ 1.5 5,594 1

Sort (cost=2,486.77..2,507.59 rows=8,328 width=422) (actual time=1,793.157..1,793.653 rows=5,594 loops=1)

  • Sort Key: (COALESCE(NULLIF((i.match_uid)::text, ''::text), (i.identity_id)::text))
  • Sort Method: quicksort Memory: 4,618kB
  • Buffers: shared hit=740,226
3. 9.830 1,771.776 ↑ 1.5 5,594 1

Nested Loop (cost=71.21..1,944.46 rows=8,328 width=422) (actual time=804.104..1,771.776 rows=5,594 loops=1)

  • Buffers: shared hit=740,226
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.015..0.029 rows=1 loops=1)

  • Filter: (g.username = 'cmaoi'::text)
  • Buffers: shared hit=1
5. 17.946 1,761.917 ↓ 466.2 5,594 1

Nested Loop (cost=71.21..1,672.63 rows=12 width=501) (actual time=804.052..1,761.917 rows=5,594 loops=1)

  • Buffers: shared hit=740,225
6. 784.346 1,463.901 ↓ 666.8 8,002 1

Bitmap Heap Scan on identities i (cost=59.42..1,637.21 rows=12 width=213) (actual time=801.719..1,463.901 rows=8,002 loops=1)

  • Filter: (i.last_modified > '2020-09-10 13:23:05'::timestamp without time zone)
  • Heap Blocks: exact=479,051
  • Buffers: shared hit=531,389
7. 679.555 679.555 ↓ 9,812.0 3,924,810 1

Bitmap Index Scan on identities_last_geopoint_idx (cost=0..59.41 rows=400 width=0) (actual time=679.554..679.555 rows=3,924,810 loops=1)

  • Index Cond: (i.last_geopoint && g.geometry)
  • Buffers: shared hit=52,338
8. 52.050 280.070 ↑ 1.0 1 8,002

Hash Join (cost=11.79..13.75 rows=1 width=288) (actual time=0.029..0.035 rows=1 loops=8,002)

  • Buffers: shared hit=208,836
9. 27.970 27.970 ↓ 1.0 70 5,594

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

  • Buffers: shared hit=5,594
10. 0.000 200.050 ↑ 1.0 1 8,002

Hash (cost=11.78..11.78 rows=1 width=269) (actual time=0.025..0.025 rows=1 loops=8,002)

  • Buffers: shared hit=203,242
11. 0.000 200.050 ↑ 1.0 1 8,002

Subquery Scan on cr (cost=2.54..11.78 rows=1 width=269) (actual time=0.025..0.025 rows=1 loops=8,002)

  • Buffers: shared hit=203,242
12. 8.002 200.050 ↑ 1.0 1 8,002

Limit (cost=2.54..11.77 rows=1 width=329) (actual time=0.024..0.025 rows=1 loops=8,002)

  • Buffers: shared hit=203,242
13. 48.012 192.048 ↑ 35.0 1 8,002

Merge Append (cost=2.54..325.54 rows=35 width=329) (actual time=0.024..0.024 rows=1 loops=8,002)

  • Buffers: shared hit=203,242
14. 144.036 144.036 ↑ 23.0 1 8,002

Index Scan using contact_reports_p2020_09_10_identity_id_report_date_time_idx on contact_reports_p2020_09_10 c (cost=0.56..226.35 rows=23 width=329) (actual time=0.018..0.018 rows=1 loops=8,002)

  • 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=107,218
15. 0.000 0.000 ↓ 0.0 0 8,002

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..0 rows=0 loops=8,002)

  • 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,002
16. 0.000 0.000 ↓ 0.0 0 8,002

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,002)

  • 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,002
17. 0.000 0.000 ↓ 0.0 0 8,002

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,002)

  • 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,002
18. 0.000 0.000 ↓ 0.0 0 8,002

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,002)

  • 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,002
19. 0.000 0.000 ↓ 0.0 0 8,002

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,002)

  • 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,002
20. 0.000 0.000 ↓ 0.0 0 8,002

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,002)

  • 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,002
21. 0.000 0.000 ↓ 0.0 0 8,002

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,002)

  • 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,002
22. 0.000 0.000 ↓ 0.0 0 8,002

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,002)

  • 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,002
23. 0.000 0.000 ↓ 0.0 0 8,002

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,002)

  • 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,002
24. 0.000 0.000 ↓ 0.0 0 8,002

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,002)

  • 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,002
25. 0.000 0.000 ↓ 0.0 0 8,002

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,002)

  • 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,002
26. 0.000 0.000 ↓ 0.0 0 8,002

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,002)

  • 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,002
Planning time : 45.505 ms
Execution time : 1,802.567 ms