explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vlEm

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 130,718.723 ↓ 100.0 100 1

Limit (cost=176,466.29..176,466.30 rows=1 width=12) (actual time=130,718.652..130,718.723 rows=100 loops=1)

2. 0.053 130,718.712 ↓ 100.0 100 1

Unique (cost=176,466.29..176,466.30 rows=1 width=12) (actual time=130,718.651..130,718.712 rows=100 loops=1)

3. 228.662 130,718.659 ↓ 100.0 100 1

Sort (cost=176,466.29..176,466.30 rows=1 width=12) (actual time=130,718.650..130,718.659 rows=100 loops=1)

  • Sort Key: first_time_orderings.created_at DESC NULLS LAST, buyer_vendors.id
  • Sort Method: external merge Disk: 1024kB
4. 65,684.537 130,489.997 ↓ 70,336.0 70,336 1

Nested Loop Left Join (cost=25,989.84..176,466.28 rows=1 width=12) (actual time=742.384..130,489.997 rows=70,336 loops=1)

  • Join Filter: (first_time_orderings.buyer_vendor_id = buyer_vendors.id)
  • Rows Removed by Join Filter: 900432359
5. 227.727 6,838.546 ↓ 67,247.0 67,247 1

Hash Anti Join (cost=25,989.84..176,079.01 rows=1 width=4) (actual time=740.416..6,838.546 rows=67,247 loops=1)

  • Hash Cond: (account_assignments.sales_rep_id = users.id)
6. 311.403 6,369.386 ↓ 3.0 283,209 1

Nested Loop Left Join (cost=13,184.53..161,578.26 rows=95,586 width=8) (actual time=497.911..6,369.386 rows=283,209 loops=1)

7. 282.042 2,591.138 ↓ 2.4 231,123 1

Hash Join (cost=13,184.11..111,690.75 rows=95,586 width=8) (actual time=497.885..2,591.138 rows=231,123 loops=1)

  • Hash Cond: (buyer_vendors.buyer_id = buyers.id)
8. 1,183.255 2,116.468 ↓ 2.4 231,123 1

Hash Right Join (cost=10,854.23..108,046.56 rows=95,586 width=12) (actual time=305.226..2,116.468 rows=231,123 loops=1)

  • Hash Cond: (accounts.id = buyer_vendors.account_id)
9. 629.296 629.296 ↑ 1.0 1,979,614 1

Seq Scan on accounts (cost=0.00..55,705.93 rows=1,980,193 width=4) (actual time=0.733..629.296 rows=1,979,614 loops=1)

10. 93.832 303.917 ↓ 2.4 231,123 1

Hash (cost=9,192.40..9,192.40 rows=95,586 width=12) (actual time=303.917..303.917 rows=231,123 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 8 (originally 2) Memory Usage: 3368kB
11. 73.094 210.085 ↓ 2.4 231,123 1

Hash Join (cost=176.66..9,192.40 rows=95,586 width=12) (actual time=8.756..210.085 rows=231,123 loops=1)

  • Hash Cond: (buyer_vendors.vendor_id = vendors.id)
12. 128.273 128.273 ↑ 1.0 256,388 1

Seq Scan on buyer_vendors (cost=0.00..7,097.86 rows=256,538 width=16) (actual time=0.019..128.273 rows=256,388 loops=1)

  • Filter: (active AND ((type)::text = 'SubscribedBuyerVendor'::text))
  • Rows Removed by Filter: 21300
13. 0.213 8.718 ↓ 1.5 724 1

Hash (cost=170.71..170.71 rows=476 width=4) (actual time=8.718..8.718 rows=724 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
14. 8.505 8.505 ↓ 1.5 724 1

Seq Scan on vendors (cost=0.00..170.71 rows=476 width=4) (actual time=1.968..8.505 rows=724 loops=1)

  • Filter: (((NOT demo) OR (demo IS NULL)) AND ((NOT staged) OR (staged IS NULL)) AND ((NOT locked) OR (locked IS NULL)))
  • Rows Removed by Filter: 547
15. 49.142 192.628 ↑ 1.0 60,163 1

Hash (cost=1,577.17..1,577.17 rows=60,217 width=4) (actual time=192.628..192.628 rows=60,163 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2628kB
16. 143.486 143.486 ↑ 1.0 60,163 1

Seq Scan on buyers (cost=0.00..1,577.17 rows=60,217 width=4) (actual time=0.465..143.486 rows=60,163 loops=1)

17. 3,466.845 3,466.845 ↑ 2.0 1 231,123

Index Scan using index_account_assignments_on_account_id on account_assignments (cost=0.43..0.50 rows=2 width=8) (actual time=0.013..0.015 rows=1 loops=231,123)

  • Index Cond: (account_id = accounts.id)
18. 36.845 241.433 ↑ 1.0 150,582 1

Hash (cost=10,331.69..10,331.69 rows=150,769 width=4) (actual time=241.433..241.433 rows=150,582 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3680kB
19. 204.588 204.588 ↑ 1.0 150,582 1

Seq Scan on users (cost=0.00..10,331.69 rows=150,769 width=4) (actual time=0.758..204.588 rows=150,582 loops=1)

20. 57,966.914 57,966.914 ↑ 1.0 13,390 67,247

Seq Scan on first_time_orderings (cost=0.00..219.90 rows=13,390 width=12) (actual time=0.003..0.862 rows=13,390 loops=67,247)

Planning time : 67.791 ms
Execution time : 130,719.736 ms