explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZUQJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 5,634.435 ↑ 3,389.0 23 1

Subquery Scan on a (cost=2,531,587.35..2,549,369.29 rows=77,947 width=88) (actual time=5,634.276..5,634.435 rows=23 loops=1)

  • Buffers: shared hit=1130184 dirtied=109, temp read=20185 written=20185
2. 0.188 5,634.430 ↑ 3,389.0 23 1

GroupAggregate (cost=2,531,587.35..2,541,574.59 rows=77,947 width=113) (actual time=5,634.274..5,634.430 rows=23 loops=1)

  • Group Key: c_1.id, e.cep, w.datacriacao, w.dataalteracao
  • Filter: (string_agg(DISTINCT (plista.codigo)::text, '|'::text) IS NOT NULL)
  • Rows Removed by Filter: 7
  • Buffers: shared hit=1130184 dirtied=109, temp read=20185 written=20185
3. 0.090 5,634.242 ↑ 1,135.3 69 1

Sort (cost=2,531,587.35..2,531,783.20 rows=78,339 width=45) (actual time=5,634.238..5,634.242 rows=69 loops=1)

  • Sort Key: c_1.id, e.cep, w.datacriacao, w.dataalteracao
  • Sort Method: quicksort Memory: 33kB
  • Buffers: shared hit=1130184 dirtied=109, temp read=20185 written=20185
4. 0.010 5,634.152 ↑ 1,135.3 69 1

Nested Loop Left Join (cost=2,401,005.41..2,525,219.39 rows=78,339 width=45) (actual time=5,585.117..5,634.152 rows=69 loops=1)

  • Buffers: shared hit=1130181 dirtied=109, temp read=20185 written=20185
5. 30.155 5,633.866 ↑ 1,135.3 69 1

Hash Right Join (cost=2,401,004.98..2,424,854.32 rows=78,339 width=44) (actual time=5,585.094..5,633.866 rows=69 loops=1)

  • Hash Cond: (w.idcliente = c_1.id)
  • Filter: ((COALESCE(w.dataalteracao, w.datacriacao) IS NOT NULL) AND (COALESCE(w.dataalteracao, w.datacriacao) >= '2020-02-02 09:00:09.861'::timestamp without time zone))
  • Rows Removed by Filter: 216053
  • Buffers: shared hit=1129905 dirtied=109, temp read=20185 written=20185
6. 4.668 31.189 ↓ 1.2 23,500 1

Merge Right Join (cost=0.86..23,798.23 rows=19,763 width=36) (actual time=0.066..31.189 rows=23,500 loops=1)

  • Merge Cond: (pw.wishlist_id = w.id)
  • Buffers: shared hit=39212
7. 8.214 22.304 ↓ 1.0 11,405 1

Nested Loop Left Join (cost=0.58..20,176.58 rows=11,404 width=20) (actual time=0.045..22.304 rows=11,405 loops=1)

  • Buffers: shared hit=37278
8. 2.685 2.685 ↓ 1.0 11,405 1

Index Only Scan using produtos_wishlist_pkey on produtos_wishlist pw (cost=0.29..1,386.98 rows=11,404 width=16) (actual time=0.019..2.685 rows=11,405 loops=1)

  • Heap Fetches: 11405
  • Buffers: shared hit=3058
9. 11.405 11.405 ↑ 1.0 1 11,405

Index Scan using produto_pkey on produto plista (cost=0.29..1.65 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=11,405)

  • Index Cond: (id = pw.produtos_id)
  • Buffers: shared hit=34220
10. 4.217 4.217 ↓ 1.0 19,764 1

Index Scan using wishlist_pkey on wishlist w (cost=0.29..2,405.11 rows=19,763 width=32) (actual time=0.018..4.217 rows=19,764 loops=1)

  • Buffers: shared hit=1934
11. 34.922 5,572.522 ↑ 1.0 210,260 1

