explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 53BS : Items view slow list query

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 32,253.819 ↓ 10.0 10 1

Limit (cost=50,067.11..50,067.12 rows=1 width=414) (actual time=32,253.817..32,253.819 rows=10 loops=1)

2. 9.592 32,253.817 ↓ 10.0 10 1

Sort (cost=50,067.11..50,067.12 rows=1 width=414) (actual time=32,253.816..32,253.817 rows=10 loops=1)

  • Sort Key: items_raw.id
  • Sort Method: top-N heapsort Memory: 31kB
3. 11,902.371 32,244.225 ↓ 3,104.0 3,104 1

Nested Loop (cost=1,004.37..50,067.1 rows=1 width=414) (actual time=920.924..32,244.225 rows=3,104 loops=1)

4. 11,951.545 16,613.950 ↓ 3,104.0 3,104 1

Nested Loop (cost=1,004.37..48,899.8 rows=1 width=334) (actual time=910.583..16,613.95 rows=3,104 loops=1)

5. 41.228 953.125 ↓ 3,104.0 3,104 1

Nested Loop (cost=1,004.37..47,732.52 rows=1 width=328) (actual time=899.601..953.125 rows=3,104 loops=1)

6. 0.018 2.963 ↓ 6.0 6 1

Nested Loop (cost=0.13..825.39 rows=1 width=26) (actual time=2.938..2.963 rows=6 loops=1)

7. 0.009 0.009 ↑ 1.0 8 1

Index Scan using slug_uidx on wms_systems ws (cost=0.13..12.25 rows=8 width=10) (actual time=0.004..0.009 rows=8 loops=1)

8. 0.016 2.936 ↓ 3.0 6 8

Materialize (cost=0..812.9 rows=2 width=21) (actual time=0.023..0.367 rows=6 loops=8)

9. 2.920 2.920 ↓ 3.0 6 1

Seq Scan on sfdc_xref_customer cx (cost=0..812.89 rows=2 width=21) (actual time=0.176..2.92 rows=6 loops=1)

  • Filter: ((lin_customer_enterprise_id__c)::text = '10123'::text)
10. 130.404 908.934 ↓ 122.3 15,773 6

Gather (cost=1,004.24..46,903.91 rows=129 width=320) (actual time=38.02..151.489 rows=15,773 loops=6)

11. 174.642 778.530 ↓ 97.4 5,258 18

Hash Join (cost=4.24..45,891.01 rows=54 width=320) (actual time=34.787..129.755 rows=5,258 loops=18)

12. 603.618 603.618 ↑ 1.2 206,856 18

Seq Scan on items_raw items_raw (cost=0..43,951.23 rows=258,000 width=308) (actual time=0.128..100.603 rows=206,856 loops=18)

  • Filter: ((deleted_at IS NULL) AND (created_at >= '2018-11-11 12:19:48.748+00'::timestamp with time zone) AND (created_at <= '2019-09-07 12:19:48.748+00'::timestamp with time zone))
13. 0.030 0.270 ↑ 1.0 1 13

Hash (cost=4.22..4.22 rows=1 width=16) (actual time=0.045..0.045 rows=1 loops=13)

14. 0.240 0.240 ↑ 1.0 1 13

Seq Scan on facility_warehouse_link fwl (cost=0..4.22 rows=1 width=16) (actual time=0.034..0.04 rows=1 loops=13)

  • Filter: ((facility_id)::integer = 1)
15. 3,709.280 3,709.280 ↑ 1.0 15,751 3,104

Seq Scan on sfdc_xref_customer cxb (cost=0..773.51 rows=15,751 width=21) (actual time=0.048..1.195 rows=15,751 loops=3,104)

16. 3,727.904 3,727.904 ↑ 1.0 15,751 3,104

Seq Scan on sfdc_xref_customer cxo (cost=0..773.51 rows=15,751 width=21) (actual time=0.043..1.201 rows=15,751 loops=3,104)