explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Nxox

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,728.342 ↑ 1.0 1 1

Aggregate (cost=49,303.64..49,303.65 rows=1 width=8) (actual time=1,728.342..1,728.342 rows=1 loops=1)

2. 24.540 1,728.340 ↓ 0.0 0 1

Subquery Scan on articles_list (cost=36,192.94..49,303.64 rows=1 width=0) (actual time=1,728.340..1,728.340 rows=0 loops=1)

  • Filter: (((articles_list.""group"")::text ~~* '%a304%'::text) AND ((articles_list.name)::text ~~* '%rami ra%'::text))
  • Rows Removed by Filter: 45,272
3. 14.403 1,703.800 ↑ 2.7 45,272 1

Unique (cost=36,192.94..47,474.24 rows=121,960 width=480) (actual time=1,592.433..1,703.800 rows=45,272 loops=1)

4.          

CTE a

5. 0.002 0.004 ↓ 0.0 0 1

Sort (cost=31.64..32.14 rows=200 width=100) (actual time=0.004..0.004 rows=0 loops=1)

  • Sort Key: article_structures.seq
  • Sort Method: quicksort Memory: 25kB
6. 0.001 0.002 ↓ 0.0 0 1

HashAggregate (cost=22.00..24.00 rows=200 width=100) (actual time=0.002..0.002 rows=0 loops=1)

  • Group Key: article_structures.seq, article_structures.part_no, article_structures.artcode, article_structures.alt_code
7. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on article_structures (cost=0.00..16.00 rows=600 width=100) (actual time=0.000..0.001 rows=0 loops=1)

8. 81.854 1,689.397 ↑ 2.7 45,272 1

GroupAggregate (cost=36,160.80..47,137.20 rows=121,960 width=480) (actual time=1,592.430..1,689.397 rows=45,272 loops=1)

  • Group Key: articles.code, articles.""group"", articles.name, articles.unit, articles.class, articles.basesellprice, articles.basepurcprice, f.filename, articles.classif, articles.barcode, (CASE WHEN (articles.class = 'DET'::text) THEN s.component_part ELSE NULL::text END), (CASE WHEN (articles.class = 'DET'::text) THEN s.component_desc ELSE NULL::text END), articles.length, articles.width, articles.height, articles.capacity, articles.diameter, (COALESCE(articles.last_modified_by, articles.created_by)), (COALESCE(articles.modified)), articles.closed, articles.minlevel, articles.created_by, articles.ean, articles.comment, articles.lastpurchprice, articles.taric_code, articles.visible_in_web, articles.pindala, articles.net_weight
9. 1,307.862 1,607.543 ↓ 1.1 135,706 1

Sort (cost=36,160.80..36,465.70 rows=121,960 width=448) (actual time=1,592.424..1,607.543 rows=135,706 loops=1)

  • Sort Key: articles.code, articles.""group"", articles.name, articles.unit, articles.class, articles.basesellprice, articles.basepurcprice, f.filename, articles.classif, articles.barcode, (CASE WHEN (articles.class = 'DET'::text) THEN s.component_part ELSE NULL::text END), (CASE WHEN (articles.class = 'DET'::text) THEN s.component_desc ELSE NULL::text END), articles.length, articles.width, articles.height, articles.capacity, articles.diameter, (COALESCE(articles.last_modified_by, articles.created_by)), (COALESCE(articles.modified)), articles.closed, articles.minlevel, articles.created_by, articles.ean, articles.comment, articles.lastpurchprice, articles.taric_code, articles.visible_in_web, articles.pindala, articles.net_weight
  • Sort Method: external sort Disk: 13,152kB
10. 90.288 299.681 ↓ 1.1 135,706 1

Merge Left Join (cost=13.83..11,803.59 rows=121,960 width=448) (actual time=0.078..299.681 rows=135,706 loops=1)

  • Merge Cond: (articles.code = s.part_no)
11. 60.632 144.616 ↑ 1.0 45,272 1

Merge Left Join (cost=13.42..4,568.29 rows=45,272 width=359) (actual time=0.063..144.616 rows=45,272 loops=1)

  • Merge Cond: (articles.code = sa.part_no)
12. 9.017 64.735 ↑ 1.0 45,272 1

Merge Left Join (cost=13.13..2,673.13 rows=45,272 width=359) (actual time=0.037..64.735 rows=45,272 loops=1)

  • Merge Cond: (articles.code = a.artcode)
13. 9.769 55.711 ↑ 1.0 45,272 1

Merge Left Join (cost=1.49..2,545.31 rows=45,272 width=359) (actual time=0.030..55.711 rows=45,272 loops=1)

  • Merge Cond: (articles.code = (((f.uid)::json ->> 'code'::text)))
14. 45.936 45.936 ↑ 1.0 45,272 1

Index Scan using articles_code_idx on articles (cost=0.41..2,091.49 rows=45,272 width=303) (actual time=0.022..45.936 rows=45,272 loops=1)

15. 0.002 0.006 ↓ 0.0 0 1

Sort (cost=1.07..1.07 rows=1 width=71) (actual time=0.006..0.006 rows=0 loops=1)

  • Sort Key: (((f.uid)::json ->> 'code'::text))
  • Sort Method: quicksort Memory: 25kB
16. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on files f (cost=0.00..1.06 rows=1 width=71) (actual time=0.004..0.004 rows=0 loops=1)

  • Filter: ((module = 'Articles'::text) AND (dest = 'articles_list'::text))
  • Rows Removed by Filter: 5
17. 0.002 0.007 ↓ 0.0 0 1

Sort (cost=11.64..12.14 rows=200 width=32) (actual time=0.007..0.007 rows=0 loops=1)

  • Sort Key: a.artcode
  • Sort Method: quicksort Memory: 25kB
18. 0.005 0.005 ↓ 0.0 0 1

CTE Scan on a (cost=0.00..4.00 rows=200 width=32) (actual time=0.005..0.005 rows=0 loops=1)

19. 19.249 19.249 ↑ 1.0 31,442 1

Index Only Scan using unique_structure_alternatine on structure_alt sa (cost=0.29..1,386.66 rows=31,625 width=19) (actual time=0.024..19.249 rows=31,442 loops=1)

  • Heap Fetches: 29,388
20. 64.777 64.777 ↑ 1.0 121,876 1

Index Scan using fki_structure_rows_structures_part_no_fkey on structure_rows s (cost=0.42..4,987.82 rows=121,960 width=60) (actual time=0.013..64.777 rows=121,876 loops=1)

Planning time : 1.487 ms
Execution time : 1,730.985 ms