explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AliJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,871.229 ↑ 2.1 153 1

Group (cost=1,523,962.30..1,523,995.09 rows=317 width=30) (actual time=1,870.899..1,871.229 rows=153 loops=1)

  • Group Key: product_hierarchies.ppg_name, clean_data_inputs.date
2. 16.551 1,881.355 ↑ 1.7 156 1

Gather Merge (cost=1,523,962.30..1,523,993.77 rows=264 width=30) (actual time=1,870.896..1,881.355 rows=156 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 0.040 1,864.804 ↑ 2.5 52 3 / 3

Group (cost=1,522,962.28..1,522,963.27 rows=132 width=30) (actual time=1,864.759..1,864.804 rows=52 loops=3)

  • Group Key: product_hierarchies.ppg_name, clean_data_inputs.date
4. 0.702 1,864.764 ↑ 1.4 95 3 / 3

Sort (cost=1,522,962.28..1,522,962.61 rows=132 width=30) (actual time=1,864.755..1,864.764 rows=95 loops=3)

  • Sort Key: product_hierarchies.ppg_name, clean_data_inputs.date
  • Sort Method: quicksort Memory: 32kB
  • Worker 0: Sort Method: quicksort Memory: 32kB
  • Worker 1: Sort Method: quicksort Memory: 33kB
5. 0.193 1,864.062 ↑ 1.4 95 3 / 3

Hash Join (cost=183,227.44..1,522,957.63 rows=132 width=30) (actual time=1,720.790..1,864.062 rows=95 loops=3)

  • Hash Cond: (product_settings.ppg_id = product_hierarchies.id)
6. 22.902 1,860.123 ↑ 3.5 95 3 / 3

Parallel Hash Join (cost=183,017.64..1,522,746.95 rows=335 width=8) (actual time=1,716.919..1,860.123 rows=95 loops=3)

  • Hash Cond: (clean_data_inputs.product_settings_id = product_settings.id)
7. 1,129.485 1,599.334 ↑ 1.3 65,244 3 / 3

Parallel Bitmap Heap Scan on clean_data_inputs (cost=133,053.91..1,472,564.72 rows=83,240 width=8) (actual time=1,475.889..1,599.334 rows=65,244 loops=3)

  • Recheck Cond: ((date = ANY ('{2018-09-01,2018-09-08,2018-09-29,2018-10-27,2018-11-24,2018-12-01,2018-12-15,2019-01-12,2019-01-19,2019-01-26,2019-02-02,2019-02-23,2019-03-02,2019-03-16,2019-03-30,2019-05-04,2019-06-01,2019-06-15,2019-07-06,2019-08-03}'::date[])) AND (enterprise_id = 18))
  • Rows Removed by Index Recheck: 220954
  • Heap Blocks: exact=1333 lossy=4762
8. 22.794 469.849 ↓ 0.0 0 1 / 3

BitmapAnd (cost=133,053.91..133,053.91 rows=199,776 width=0) (actual time=1,409.548..1,409.548 rows=0 loops=1)

9. 250.067 250.067 ↓ 1.4 4,757,321 1 / 3

Bitmap Index Scan on data_date_ix (cost=0.00..61,002.80 rows=3,348,193 width=0) (actual time=750.200..750.201 rows=4,757,321 loops=1)

  • Index Cond: (date = ANY ('{2018-09-01,2018-09-08,2018-09-29,2018-10-27,2018-11-24,2018-12-01,2018-12-15,2019-01-12,2019-01-19,2019-01-26,2019-02-02,2019-02-23,2019-03-02,2019-03-16,2019-03-30,2019-05-04,2019-06-01,2019-06-15,2019-07-06,2019-08-03}'::date[]))
10. 196.988 196.988 ↑ 1.0 3,803,154 1 / 3

Bitmap Index Scan on data_enterprise_id_ix (cost=0.00..71,950.98 rows=3,895,788 width=0) (actual time=590.965..590.965 rows=3,803,154 loops=1)

  • Index Cond: (enterprise_id = 18)
11. 2.105 237.887 ↑ 1.1 4,116 3 / 3

Parallel Hash (cost=49,906.64..49,906.64 rows=4,567 width=8) (actual time=237.887..237.887 rows=4,116 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 640kB
12. 166.735 235.782 ↑ 1.1 4,116 3 / 3

Parallel Bitmap Heap Scan on product_settings (cost=21,739.36..49,906.64 rows=4,567 width=8) (actual time=205.059..235.782 rows=4,116 loops=3)

  • Recheck Cond: ((banner_id = 801) AND ((promo_group)::text = 'Flyer'::text))
  • Filter: (ppg_id <> 911)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=4521
13. 0.832 69.047 ↓ 0.0 0 1 / 3

BitmapAnd (cost=21,739.36..21,739.36 rows=10,961 width=0) (actual time=207.142..207.142 rows=0 loops=1)

14. 2.647 2.647 ↓ 1.1 30,875 1 / 3

Bitmap Index Scan on index_product_settings_on_banner_id (cost=0.00..504.60 rows=27,223 width=0) (actual time=7.941..7.942 rows=30,875 loops=1)

  • Index Cond: (banner_id = 801)
15. 65.568 65.568 ↑ 1.0 1,086,740 1 / 3

Bitmap Index Scan on index_product_settings_on_promo_group (cost=0.00..21,229.03 rows=1,096,080 width=0) (actual time=196.705..196.705 rows=1,086,740 loops=1)

  • Index Cond: ((promo_group)::text = 'Flyer'::text)
16. 1.678 3.746 ↑ 1.0 3,991 3 / 3

Hash (cost=159.91..159.91 rows=3,991 width=30) (actual time=3.746..3.746 rows=3,991 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 282kB
17. 2.068 2.068 ↑ 1.0 3,991 3 / 3

Seq Scan on product_hierarchies (cost=0.00..159.91 rows=3,991 width=30) (actual time=0.022..2.068 rows=3,991 loops=3)

Planning time : 1.771 ms
Execution time : 1,881.609 ms