explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GrTa

Settings
# exclusive inclusive rows x rows loops node
1. 49.703 9,870.399 ↑ 66.3 82,234 1

GroupAggregate (cost=2,242,944.03..2,461,121.63 rows=5,454,440 width=88) (actual time=9,809.844..9,870.399 rows=82,234 loops=1)

  • Group Key: ((bd_1.external_id)::integer), l.id, (CASE WHEN (b_1.preferred_provider = 'Distrifood'::text) THEN (ds.valid_on)::timestamp without time zone ELSE rs.finished_at END)
2.          

Initplan (for GroupAggregate)

3. 0.001 0.016 ↑ 1.0 1 1

Result (cost=6.90..6.91 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)

4.          

Initplan (for Result)

5. 0.000 0.015 ↑ 1.0 1 1

Limit (cost=0.15..6.90 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)

6. 0.015 0.015 ↑ 6.0 1 1

Index Scan Backward using pk_datasets on tbl_datasets (cost=0.15..40.66 rows=6 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (id IS NOT NULL)
  • Filter: ((name)::text ~~* '%distrifood%'::text)
  • Rows Removed by Filter: 6
7. 0.001 0.013 ↑ 1.0 1 1

Result (cost=0.70..0.71 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)

8.          

Initplan (for Result)

9. 0.001 0.012 ↑ 1.0 1 1

Limit (cost=0.15..0.70 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)

10. 0.011 0.011 ↑ 73.0 1 1

Index Scan Backward using pk_datasets on tbl_datasets tbl_datasets_1 (cost=0.15..40.66 rows=73 width=4) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (id IS NOT NULL)
  • Filter: ((name)::text ~~ 'RS STORE SCRAPER %'::text)
  • Rows Removed by Filter: 2
11. 67.525 9,820.667 ↑ 34.3 158,998 1

Sort (cost=2,242,936.41..2,256,572.51 rows=5,454,440 width=42) (actual time=9,809.833..9,820.667 rows=158,998 loops=1)

  • Sort Key: ((bd_1.external_id)::integer), l.id, (CASE WHEN (b_1.preferred_provider = 'Distrifood'::text) THEN (ds.valid_on)::timestamp without time zone ELSE rs.finished_at END)
  • Sort Method: quicksort Memory: 18,566kB
12. 27.504 9,753.142 ↑ 34.3 158,998 1

Merge Right Join (cost=1,468,968.65..1,632,611.85 rows=5,454,440 width=42) (actual time=9,711.655..9,753.142 rows=158,998 loops=1)

  • Merge Cond: (rs.scrape_run_id = ((bd_1.external_id)::integer))
13. 2.630 29.899 ↓ 11.9 11,865 1

Sort (cost=60.08..62.58 rows=1,000 width=12) (actual time=29.187..29.899 rows=11,865 loops=1)

  • Sort Key: rs.scrape_run_id
  • Sort Method: quicksort Memory: 947kB
14. 27.269 27.269 ↓ 12.0 11,991 1

Function Scan on get_scrape_runs rs (cost=0.25..10.25 rows=1,000 width=12) (actual time=26.445..27.269 rows=11,991 loops=1)

15. 57.259 9,695.739 ↑ 6.9 158,998 1

Sort (cost=1,468,908.57..1,471,635.79 rows=1,090,888 width=50) (actual time=9,682.286..9,695.739 rows=158,998 loops=1)

  • Sort Key: ((bd_1.external_id)::integer)
  • Sort Method: quicksort Memory: 28,504kB
16. 4,652.390 9,638.480 ↑ 6.9 158,998 1

Hash Right Join (cost=410,345.10..1,359,508.48 rows=1,090,888 width=50) (actual time=8,142.265..9,638.480 rows=158,998 loops=1)

  • Hash Cond: ((lav.location_attribute_id = la.id) AND (lav.location_id = l.id))
17. 3,798.999 3,798.999 ↑ 1.0 29,984,409 1

Seq Scan on tbl_location_attribute_values lav (cost=0.00..723,722.64 rows=30,046,464 width=13) (actual time=0.008..3,798.999 rows=29,984,409 loops=1)

18. 38.043 1,187.091 ↑ 6.9 158,998 1

Hash (cost=393,981.78..393,981.78 rows=1,090,888 width=49) (actual time=1,187.091..1,187.091 rows=158,998 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 29,401kB
19. 27.387 1,149.048 ↑ 6.9 158,998 1

Merge Join (cost=362,857.15..393,981.78 rows=1,090,888 width=49) (actual time=1,115.582..1,149.048 rows=158,998 loops=1)

  • Merge Cond: ((bd_1.dataset_id = l.dataset_id) AND (bd_1.brand_id = l.brand_id))
20. 20.401 45.861 ↓ 1.0 130,588 1

Merge Join (cost=60.00..5,456.96 rows=126,251 width=42) (actual time=0.455..45.861 rows=130,588 loops=1)

  • Merge Cond: (bd_1.dataset_id = la.dataset_id)
21. 18.661 18.661 ↑ 1.0 71,639 1

Index Scan using tbl_brand_datasets_dataset_id_brand_id_idx on tbl_brand_datasets bd_1 (cost=0.29..3,324.38 rows=71,639 width=13) (actual time=0.012..18.661 rows=71,639 loops=1)

22. 6.387 6.799 ↓ 839.5 129,287 1

Sort (cost=59.71..60.09 rows=154 width=29) (actual time=0.440..6.799 rows=129,287 loops=1)

  • Sort Key: la.dataset_id
  • Sort Method: quicksort Memory: 37kB
23. 0.030 0.412 ↑ 1.0 154 1

Hash Join (cost=24.26..54.11 rows=154 width=29) (actual time=0.205..0.412 rows=154 loops=1)

  • Hash Cond: (la.dataset_id = ds.id)
24. 0.195 0.195 ↑ 1.0 154 1

Seq Scan on tbl_location_attributes la (cost=0.00..27.74 rows=154 width=21) (actual time=0.014..0.195 rows=154 loops=1)

  • Filter: ((name)::text = ANY ('{"gross surface","neto surface"}'::text[]))
  • Rows Removed by Filter: 785
25. 0.040 0.187 ↑ 1.0 367 1

Hash (cost=19.67..19.67 rows=367 width=8) (actual time=0.187..0.187 rows=367 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
26. 0.147 0.147 ↑ 1.0 367 1

Seq Scan on tbl_datasets ds (cost=0.00..19.67 rows=367 width=8) (actual time=0.012..0.147 rows=367 loops=1)

27. 27.363 1,075.800 ↑ 11.9 158,997 1

Sort (cost=362,797.15..367,526.34 rows=1,891,675 width=23) (actual time=1,066.873..1,075.800 rows=158,997 loops=1)

  • Sort Key: l.dataset_id, l.brand_id
  • Sort Method: quicksort Memory: 9,497kB
28. 656.595 1,048.437 ↑ 23.0 82,234 1

Hash Left Join (cost=349.71..165,578.37 rows=1,891,675 width=23) (actual time=968.016..1,048.437 rows=82,234 loops=1)

  • Hash Cond: (l.brand_id = b_1.id)
  • Filter: CASE WHEN (b_1.preferred_provider = 'Distrifood'::text) THEN (l.dataset_id = $1) ELSE (l.dataset_id = $3) END
  • Rows Removed by Filter: 3,713,508
29. 389.163 389.163 ↓ 1.0 3,795,742 1

Seq Scan on tbl_locations l (cost=0.00..84,832.49 rows=3,783,349 width=12) (actual time=0.003..389.163 rows=3,795,742 loops=1)

30. 0.770 2.679 ↑ 1.0 6,965 1

Hash (cost=262.65..262.65 rows=6,965 width=15) (actual time=2.679..2.679 rows=6,965 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 391kB
31. 1.909 1.909 ↑ 1.0 6,965 1

Seq Scan on tbl_brands b_1 (cost=0.00..262.65 rows=6,965 width=15) (actual time=0.010..1.909 rows=6,965 loops=1)

Planning time : 5.425 ms
Execution time : 9,878.284 ms