explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bHDd : Optimization for: plan #9s4n

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 9.596 8,603.458 ↓ 231.0 231 1

Hash Left Join (cost=178,258.62..178,263.82 rows=1 width=1,202) (actual time=8,589.072..8,603.458 rows=231 loops=1)

  • Hash Cond: (pi.id = zo.id)
  • Filter: (((zo.zoho ->> 'ra_invoice'::text) IS NULL) AND ((zo.zoho ->> 'ta_invoice'::text) IS NULL))
  • Rows Removed by Filter: 14762
2.          

CTE src_zoho

3. 19.163 70.294 ↑ 1.0 17,797 1

HashAggregate (cost=1,853.84..2,031.81 rows=17,797 width=168) (actual time=63.105..70.294 rows=17,797 loops=1)

  • Group Key: ""*SELECT* 1"".id, ""*SELECT* 1"".number, ""*SELECT* 1"".purchaseorder, ""*SELECT* 1"".total, ('ra_invoice'::text), ""*SELECT* 1"".date
4. 2.186 51.131 ↑ 1.0 17,797 1

Append (cost=341.21..1,586.88 rows=17,797 width=168) (actual time=12.780..51.131 rows=17,797 loops=1)

5. 1.611 17.092 ↑ 1.0 6,534 1

Subquery Scan on *SELECT* 1 (cost=341.21..520.90 rows=6,534 width=89) (actual time=12.779..17.092 rows=6,534 loops=1)

6. 8.315 15.481 ↑ 1.0 6,534 1

HashAggregate (cost=341.21..455.56 rows=6,534 width=89) (actual time=12.777..15.481 rows=6,534 loops=1)

  • Group Key: source_zoho_ra.id, source_zoho_ra.number, source_zoho_ra.purchaseorder, source_zoho_ra.total, 'ra_invoice'::text, ((source_zoho_ra.date)::date)::timestamp without time zone
7. 7.166 7.166 ↑ 1.0 7,422 1

Seq Scan on source_zoho_ra (cost=0.00..229.88 rows=7,422 width=89) (actual time=0.619..7.166 rows=7,422 loops=1)

8. 2.712 31.853 ↑ 1.0 11,263 1

Subquery Scan on *SELECT* 2 (cost=667.26..977.00 rows=11,263 width=84) (actual time=24.390..31.853 rows=11,263 loops=1)

9. 16.057 29.141 ↑ 1.0 11,263 1

HashAggregate (cost=667.26..864.37 rows=11,263 width=84) (actual time=24.388..29.141 rows=11,263 loops=1)

  • Group Key: source_zoho_ta.id, source_zoho_ta.number, source_zoho_ta.purchaseorder, source_zoho_ta.total, 'ta_invoice'::text, ((source_zoho_ta.date)::date)::timestamp without time zone
10. 13.084 13.084 ↑ 1.0 14,562 1

Seq Scan on source_zoho_ta (cost=0.00..448.83 rows=14,562 width=84) (actual time=0.467..13.084 rows=14,562 loops=1)

11.          

CTE flat_history

12. 233.364 973.164 ↓ 1.0 27,400 1

Index Scan using idx_28e0e656d053dd74 on sales_order_packages (cost=0.42..174,611.32 rows=27,152 width=40) (actual time=0.128..973.164 rows=27,400 loops=1)

  • Index Cond: (shipping_box IS NOT NULL)
  • Filter: ((updatedat > '2018-12-25 00:00:00'::timestamp without time zone) AND (((status ->> 'L'::text) <> 'RECEIVED_ON_STOCK'::text) OR (NOT (status ? 'L'::text))))
  • Rows Removed by Filter: 73839
13.          

SubPlan (forIndex Scan)

14. 0.000 219.200 ↑ 1.0 1 27,400

Limit (cost=0.00..1.26 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=27,400)

15. 219.200 219.200 ↑ 1.0 1 27,400

Function Scan on jsonb_array_elements (cost=0.00..1.26 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=27,400)

  • Filter: (value @> '{"to": ["SHIPMENT_NEW_HUB"]}'::jsonb)
  • Rows Removed by Filter: 3
16. 27.400 191.800 ↑ 1.0 1 27,400

Limit (cost=0.00..1.26 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=27,400)

