explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3b2G

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 29,476.541 ↑ 67.0 3 1

HashAggregate (cost=145,113.99..145,116.00 rows=201 width=48) (actual time=29,476.539..29,476.541 rows=3 loops=1)

  • Group Key: ""*SELECT* 1"".month, ""*SELECT* 1"".nb_todo, ""*SELECT* 1"".nb_done
2.          

CTE months

3. 0.022 0.022 ↑ 500.0 2 1

Function Scan on generate_series dates (cost=0.02..17.52 rows=1,000 width=4) (actual time=0.014..0.022 rows=2 loops=1)

4.          

CTE models_20_80

5. 5.632 29,463.026 ↓ 66.6 16,306 1

Unique (cost=144,999.47..145,001.92 rows=245 width=12) (actual time=29,454.688..29,463.026 rows=16,306 loops=1)

6. 33.417 29,457.394 ↓ 230.0 56,354 1

Sort (cost=144,999.47..145,000.08 rows=245 width=12) (actual time=29,454.686..29,457.394 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
7. 21,678.729 29,423.977 ↓ 230.0 56,354 1

Nested Loop Left Join (cost=1,008.60..144,989.75 rows=245 width=12) (actual time=304.056..29,423.977 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
8. 114.040 114.040 ↓ 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.015..114.040 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))
9. 7,328.776 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)

10. 1.896 302.432 ↓ 149.9 3,447 1

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

  • Workers Planned: 2
  • Workers Launched: 2
11. 142.098 300.536 ↓ 114.9 1,149 3 / 3

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

  • Hash Cond: ((fs.fst_article_r3)::integer = rdi.rrdi_code_fournisseur)
12. 156.457 156.457 ↑ 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=5.728..156.457 rows=645,814 loops=3)

  • Filter: ((fst_article_r3)::text !~~ '10000000%'::text)
  • Rows Removed by Filter: 113
13. 0.619 1.981 ↓ 3,447.0 3,447 3 / 3

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

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 194kB
14. 1.362 1.362 ↓ 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.022..1.362 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))
15. 0.002 29,476.533 ↑ 67.0 3 1

Append (cost=81.90..93.05 rows=201 width=48) (actual time=29,471.813..29,476.533 rows=3 loops=1)

16. 0.001 29,471.814 ↑ 100.0 2 1

Subquery Scan on "*SELECT* 1" (cost=81.90..86.90 rows=200 width=48) (actual time=29,471.812..29,471.814 rows=2 loops=1)

17. 2.243 29,471.813 ↑ 100.0 2 1

HashAggregate (cost=81.90..84.90 rows=200 width=52) (actual time=29,471.811..29,471.813 rows=2 loops=1)

  • Group Key: mo.month
18. 1.757 29,469.570 ↓ 13.3 16,306 1

Hash Left Join (cost=7.96..72.71 rows=1,225 width=12) (actual time=29,467.811..29,469.570 rows=16,306 loops=1)

  • Hash Cond: (mo.month = m.yearmonth)
19. 0.023 0.023 ↑ 500.0 2 1

CTE Scan on months mo (cost=0.00..20.00 rows=1,000 width=4) (actual time=0.015..0.023 rows=2 loops=1)

20. 1.938 29,467.790 ↓ 66.6 16,306 1

Hash (cost=4.90..4.90 rows=245 width=12) (actual time=29,467.790..29,467.790 rows=16,306 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 769kB
21. 29,465.852 29,465.852 ↓ 66.6 16,306 1

CTE Scan on models_20_80 m (cost=0.00..4.90 rows=245 width=12) (actual time=29,454.690..29,465.852 rows=16,306 loops=1)

22. 3.348 4.717 ↑ 1.0 1 1

Aggregate (cost=6.12..6.13 rows=1 width=48) (actual time=4.717..4.717 rows=1 loops=1)

23. 1.369 1.369 ↓ 66.6 16,306 1

CTE Scan on models_20_80 m_1 (cost=0.00..4.90 rows=245 width=8) (actual time=0.000..1.369 rows=16,306 loops=1)

Planning time : 0.472 ms
Execution time : 29,477.066 ms