explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G64n

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 27.615 95,617.899 ↓ 116.0 116 1

HashAggregate (cost=9,803,057.03..9,803,057.04 rows=1 width=80) (actual time=95,617.868..95,617.899 rows=116 loops=1)

  • Group Key: qm.id_legal, l.name, l.full_name
2. 30.515 95,590.284 ↓ 48,754.0 48,754 1

Nested Loop (cost=6,492,838.93..9,803,057.03 rows=1 width=80) (actual time=77,748.540..95,590.284 rows=48,754 loops=1)

3. 17,619.555 95,462.261 ↓ 48,754.0 48,754 1

Hash Left Join (cost=6,492,838.65..9,803,056.68 rows=1 width=2) (actual time=77,748.499..95,462.261 rows=48,754 loops=1)

  • Hash Cond: (((qm.id_operation)::character varying)::text = (ce.session)::text)
  • Filter: (ce.id_operation IS NULL)
4. 3.930 120.202 ↓ 1.0 48,754 1

Append (cost=0.00..27,750.71 rows=47,915 width=10) (actual time=18.099..120.202 rows=48,754 loops=1)

5. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on master qm (cost=0.00..0.00 rows=1 width=10) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((time_server > '2019-03-11 00:00:00+05'::timestamp with time zone) AND (time_server < '2019-03-12 00:00:00+05'::timestamp with time zone) AND (id_provider = ANY ('{3220,3700,2381}'::integer[])))
6. 101.213 116.003 ↓ 1.0 48,754 1

Bitmap Heap Scan on collector_out qm_1 (cost=5,384.56..27,591.80 rows=47,910 width=10) (actual time=18.098..116.003 rows=48,754 loops=1)

  • Recheck Cond: (id_provider = ANY ('{3220,3700,2381}'::integer[]))
  • Filter: ((time_server > '2019-03-11 00:00:00+05'::timestamp with time zone) AND (time_server < '2019-03-12 00:00:00+05'::timestamp with time zone))
  • Rows Removed by Filter: 120875
  • Heap Blocks: exact=18604
7. 14.790 14.790 ↑ 1.0 169,635 1

Bitmap Index Scan on collector_out_id_provider_key (cost=0.00..5,372.58 rows=170,573 width=0) (actual time=14.790..14.790 rows=169,635 loops=1)

  • Index Cond: (id_provider = ANY ('{3220,3700,2381}'::integer[]))
8. 0.024 0.024 ↓ 0.0 0 1

Index Scan using operations_y2019m03_time_server_key on y2019m03 qm_2 (cost=0.43..8.45 rows=1 width=10) (actual time=0.024..0.024 rows=0 loops=1)

  • Index Cond: ((time_server > '2019-03-11 00:00:00+05'::timestamp with time zone) AND (time_server < '2019-03-12 00:00:00+05'::timestamp with time zone))
  • Filter: (id_provider = ANY ('{3220,3700,2381}'::integer[]))
9. 0.245 0.245 ↓ 0.0 0 1

Index Scan using queue_id_provider_key on queue qm_3 (cost=0.41..150.46 rows=3 width=10) (actual time=0.245..0.245 rows=0 loops=1)

  • Index Cond: (id_provider = ANY ('{3220,3700,2381}'::integer[]))
  • Filter: ((time_server > '2019-03-11 00:00:00+05'::timestamp with time zone) AND (time_server < '2019-03-12 00:00:00+05'::timestamp with time zone))
  • Rows Removed by Filter: 17
10. 49,950.482 77,722.504 ↑ 1.0 183,365,891 1

Hash (cost=3,115,808.40..3,115,808.40 rows=183,940,340 width=18) (actual time=77,722.504..77,722.504 rows=183,365,891 loops=1)

  • Buckets: 524288 Batches: 512 Memory Usage: 23625kB
11. 27,772.022 27,772.022 ↑ 1.0 183,365,891 1

Seq Scan on cyber_external ce (cost=0.00..3,115,808.40 rows=183,940,340 width=18) (actual time=0.006..27,772.022 rows=183,365,891 loops=1)

12. 97.508 97.508 ↑ 1.0 1 48,754

Index Scan using fki_provider_biller_legals_fkey on legals l (cost=0.28..0.34 rows=1 width=80) (actual time=0.002..0.002 rows=1 loops=48,754)

  • Index Cond: (id_legal = qm.id_legal)