explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PpuQ

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

Subquery Scan on a (cost=2,530,339.62..2,548,121.57 rows=77,947 width=88) (actual time=5,467.581..5,467.738 rows=23 loops=1)

  • Buffers: shared hit=1130139, temp read=20185 written=20185
2. 0.180 5,467.734 ↑ 3,389.0 23 1

GroupAggregate (cost=2,530,339.62..2,540,326.87 rows=77,947 width=113) (actual time=5,467.579..5,467.734 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=1130139, temp read=20185 written=20185
3. 0.049 5,467.554 ↑ 1,135.3 69 1

Sort (cost=2,530,339.62..2,530,535.47 rows=78,339 width=45) (actual time=5,467.550..5,467.554 rows=69 loops=1)

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

Nested Loop Left Join (cost=2,399,744.77..2,523,971.66 rows=78,339 width=45) (actual time=5,421.205..5,467.505 rows=69 loops=1)

  • Buffers: shared hit=1130139, temp read=20185 written=20185
5. 29.879 5,467.316 ↑ 1,135.3 69 1

Hash Right Join (cost=2,399,744.33..2,423,597.68 rows=78,339 width=44) (actual time=5,421.192..5,467.316 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: 216056
  • Buffers: shared hit=1129863, temp read=20185 written=20185
6. 4.499 26.610 ↓ 1.2 23,500 1

Merge Right Join (cost=0.86..23,802.23 rows=19,763 width=36) (actual time=0.042..26.610 rows=23,500 loops=1)

  • Merge Cond: (pw.wishlist_id = w.id)
  • Buffers: shared hit=39215
7. 4.518 18.451 ↓ 1.0 11,405 1

Nested Loop Left Join (cost=0.58..20,180.58 rows=11,404 width=20) (actual time=0.030..18.451 rows=11,405 loops=1)

  • Buffers: shared hit=37281
8. 2.528 2.528 ↓ 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.018..2.528 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=34223
10. 3.660 3.660 ↓ 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.008..3.660 rows=19,764 loops=1)

  • Buffers: shared hit=1934
11. 37.551 5,410.827 ↑ 1.0 210,263 1

Hash (cost=2,378,136.37..2,378,136.37 rows=210,801 width=16) (actual time=5,410.827..5,410.827 rows=210,263 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 11905kB
  • Buffers: shared hit=1090648, temp read=20185 written=20185
12. 297.878 5,373.276 ↑ 1.0 210,263 1

HashAggregate (cost=2,335,870.27..2,357,056.27 rows=210,801 width=16) (actual time=5,325.838..5,373.276 rows=210,263 loops=1)

  • Group Key: c_1.id
  • Filter: (max(e_1.id) IS NOT NULL)
  • Rows Removed by Filter: 1521
  • Buffers: shared hit=1090648, temp read=20185 written=20185
13. 1,221.260 5,075.398 ↑ 2.4 1,377,303 1

Hash Left Join (cost=1,388,542.70..2,310,892.94 rows=3,330,310 width=16) (actual time=1,819.980..5,075.398 rows=1,377,303 loops=1)

  • Hash Cond: (COALESCE(p.idendereco, est.idendereco) = e_1.id)
  • Buffers: shared hit=1090648, temp read=20185 written=20185
14. 370.285 2,212.097 ↑ 2.4 1,377,303 1

Merge Left Join (cost=65,535.24..916,238.31 rows=3,330,310 width=24) (actual time=160.003..2,212.097 rows=1,377,303 loops=1)

  • Merge Cond: (c_1.id = est.idcliente)
  • Join Filter: (p.idendereco IS NULL)
  • Rows Removed by Join Filter: 2182206
  • Buffers: shared hit=1029300
15. 679.471 1,550.656 ↑ 2.5 1,318,786 1

Merge Left Join (cost=0.85..367,229.11 rows=3,330,310 width=16) (actual time=0.033..1,550.656 rows=1,318,786 loops=1)

  • Merge Cond: (c_1.id = p.idcliente)
  • Buffers: shared hit=1020820
16. 87.990 87.990 ↑ 1.0 211,784 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.016..87.990 rows=211,784 loops=1)

  • Heap Fetches: 211784
  • Buffers: shared hit=133755
17. 783.195 783.195 ↑ 2.8 1,185,888 1

Index Scan using pedido_idcliente on pedido p (cost=0.43..600,909.41 rows=3,330,310 width=16) (actual time=0.010..783.195 rows=1,185,888 loops=1)

  • Buffers: shared hit=887065
18. 218.335 291.156 ↓ 8.0 2,383,525 1

Sort (cost=65,533.31..66,280.30 rows=298,796 width=16) (actual time=159.962..291.156 rows=2,383,525 loops=1)

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

Seq Scan on estabelecimento est (cost=0.00..38,359.60 rows=298,796 width=16) (actual time=0.010..72.821 rows=298,796 loops=1)

  • Buffers: shared hit=8480
20. 973.495 1,642.041 ↑ 1.0 6,111,574 1

Hash (cost=672,598.60..672,598.60 rows=6,112,506 width=8) (actual time=1,642.041..1,642.041 rows=6,111,574 loops=1)

  • Buckets: 4194304 Batches: 4 Memory Usage: 92466kB
  • Buffers: shared hit=61348, temp written=15664
21. 668.546 668.546 ↑ 1.0 6,111,574 1

Seq Scan on endereco e_1 (cost=0.00..672,598.60 rows=6,112,506 width=8) (actual time=0.014..668.546 rows=6,111,574 loops=1)

  • Buffers: shared hit=61348
22. 0.138 0.138 ↑ 1.0 1 69

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

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