explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8kTW

Settings
# exclusive inclusive rows x rows loops node
1. 35.807 6,401.925 ↑ 1.0 500,000 1

Limit (cost=187,692.73..374,504.14 rows=500,000 width=462) (actual time=1,663.805..6,401.925 rows=500,000 loops=1)

2. 0.000 6,366.118 ↑ 2.5 500,000 1

Hash Join (cost=187,692.73..663,436.02 rows=1,273,325 width=462) (actual time=1,663.802..6,366.118 rows=500,000 loops=1)

  • Hash Cond: (cr.provider_id = p.provider_id)
3. 5,785.546 5,785.546 ↑ 2.5 500,000 1

Hash Join (cost=187,691.21..634,009.62 rows=1,273,325 width=433) (actual time=1,663.744..5,785.546 rows=500,000 loops=1)

  • Hash Cond: (cr.identity_id = i.identity_id)
4. 0.000 3,191.174 ↑ 1.6 810,853 1

Append (cost=0.44..308,119.36 rows=1,273,325 width=257) (actual time=0.121..3,191.174 rows=810,853 loops=1)

  • -> Index Scan using contact_reports_p2019_10_02_received_date_time_idx on contact_reports_p2019_10_02 cr (cost=0.44..3664.80 rows=17343 width=255) (actual time=0.081..0.328 rows=0 l
  • Index Cond: (received_date_time >= '2019-10-03 10:27:49'::timestamp without time zone)
  • Filter: ((geopoint && '0103000020E61000000100000006000000FDFFFFFF7FDF38C034EA5FC4E42D454000000000B03966C0F079E7FAED74444000000000B03966C0F079E7FAED7444400000000090D463C00E69492ED93255405A000000006C0BC064465B2EE2345540FDFFFFFF7FDF38C034EA5FC4E42D4540'::geometry) AND (provider_id = ANY ('{12055157,12055159}'::integer[])))
  • Rows Removed by Filter: 5
5. 3,142.746 3,142.746 ↑ 1.5 810,853 1

Index Scan using contact_reports_p2019_10_03_received_date_time_idx on contact_reports_p2019_10_03 cr_1 (cost=0.43..298,037.65 rows=1,255,976 width=257) (actual time=0.040..3,142.746 rows=810,853 loops=1)

  • Index Cond: (received_date_time >= '2019-10-03 10:27:49'::timestamp without time zone)
  • Filter: ((geopoint && '0103000020E61000000100000006000000FDFFFFFF7FDF38C034EA5FC4E42D454000000000B03966C0F079E7FAED74444000000000B03966C0F079E7FAED7444400000000090D463C00E69492ED93255405A000000006C0BC064465B2EE2345540FDFFFFFF7FDF38C034EA5FC4E42D4540'::geometry) AND (provider_id = ANY ('{12055157,12055159}'::integer[])))
  • Rows Removed by Filter: 3337954
6. 0.007 0.040 ↓ 0.0 0 1

Bitmap Heap Scan on contact_reports_p2019_10_04 cr_2 (cost=4.31..9.66 rows=1 width=236) (actual time=0.018..0.040 rows=0 loops=1)

  • Recheck Cond: (provider_id = ANY ('{12055157,12055159}'::integer[]))
  • Filter: ((received_date_time >= '2019-10-03 10:27:49'::timestamp without time zone) AND (geopoint && '0103000020E61000000100000006000000FDFFFFFF7FDF38C034EA5FC4E42D454000000000B03966C0F079E7FAED74444000000000B03966C0F079E7FAED7444400000000090D463C00E69492ED93255405A000000006C0BC064465B2EE2345540FDFFFFFF7FDF38C034EA5FC4E42D4540'::geometry))
7. 0.033 0.033 ↓ 0.0 0 1

Bitmap Index Scan on contact_reports_p2019_10_04_provider_id_idx (cost=0.00..4.30 rows=2 width=0) (actual time=0.011..0.033 rows=0 loops=1)

  • Index Cond: (provider_id = ANY ('{12055157,12055159}'::integer[]))
8. 0.005 0.037 ↓ 0.0 0 1

Bitmap Heap Scan on contact_reports_p2019_10_05 cr_3 (cost=4.31..9.66 rows=1 width=236) (actual time=0.015..0.037 rows=0 loops=1)

  • Recheck Cond: (provider_id = ANY ('{12055157,12055159}'::integer[]))
  • Filter: ((received_date_time >= '2019-10-03 10:27:49'::timestamp without time zone) AND (geopoint && '0103000020E61000000100000006000000FDFFFFFF7FDF38C034EA5FC4E42D454000000000B03966C0F079E7FAED74444000000000B03966C0F079E7FAED7444400000000090D463C00E69492ED93255405A000000006C0BC064465B2EE2345540FDFFFFFF7FDF38C034EA5FC4E42D4540'::geometry))
9. 0.032 0.032 ↓ 0.0 0 1

Bitmap Index Scan on contact_reports_p2019_10_05_provider_id_idx (cost=0.00..4.30 rows=2 width=0) (actual time=0.010..0.032 rows=0 loops=1)

  • Index Cond: (provider_id = ANY ('{12055157,12055159}'::integer[]))
10. 0.003 0.033 ↓ 0.0 0 1

Bitmap Heap Scan on contact_reports_p2019_10_06 cr_4 (cost=4.31..9.66 rows=1 width=236) (actual time=0.012..0.033 rows=0 loops=1)

  • Recheck Cond: (provider_id = ANY ('{12055157,12055159}'::integer[]))
  • Filter: ((received_date_time >= '2019-10-03 10:27:49'::timestamp without time zone) AND (geopoint && '0103000020E61000000100000006000000FDFFFFFF7FDF38C034EA5FC4E42D454000000000B03966C0F079E7FAED74444000000000B03966C0F079E7FAED7444400000000090D463C00E69492ED93255405A000000006C0BC064465B2EE2345540FDFFFFFF7FDF38C034EA5FC4E42D4540'::geometry))
11. 0.030 0.030 ↓ 0.0 0 1

Bitmap Index Scan on contact_reports_p2019_10_06_provider_id_idx (cost=0.00..4.30 rows=2 width=0) (actual time=0.008..0.030 rows=0 loops=1)

  • Index Cond: (provider_id = ANY ('{12055157,12055159}'::integer[]))
12. 0.004 0.035 ↓ 0.0 0 1

Bitmap Heap Scan on contact_reports_p2019_10_07 cr_5 (cost=4.31..9.66 rows=1 width=236) (actual time=0.013..0.035 rows=0 loops=1)

  • Recheck Cond: (provider_id = ANY ('{12055157,12055159}'::integer[]))
  • Filter: ((received_date_time >= '2019-10-03 10:27:49'::timestamp without time zone) AND (geopoint && '0103000020E61000000100000006000000FDFFFFFF7FDF38C034EA5FC4E42D454000000000B03966C0F079E7FAED74444000000000B03966C0F079E7FAED7444400000000090D463C00E69492ED93255405A000000006C0BC064465B2EE2345540FDFFFFFF7FDF38C034EA5FC4E42D4540'::geometry))
13. 0.031 0.031 ↓ 0.0 0 1

Bitmap Index Scan on contact_reports_p2019_10_07_provider_id_idx (cost=0.00..4.30 rows=2 width=0) (actual time=0.008..0.031 rows=0 loops=1)

  • Index Cond: (provider_id = ANY ('{12055157,12055159}'::integer[]))
14. 0.004 0.033 ↓ 0.0 0 1

Bitmap Heap Scan on contact_reports_p2019_10_08 cr_6 (cost=4.31..9.66 rows=1 width=236) (actual time=0.011..0.033 rows=0 loops=1)

  • Recheck Cond: (provider_id = ANY ('{12055157,12055159}'::integer[]))
  • Filter: ((received_date_time >= '2019-10-03 10:27:49'::timestamp without time zone) AND (geopoint && '0103000020E61000000100000006000000FDFFFFFF7FDF38C034EA5FC4E42D454000000000B03966C0F079E7FAED74444000000000B03966C0F079E7FAED7444400000000090D463C00E69492ED93255405A000000006C0BC064465B2EE2345540FDFFFFFF7FDF38C034EA5FC4E42D4540'::geometry))
15. 0.029 0.029 ↓ 0.0 0 1

Bitmap Index Scan on contact_reports_p2019_10_08_provider_id_idx (cost=0.00..4.30 rows=2 width=0) (actual time=0.008..0.029 rows=0 loops=1)

  • Index Cond: (provider_id = ANY ('{12055157,12055159}'::integer[]))
16. 0.036 0.036 ↓ 0.0 0 1

Seq Scan on contact_reports_default cr_7 (cost=0.00..2.00 rows=1 width=236) (actual time=0.014..0.036 rows=0 loops=1)

  • Filter: ((received_date_time >= '2019-10-03 10:27:49'::timestamp without time zone) AND (geopoint && '0103000020E61000000100000006000000FDFFFFFF7FDF38C034EA5FC4E42D454000000000B03966C0F079E7FAED74444000000000B03966C0F079E7FAED7444400000000090D463C00E69492ED93255405A000000006C0BC064465B2EE2345540FDFFFFFF7FDF38C034EA5FC4E42D4540'::geometry) AND (provider_id = ANY ('{12055157,12055159}'::integer[])))
17. 898.124 1,651.515 ↓ 1.1 1,966,993 1

Hash (cost=120,052.01..120,052.01 rows=1,785,101 width=180) (actual time=1,651.098..1,651.515 rows=1,966,993 loops=1)

  • Buckets: 2097152 Batches: 2 Memory Usage: 209863kB
18. 753.391 753.391 ↓ 1.1 1,966,993 1

Seq Scan on identities i (cost=0.00..120,052.01 rows=1,785,101 width=180) (actual time=0.008..753.391 rows=1,966,993 loops=1)

19. 0.025 0.294 ↓ 1.1 26 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
20. 0.269 0.269 ↓ 1.1 26 1

Seq Scan on providers p (cost=0.00..1.23 rows=23 width=23) (actual time=0.006..0.269 rows=26 loops=1)