explain.depesz.com

PostgreSQL's explain analyze made readable

Result: l4Ib : added BRIN index on iccid, end. Also, much larger timespan

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 266.142 10,612.350 ↑ 6.8 3,290 1

GroupAggregate (cost=484,725.03..504,259.69 rows=22,290 width=168) (actual time=9,930.968..10,612.350 rows=3,290 loops=1)

  • Group Key: sims.iccid, traffic_sessions.carrier_country_code, traffic_sessions.carrier_name
2. 4,900.192 10,346.208 ↑ 1.5 756,642 1

Sort (cost=484,725.03..487,475.89 rows=1,100,345 width=124) (actual time=9,930.476..10,346.208 rows=756,642 loops=1)

  • Sort Key: sims.iccid, traffic_sessions.carrier_country_code, traffic_sessions.carrier_name
  • Sort Method: external merge Disk: 105920kB
3. 605.071 5,446.016 ↑ 1.5 756,642 1

Hash Right Join (cost=380.00..231,389.03 rows=1,100,345 width=124) (actual time=974.872..5,446.016 rows=756,642 loops=1)

  • Hash Cond: (traffic_sessions.iccid = (sims.iccid)::text)
4. 4,808.354 4,808.354 ↑ 1.0 2,737,790 1

Seq Scan on traffic_sessions (cost=0.00..223,648.39 rows=2,801,956 width=47) (actual time=468.195..4,808.354 rows=2,737,790 loops=1)

  • Filter: (("end" >= '2019-10-01 06:00:00+00'::timestamp with time zone) AND ("end" < '2019-11-01 07:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 3833703
5. 1.610 32.591 ↓ 1.4 3,108 1

Hash (cost=352.14..352.14 rows=2,229 width=97) (actual time=32.591..32.591 rows=3,108 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 420kB
6. 30.981 30.981 ↓ 1.4 3,108 1

Seq Scan on sims (cost=0.00..352.14 rows=2,229 width=97) (actual time=0.015..30.981 rows=3,108 loops=1)

  • Filter: ((NOT archived) AND (("clientSlug")::text = 'FOO'::text) AND (("providerSlug")::text = 'BAR'::text))
  • Rows Removed by Filter: 2568
Planning time : 7.663 ms