explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d5Pp

Settings
# exclusive inclusive rows x rows loops node
1. 4.677 8,611.293 ↑ 23.1 49 1

Group (cost=0.69..1,944,916.22 rows=1,134 width=112) (actual time=0.190..8,611.293 rows=49 loops=1)

  • Group Key: l.manufacturer, l.pharmacy, p.id
2. 13.466 8,597.208 ↓ 3.5 19,963 1

Nested Loop (cost=0.69..1,835,816.42 rows=5,643 width=363) (actual time=0.120..8,597.208 rows=19,963 loops=1)

3. 27.738 8,500.674 ↓ 1.0 20,767 1

Index Only Scan using "Whitelists_manufacturer_pharmacy_idx" on "Whitelists" l (cost=0.41..1,828,071.23 rows=20,663 width=32) (actual time=0.113..8,500.674 rows=20,767 loops=1)

  • Index Cond: (manufacturer = '304ef979-b4f1-492f-b0a9-eab86be556cb'::uuid)
  • Filter: (((SubPlan 3))::text <> 'EMPTY'::text)
  • Heap Fetches: 20767
4.          

SubPlan (for Index Only Scan)

5. 20.767 8,472.936 ↑ 1.0 1 20,767

Limit (cost=87.86..87.86 rows=1 width=10) (actual time=0.407..0.408 rows=1 loops=20,767)

6. 2,948.914 8,452.169 ↑ 24.0 1 20,767

Sort (cost=87.86..87.92 rows=24 width=10) (actual time=0.407..0.407 rows=1 loops=20,767)

  • Sort Key: l2_1.rev DESC
  • Sort Method: top-N heapsort Memory: 25kB
7. 5,503.255 5,503.255 ↓ 35.8 860 20,767

Index Scan using "Whitelists_manufacturer_pharmacy_idx" on "Whitelists" l2_1 (cost=0.41..87.74 rows=24 width=10) (actual time=0.007..0.265 rows=860 loops=20,767)

  • Index Cond: ((manufacturer = l.manufacturer) AND (pharmacy = l.pharmacy))
8. 83.068 83.068 ↑ 1.0 1 20,767

Index Scan using "Clients_pkey1" on "Clients" p (cost=0.28..0.36 rows=1 width=331) (actual time=0.003..0.004 rows=1 loops=20,767)

  • Index Cond: (id = l.pharmacy)
  • Filter: ((NOT deleted) AND ((status)::text = 'active'::text))
  • Rows Removed by Filter: 0
9.          

SubPlan (for Group)

10. 0.049 0.049 ↓ 0.0 0 49

Index Scan using "Clients_pkey1" on "Clients" pp (cost=0.29..8.31 rows=1 width=30) (actual time=0.001..0.001 rows=0 loops=49)

  • Index Cond: (id = ((p.data ->> 'group'::text))::uuid)
11. 0.098 9.359 ↑ 1.0 1 49

Limit (cost=87.86..87.86 rows=1 width=25) (actual time=0.191..0.191 rows=1 loops=49)

12. 3.332 9.261 ↑ 24.0 1 49

Sort (cost=87.86..87.92 rows=24 width=25) (actual time=0.189..0.189 rows=1 loops=49)

  • Sort Key: l2.rev DESC
  • Sort Method: top-N heapsort Memory: 25kB
13. 5.929 5.929 ↓ 17.0 407 49

Index Scan using "Whitelists_manufacturer_pharmacy_idx" on "Whitelists" l2 (cost=0.41..87.74 rows=24 width=25) (actual time=0.008..0.121 rows=407 loops=49)

  • Index Cond: ((manufacturer = l.manufacturer) AND (pharmacy = l.pharmacy))
Planning time : 0.498 ms
Execution time : 8,611.397 ms