explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xaha

Settings
# exclusive inclusive rows x rows loops node
1. 5.456 28,497.652 ↓ 66.6 16,306 1

Unique (cost=144,999.47..145,001.92 rows=245 width=12) (actual time=28,489.696..28,497.652 rows=16,306 loops=1)

2. 34.353 28,492.196 ↓ 230.0 56,354 1

Sort (cost=144,999.47..145,000.08 rows=245 width=12) (actual time=28,489.694..28,492.196 rows=56,354 loops=1)

  • Sort Key: ((to_char((to_date((models_todo.yearweek)::text, 'IYYYIW'::text) - '1 day'::interval), 'IYYYMM'::text))::integer), models_todo.model_id, ((fs.fst_modele_r3)::integer)
  • Sort Method: quicksort Memory: 4,007kB
3. 20,711.773 28,457.843 ↓ 230.0 56,354 1

Nested Loop Left Join (cost=1,008.60..144,989.75 rows=245 width=12) (actual time=298.374..28,457.843 rows=56,354 loops=1)

  • Join Filter: (((fs.fst_modele_r3)::integer = models_todo.model_id) AND ((to_char(rdi.rrdi_date_impact, 'IYYYMM'::text))::integer = (to_char((to_date((models_todo.yearweek)::text, 'IYYYIW'::text) - '1 day'::interval), 'IYYYMM'::text))::integer))
  • Rows Removed by Join Filter: 168,610,122
4. 114.862 114.862 ↓ 199.7 48,918 1

Seq Scan on model_20_80_store models_todo (cost=0.00..3,566.96 rows=245 width=8) (actual time=0.016..114.862 rows=48,918 loops=1)

  • Filter: ((store_id = 230) AND ((to_char((to_date((yearweek)::text, 'IYYYIW'::text) - '1 day'::interval), 'IYYYMM'::text))::integer >= (to_char('2020-09-20 00:00:00'::timestamp without time zone, 'IYYYMM'::text))::integer) AND ((to_char((to_date((yearweek)::text, 'IYYYIW'::text) - '1 day'::interval), 'IYYYMM'::text))::integer <= (to_char('2020-10-05 23:59:59'::timestamp without time zone, 'IYYYMM'::text))::integer))
5. 7,334.579 7,631.208 ↓ 149.9 3,447 48,918

Materialize (cost=1,008.60..141,149.67 rows=23 width=15) (actual time=0.000..0.156 rows=3,447 loops=48,918)

6. 1.898 296.629 ↓ 149.9 3,447 1

Gather (cost=1,008.60..141,149.55 rows=23 width=15) (actual time=19.101..296.629 rows=3,447 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 137.679 294.731 ↓ 114.9 1,149 3 / 3

Hash Join (cost=8.60..140,147.25 rows=10 width=15) (actual time=15.273..294.731 rows=1,149 loops=3)

  • Hash Cond: ((fs.fst_article_r3)::integer = rdi.rrdi_code_fournisseur)
8. 154.905 154.905 ↑ 1.3 645,814 3 / 3

Parallel Seq Scan on flat_structure fs (cost=0.00..135,076.37 rows=809,949 width=14) (actual time=7.829..154.905 rows=645,814 loops=3)

  • Filter: ((fst_article_r3)::text !~~ '10000000%'::text)
  • Rows Removed by Filter: 113
9. 0.679 2.147 ↓ 3,447.0 3,447 3 / 3

Hash (cost=8.59..8.59 rows=1 width=16) (actual time=2.147..2.147 rows=3,447 loops=3)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 194kB
10. 1.468 1.468 ↓ 3,447.0 3,447 3 / 3

Index Scan using idx02_registry_data_impacted on rfid_registry_data_impacted rdi (cost=0.56..8.59 rows=1 width=16) (actual time=0.026..1.468 rows=3,447 loops=3)

  • Index Cond: ((tti_num_type_tiers_tir = 7) AND (tir_num_tiers_tir = 230) AND (rrdi_date_impact >= '2020-09-20 00:00:00'::timestamp without time zone) AND (rrdi_date_impact <= '2020-10-05 23:59:59'::timestamp without time zone))
Planning time : 0.965 ms
Execution time : 28,498.757 ms