17. 164.400 164.400 ↑ 1.0 1 27,400

Function Scan on jsonb_array_elements jsonb_array_elements_1 (cost=0.00..1.26 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=27,400)

  • Filter: (value @> '{"to": ["REQUEST_SENT"]}'::jsonb)
  • Rows Removed by Filter: 2
18. 27.400 164.400 ↑ 1.0 1 27,400

Limit (cost=0.00..1.26 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=27,400)

19. 137.000 137.000 ↑ 1.0 1 27,400

Function Scan on jsonb_array_elements jsonb_array_elements_2 (cost=0.00..1.26 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=27,400)

  • Filter: (value @> '{"to": ["DISPATCHED"]}'::jsonb)
  • Rows Removed by Filter: 1
20. 27.400 164.400 ↑ 1.0 1 27,400

Limit (cost=0.00..1.26 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=27,400)

21. 137.000 137.000 ↑ 1.0 1 27,400

Function Scan on jsonb_array_elements jsonb_array_elements_3 (cost=0.00..1.26 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=27,400)

  • Filter: (value @> '{"to": ["DELIVERED"]}'::jsonb)
  • Rows Removed by Filter: 1
22.          

CTE packed_invoices

23. 7,359.368 8,410.881 ↓ 111.9 14,993 1

Nested Loop (cost=0.42..1,003.57 rows=134 width=132) (actual time=0.360..8,410.881 rows=14,993 loops=1)

24. 990.505 990.505 ↓ 113.0 15,252 1

CTE Scan on flat_history fh (cost=0.00..678.80 rows=135 width=40) (actual time=0.178..990.505 rows=15,252 loops=1)

  • Filter: ((date_packed IS NOT NULL) AND (date_packed >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND (date_packed < '2019-02-01 00:00:00+00'::timestamp with time zone))
  • Rows Removed by Filter: 12148
25. 61.008 61.008 ↑ 1.0 1 15,252

Index Scan using sales_order_packages_pkey on sales_order_packages sop (cost=0.42..2.35 rows=1 width=94) (actual time=0.004..0.004 rows=1 loops=15,252)

  • Index Cond: (id = fh.id)
  • Filter: ((status ->> 'G'::text) <> 'CANCELLED'::text)
  • Rows Removed by Filter: 0
26.          

CTE package_vs_zoho

27. 50.584 8,577.165 ↓ 111.9 14,993 1

HashAggregate (cost=605.89..607.57 rows=134 width=40) (actual time=8,564.397..8,577.165 rows=14,993 loops=1)

  • Group Key: bi.id
28. 11.146 8,526.581 ↓ 1.3 15,229 1

Hash Right Join (cost=4.36..546.27 rows=11,924 width=72) (actual time=8,499.046..8,526.581 rows=15,229 loops=1)

  • Hash Cond: (zi2.number = (bi.external_invoice)::text)
29. 79.510 79.510 ↑ 1.0 17,797 1

CTE Scan on src_zoho zi2 (cost=0.00..355.94 rows=17,797 width=96) (actual time=63.108..79.510 rows=17,797 loops=1)

30. 7.688 8,435.925 ↓ 111.9 14,993 1

Hash (cost=2.68..2.68 rows=134 width=524) (actual time=8,435.925..8,435.925 rows=14,993 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 919kB
31. 8,428.237 8,428.237 ↓ 111.9 14,993 1

CTE Scan on packed_invoices bi (cost=0.00..2.68 rows=134 width=524) (actual time=0.001..8,428.237 rows=14,993 loops=1)

32. 5.257 5.257 ↓ 111.9 14,993 1

CTE Scan on packed_invoices pi (cost=0.00..2.68 rows=134 width=1,138) (actual time=0.365..5.257 rows=14,993 loops=1)

33. 3.600 8,588.605 ↓ 111.9 14,993 1

Hash (cost=2.68..2.68 rows=134 width=40) (actual time=8,588.604..8,588.605 rows=14,993 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1410kB
34. 8,585.005 8,585.005 ↓ 111.9 14,993 1

CTE Scan on package_vs_zoho zo (cost=0.00..2.68 rows=134 width=40) (actual time=8,564.400..8,585.005 rows=14,993 loops=1)