explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CoRJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 0.076 ↓ 0.0 0 1

Sort (cost=67.17..67.18 rows=1 width=1,632) (actual time=0.076..0.076 rows=0 loops=1)

  • Sort Key: customer.customer_id
  • Sort Method: quicksort Memory: 25kB
2. 0.000 0.073 ↓ 0.0 0 1

WindowAgg (cost=67.11..67.16 rows=1 width=1,632) (actual time=0.073..0.073 rows=0 loops=1)

3. 0.001 0.073 ↓ 0.0 0 1

WindowAgg (cost=67.11..67.13 rows=1 width=2,092) (actual time=0.073..0.073 rows=0 loops=1)

4. 0.001 0.072 ↓ 0.0 0 1

Sort (cost=67.11..67.12 rows=1 width=2,084) (actual time=0.072..0.072 rows=0 loops=1)

  • Sort Key: ((customer.customer_id % '100'::bigint))
  • Sort Method: quicksort Memory: 25kB
5. 0.000 0.071 ↓ 0.0 0 1

Group (cost=40.77..67.10 rows=1 width=2,084) (actual time=0.071..0.071 rows=0 loops=1)

  • Group Key: customer.customer_id
6. 0.001 0.071 ↓ 0.0 0 1

Nested Loop (cost=40.77..67.10 rows=1 width=2,076) (actual time=0.071..0.071 rows=0 loops=1)

  • Join Filter: (customer.customer_id = purchase.customer_id)
7. 0.002 0.019 ↓ 3.0 3 1

Unique (cost=8.17..8.20 rows=1 width=8) (actual time=0.017..0.019 rows=3 loops=1)

8. 0.004 0.017 ↓ 3.0 3 1

GroupAggregate (cost=8.17..8.20 rows=1 width=8) (actual time=0.016..0.017 rows=3 loops=1)

  • Group Key: purchase.customer_id
  • Filter: (count(*) FILTER (WHERE (purchase.purchase_date >= '2019-02-03 04:47:13.280225+00'::timestamp with time zone)) > 0)
9. 0.004 0.013 ↓ 5.0 5 1

Sort (cost=8.17..8.18 rows=1 width=16) (actual time=0.012..0.013 rows=5 loops=1)

  • Sort Key: purchase.customer_id
  • Sort Method: quicksort Memory: 25kB
10. 0.009 0.009 ↓ 5.0 5 1

Index Scan using idx_purchase_purchase_status_id on purchase (cost=0.14..8.16 rows=1 width=16) (actual time=0.007..0.009 rows=5 loops=1)

  • Index Cond: (purchase_status_id = 10)
  • Filter: (purchase_date >= '2019-02-03 04:47:13.280225+00'::timestamp with time zone)
11. 0.000 0.051 ↓ 0.0 0 3

Nested Loop Anti Join (cost=32.60..58.88 rows=1 width=2,084) (actual time=0.017..0.017 rows=0 loops=3)

12. 0.000 0.051 ↓ 0.0 0 3

Nested Loop (cost=32.45..50.70 rows=1 width=2,084) (actual time=0.017..0.017 rows=0 loops=3)

13. 0.026 0.051 ↓ 0.0 0 3

Index Scan using customer_customer_type_id_idx on customer (cost=28.24..36.28 rows=1 width=2,076) (actual time=0.017..0.017 rows=0 loops=3)

  • Index Cond: (customer_type_id = 10)
  • Filter: ((customer_status_id = ANY ('{10,30}'::bigint[])) AND (NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 2)) AND (NOT (hashed SubPlan 3)) AND (region_id <> ALL ('{2,9,4}'::bigint[])) AND (customer_registration_time < (now() - '180 days'::interval)))
  • Rows Removed by Filter: 6
14.          

SubPlan (for Index Scan)

15. 0.001 0.010 ↑ 1.0 1 1

Unique (cost=8.18..8.23 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)

16. 0.003 0.009 ↑ 1.0 1 1