Hash (cost=2,379,397.01..2,379,397.01 rows=210,801 width=16) (actual time=5,572.522..5,572.522 rows=210,260 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 11904kB
  • Buffers: shared hit=1090693 dirtied=109, temp read=20185 written=20185
12. 295.279 5,537.600 ↑ 1.0 210,260 1

HashAggregate (cost=2,337,130.91..2,358,316.91 rows=210,801 width=16) (actual time=5,490.871..5,537.600 rows=210,260 loops=1)

  • Group Key: c_1.id
  • Filter: (max(e_1.id) IS NOT NULL)
  • Rows Removed by Filter: 1521
  • Buffers: shared hit=1090693 dirtied=109, temp read=20185 written=20185
13. 1,233.493 5,242.321 ↑ 2.4 1,377,285 1

Hash Left Join (cost=1,388,289.47..2,312,107.63 rows=3,336,438 width=16) (actual time=1,737.820..5,242.321 rows=1,377,285 loops=1)

  • Hash Cond: (COALESCE(p.idendereco, est.idendereco) = e_1.id)
  • Buffers: shared hit=1090693 dirtied=109, temp read=20185 written=20185
14. 371.755 2,448.432 ↑ 2.4 1,377,285 1

Merge Left Join (cost=65,540.00..917,626.90 rows=3,336,438 width=24) (actual time=162.451..2,448.432 rows=1,377,285 loops=1)

  • Merge Cond: (c_1.id = est.idcliente)
  • Join Filter: (p.idendereco IS NULL)
  • Rows Removed by Join Filter: 2182191
  • Buffers: shared hit=1029357 dirtied=36
15. 700.246 1,785.331 ↑ 2.5 1,318,768 1

Merge Left Join (cost=0.85..367,685.07 rows=3,336,438 width=16) (actual time=0.036..1,785.331 rows=1,318,768 loops=1)

  • Merge Cond: (c_1.id = p.idcliente)
  • Buffers: shared hit=1020877 dirtied=36
16. 95.554 95.554 ↑ 1.0 211,781 1

Index Only Scan using cliente_pkey on cliente c_1 (cost=0.42..27,295.54 rows=211,860 width=8) (actual time=0.019..95.554 rows=211,781 loops=1)

  • Heap Fetches: 211788
  • Buffers: shared hit=133760 dirtied=8
17. 989.531 989.531 ↑ 2.8 1,185,873 1

Index Scan using pedido_idcliente on pedido p (cost=0.43..601,546.88 rows=3,336,438 width=16) (actual time=0.011..989.531 rows=1,185,873 loops=1)

  • Buffers: shared hit=887117 dirtied=28
18. 207.091 291.346 ↓ 8.0 2,383,507 1

Sort (cost=65,538.06..66,285.11 rows=298,820 width=16) (actual time=162.404..291.346 rows=2,383,507 loops=1)

  • Sort Key: est.idcliente
  • Sort Method: quicksort Memory: 26153kB
  • Buffers: shared hit=8480
19. 84.255 84.255 ↑ 1.0 298,793 1

Seq Scan on estabelecimento est (cost=0.00..38,362.00 rows=298,820 width=16) (actual time=0.018..84.255 rows=298,793 loops=1)

  • Buffers: shared hit=8480
20. 865.774 1,560.396 ↓ 1.0 6,111,506 1

Hash (cost=672,467.10..672,467.10 rows=6,111,311 width=8) (actual time=1,560.396..1,560.396 rows=6,111,506 loops=1)

  • Buckets: 4194304 Batches: 4 Memory Usage: 92465kB
  • Buffers: shared hit=61336 dirtied=73, temp written=15664
21. 694.622 694.622 ↓ 1.0 6,111,506 1

Seq Scan on endereco e_1 (cost=0.00..672,467.10 rows=6,111,311 width=8) (actual time=0.025..694.622 rows=6,111,506 loops=1)

  • Buffers: shared hit=61336 dirtied=73
22. 0.276 0.276 ↑ 1.0 1 69

Index Scan using endereco_pkey on endereco e (cost=0.43..1.28 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=69)

  • Index Cond: (id = (max(e_1.id)))
  • Buffers: shared hit=276
Planning time : 0.972 ms
Execution time : 5,640.540 ms