explain.depesz.com

PostgreSQL's explain analyze made readable

Result: I49P : Optimization for: plan #HVAW

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.424 40.413 ↑ 1.0 5,000 1

Limit (cost=2.38..31,773.77 rows=5,000 width=266) (actual time=0.075..40.413 rows=5,000 loops=1)

  • Buffers: shared hit=24,513 read=51
2. 3.388 39.989 ↑ 239.6 5,000 1

Nested Loop (cost=2.38..7,611,244.96 rows=1,197,814 width=266) (actual time=0.073..39.989 rows=5,000 loops=1)

  • Buffers: shared hit=24,513 read=51
3. 1.724 11.601 ↑ 239.6 5,000 1

Hash Join (cost=1.96..278,909.66 rows=1,197,814 width=273) (actual time=0.052..11.601 rows=5,000 loops=1)

  • Buffers: shared hit=948 read=40
4. 0.456 9.861 ↑ 239.6 5,000 1

Append (cost=0.44..275,184.5 rows=1,197,814 width=254) (actual time=0.028..9.861 rows=5,000 loops=1)

  • Buffers: shared hit=947 read=40
5. 0.005 0.005 ↓ 0.0 0 1

Index Scan using contact_reports_p2019_09_30_received_date_time_idx on contact_reports_p2019_09_30 cr (cost=0.44..4.46 rows=1 width=253) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (cr.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone)
  • Filter: ((cr.geopoint && '010300000001000000060000000000000080DF38C036EA5FC4E42D454000000000B03966C0F479E7FAED74444000000000B03966C0F479E7FAED7444400000000090D463C00C69492ED93255405A000000006C0BC066465B2EE23455400000000080DF38C036EA5FC4E42D4540'::geometry) AND (cr.provider_id = ANY ('{12055157,12055159}'::integer[])))
  • Buffers: shared hit=3
6. 9.400 9.400 ↑ 239.6 5,000 1

Index Scan using contact_reports_p2019_10_01_received_date_time_idx on contact_reports_p2019_10_01 cr_1 (cost=0.44..269,129.03 rows=1,197,807 width=254) (actual time=0.023..9.4 rows=5,000 loops=1)

  • Index Cond: (cr_1.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone)
  • Filter: ((cr_1.geopoint && '010300000001000000060000000000000080DF38C036EA5FC4E42D454000000000B03966C0F479E7FAED74444000000000B03966C0F479E7FAED7444400000000090D463C00C69492ED93255405A000000006C0BC066465B2EE23455400000000080DF38C036EA5FC4E42D4540'::geometry) AND (cr_1.provider_id = ANY ('{12055157,12055159}'::integer[])))
  • Buffers: shared hit=944 read=40
7. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on contact_reports_p2019_10_02 cr_2 (cost=4.31..9.66 rows=1 width=236) (never executed)

  • Filter: ((cr_2.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone) AND (cr_2.geopoint && '010300000001000000060000000000000080DF38C036EA5FC4E42D454000000000B03966C0F479E7FAED74444000000000B03966C0F479E7FAED7444400000000090D463C00C69492ED93255405A000000006C0BC066465B2EE23455400000000080DF38C036EA5FC4E42D4540'::geometry))
8. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on contact_reports_p2019_10_02_provider_id_idx (cost=0..4.3 rows=2 width=0) (never executed)

  • Index Cond: (cr_2.provider_id = ANY ('{12055157,12055159}'::integer[]))
9. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on contact_reports_p2019_10_03 cr_3 (cost=4.31..9.66 rows=1 width=236) (never executed)

  • Filter: ((cr_3.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone) AND (cr_3.geopoint && '010300000001000000060000000000000080DF38C036EA5FC4E42D454000000000B03966C0F479E7FAED74444000000000B03966C0F479E7FAED7444400000000090D463C00C69492ED93255405A000000006C0BC066465B2EE23455400000000080DF38C036EA5FC4E42D4540'::geometry))
10. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on contact_reports_p2019_10_03_provider_id_idx (cost=0..4.3 rows=2 width=0) (never executed)

  • Index Cond: (cr_3.provider_id = ANY ('{12055157,12055159}'::integer[]))
11. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on contact_reports_p2019_10_04 cr_4 (cost=4.31..9.66 rows=1 width=236) (never executed)

  • Filter: ((cr_4.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone) AND (cr_4.geopoint && '010300000001000000060000000000000080DF38C036EA5FC4E42D454000000000B03966C0F479E7FAED74444000000000B03966C0F479E7FAED7444400000000090D463C00C69492ED93255405A000000006C0BC066465B2EE23455400000000080DF38C036EA5FC4E42D4540'::geometry))
12. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on contact_reports_p2019_10_04_provider_id_idx (cost=0..4.3 rows=2 width=0) (never executed)

  • Index Cond: (cr_4.provider_id = ANY ('{12055157,12055159}'::integer[]))
13. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on contact_reports_p2019_10_05 cr_5 (cost=4.31..9.66 rows=1 width=236) (never executed)

  • Filter: ((cr_5.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone) AND (cr_5.geopoint && '010300000001000000060000000000000080DF38C036EA5FC4E42D454000000000B03966C0F479E7FAED74444000000000B03966C0F479E7FAED7444400000000090D463C00C69492ED93255405A000000006C0BC066465B2EE23455400000000080DF38C036EA5FC4E42D4540'::geometry))
14. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on contact_reports_p2019_10_05_provider_id_idx (cost=0..4.3 rows=2 width=0) (never executed)

  • Index Cond: (cr_5.provider_id = ANY ('{12055157,12055159}'::integer[]))
15. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on contact_reports_p2019_10_06 cr_6 (cost=4.31..9.66 rows=1 width=236) (never executed)

  • Filter: ((cr_6.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone) AND (cr_6.geopoint && '010300000001000000060000000000000080DF38C036EA5FC4E42D454000000000B03966C0F479E7FAED74444000000000B03966C0F479E7FAED7444400000000090D463C00C69492ED93255405A000000006C0BC066465B2EE23455400000000080DF38C036EA5FC4E42D4540'::geometry))
16. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on contact_reports_p2019_10_06_provider_id_idx (cost=0..4.3 rows=2 width=0) (never executed)

  • Index Cond: (cr_6.provider_id = ANY ('{12055157,12055159}'::integer[]))
17. 0.000 0.000 ↓ 0.0 0 0

Bitmap Heap Scan on contact_reports_default cr_7 (cost=8.31..13.66 rows=1 width=236) (never executed)

  • Filter: ((cr_7.received_date_time >= '2019-10-01 12:27:49.84454'::timestamp without time zone) AND (cr_7.geopoint && '010300000001000000060000000000000080DF38C036EA5FC4E42D454000000000B03966C0F479E7FAED74444000000000B03966C0F479E7FAED7444400000000090D463C00C69492ED93255405A000000006C0BC066465B2EE23455400000000080DF38C036EA5FC4E42D4540'::geometry))
18. 0.000 0.000 ↓ 0.0 0 0

Bitmap Index Scan on contact_reports_default_provider_id_idx (cost=0..8.3 rows=2 width=0) (never executed)

  • Index Cond: (cr_7.provider_id = ANY ('{12055157,12055159}'::integer[]))
19. 0.007 0.016 ↓ 1.1 26 1

Hash (cost=1.23..1.23 rows=23 width=23) (actual time=0.015..0.016 rows=26 loops=1)

  • Buffers: shared hit=1
20. 0.009 0.009 ↓ 1.1 26 1

Seq Scan on providers p (cost=0..1.23 rows=23 width=23) (actual time=0.005..0.009 rows=26 loops=1)

  • Buffers: shared hit=1
21. 25.000 25.000 ↑ 1.0 1 5,000

Index Scan using pk_identities on identities i (cost=0.43..6.1 rows=1 width=53) (actual time=0.005..0.005 rows=1 loops=5,000)

  • Index Cond: (i.identity_id = cr.identity_id)
  • Buffers: shared hit=23,565 read=11