GroupAggregate (cost=8.18..8.23 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)

  • Group Key: purchase_1.customer_id
  • Filter: ((count(*) FILTER (WHERE ((purchase_1.purchase_date >= '2019-02-03 04:47:13.260964+00'::timestamp with time zone) AND (purchase_1.product_type_id = 30))) > 0) OR (count(*) FILTER (WHERE ((purchase_1.lottery_id = 21) AND (purchase_1.draw_number = 12))) > 0) OR (count(*) FILTER (WHERE ((purchase_1.purchase_date >= '2019-02-03 04:47:13.278728+00'::timestamp with time zone) AND (purchase_1.purchase_type_id = 20))) > 0))
17. 0.001 0.006 ↓ 2.0 2 1

Sort (cost=8.18..8.19 rows=1 width=48) (actual time=0.006..0.006 rows=2 loops=1)

  • Sort Key: purchase_1.customer_id
  • Sort Method: quicksort Memory: 25kB
18. 0.005 0.005 ↓ 2.0 2 1

Index Scan using idx_purchase_purchase_status_id on purchase purchase_1 (cost=0.14..8.17 rows=1 width=48) (actual time=0.004..0.005 rows=2 loops=1)

  • Index Cond: (purchase_status_id = 10)
  • Filter: (((purchase_date >= '2019-02-03 04:47:13.260964+00'::timestamp with time zone) AND (product_type_id = 30)) OR ((lottery_id = 21) AND (draw_number = 12)) OR ((purchase_date >= '2019-02-03 04:47:13.278728+00'::timestamp with time zone) AND (purchase_type_id = 20)))
  • Rows Removed by Filter: 3
19. 0.000 0.007 ↓ 0.0 0 1

Unique (cost=8.17..8.20 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)

20. 0.001 0.007 ↓ 0.0 0 1

GroupAggregate (cost=8.17..8.20 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)

  • Group Key: recurring_purchase.customer_id
  • Filter: (count(*) FILTER (WHERE (recurring_purchase.lottery_id = 21)) > 0)
21. 0.004 0.006 ↓ 0.0 0 1

Sort (cost=8.17..8.18 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=1)

  • Sort Key: recurring_purchase.customer_id
  • Sort Method: quicksort Memory: 25kB
22. 0.002 0.002 ↓ 0.0 0 1

Index Scan using recurring_purchase_lottery_id_recurring_purchase_key on recurring_purchase (cost=0.14..8.16 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (lottery_id = 21)
  • Filter: (recurring_purchase_status_id = 10)
23. 0.002 0.008 ↓ 3.0 3 1

GroupAggregate (cost=11.63..11.66 rows=1 width=8) (actual time=0.008..0.008 rows=3 loops=1)

  • Group Key: device_session.customer_id
  • Filter: (count(*) FILTER (WHERE (device_session.device_platform_id = 20)) > 0)
24. 0.001 0.006 ↓ 3.0 3 1

Sort (cost=11.63..11.64 rows=1 width=16) (actual time=0.006..0.006 rows=3 loops=1)

  • Sort Key: device_session.customer_id
  • Sort Method: quicksort Memory: 25kB
25. 0.005 0.005 ↓ 3.0 3 1

Seq Scan on device_session (cost=0.00..11.62 rows=1 width=16) (actual time=0.004..0.005 rows=3 loops=1)

  • Filter: (device_platform_id = 20)
  • Rows Removed by Filter: 2
26. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on customer_subscription (cost=4.21..14.41 rows=1 width=8) (never executed)

  • Recheck Cond: (customer_id = customer.customer_id)
  • Filter: (customer_subscription_value AND ((subscription_id = 2) OR (subscription_id = 1)))
27. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on customer_subscription_customer_id_idx (cost=0.00..4.21 rows=8 width=0) (never executed)

  • Index Cond: (customer_id = customer.customer_id)
28. 0.000 0.000 ↓ 0.0 0

Index Only Scan using excluded_email_type_excluded_email_address on excluded_email (cost=0.14..8.16 rows=1 width=516) (never executed)

  • Index Cond: (excluded_email_address = (customer.email)::text)
  • Heap Fetches: 0
Planning time : 0.600 ms
Execution time : 0.177 ms