explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R6iB

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=22,920,025.26..22,920,025.27 rows=1 width=8) (actual rows= loops=)

2.          

CTE claster

3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=131,673.24..202,491.20 rows=6,331 width=20) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on tbl_claster cl_1 (cost=131,672.80..161,692.30 rows=9,800 width=20) (actual rows= loops=)

  • Recheck Cond: ((date_change >= (now() - '7 days'::interval)) AND (date_change < now()) AND (club_status = ANY ('{0,1,2,3,4,6,7,8,9,10,11,12,13,14,15}'::integer[])))
  • Filter: (NOT (hashed SubPlan 1))
5. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=9,998.57..9,998.57 rows=19,601 width=0) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on tbl_claster_change_idx (cost=0.00..2,871.82 rows=261,341 width=0) (actual rows= loops=)

  • Index Cond: ((date_change >= (now() - '7 days'::interval)) AND (date_change < now()))
7. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on tbl_claster_clubstatus_idx (cost=0.00..7,121.60 rows=858,363 width=0) (actual rows= loops=)

  • Index Cond: (club_status = ANY ('{0,1,2,3,4,6,7,8,9,10,11,12,13,14,15}'::integer[]))
8.          

SubPlan (forBitmap Heap Scan)

9. 0.000 0.000 ↓ 0.0

Seq Scan on tbl_club_log log (cost=0.00..116,892.04 rows=1,912,876 width=4) (actual rows= loops=)

  • Filter: ((date(date_modify) >= '2019-02-05'::date) OR (date(date_create) >= '2019-02-05'::date))
10. 0.000 0.000 ↓ 0.0

Index Scan using tbl_customer_pkey2 on tbl_customer c (cost=0.43..4.16 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = cl_1.id)
  • Filter: (status <> ALL ('{-3,-10}'::integer[]))
11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=527.37..22,717,408.78 rows=50,111 width=4) (actual rows= loops=)

  • Join Filter: (claster.id = orders.customer_id)
  • Filter: (COALESCE(claster.last_invoice_date, '1970-01-01'::date) <> COALESCE(date(orders.date_invoice), '1970-01-01'::date))
12. 0.000 0.000 ↓ 0.0

CTE Scan on claster (cost=0.00..126.62 rows=6,331 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Materialize (cost=527.37..22,515,833.71 rows=1,591 width=12) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Subquery Scan on orders (cost=527.37..22,515,825.76 rows=1,591 width=12) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=527.37..22,515,809.85 rows=1,591 width=16) (actual rows= loops=)

  • Group Key: o.customer_id
16. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=527.37..22,515,785.98 rows=1,591 width=12) (actual rows= loops=)

  • Join Filter: ((item.date_return IS NULL) OR (item.date_return > cl.date_change))
17. 0.000 0.000 ↓ 0.0

Merge Join (cost=526.80..22,313,366.31 rows=61,454 width=24) (actual rows= loops=)

  • Merge Cond: (o.customer_id = cl.id)
  • Join Filter: ((o.date_invoice <= cl.date_change) AND ((o.status <> ALL ('{30,31,32,33,34,35,36,37}'::integer[])) OR ((SubPlan 3) > cl.date_change)))
18. 0.000 0.000 ↓ 0.0

Index Scan using tbl_order_cust_shop_idx on tbl_order o (cost=0.44..2,328,073.91 rows=17,365,246 width=18) (actual rows= loops=)

  • Filter: ((date_invoice IS NOT NULL) AND (status >= 10) AND ((COALESCE(catpro_id, ''::character varying))::text !~~ 'RUB2B%'::text) AND (shop_id <> ALL ('{9,8,19}'::integer[])))
19. 0.000 0.000 ↓ 0.0

Sort (cost=526.37..542.19 rows=6,331 width=12) (actual rows= loops=)

  • Sort Key: cl.id
20. 0.000 0.000 ↓ 0.0

CTE Scan on claster cl (cost=0.00..126.62 rows=6,331 width=12) (actual rows= loops=)

21.          

SubPlan (forMerge Join)

22. 0.000 0.000 ↓ 0.0

Aggregate (cost=90.49..90.50 rows=1 width=8) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan using tbl_order_act_log_idx_orderid on tbl_order_act_log order_log (cost=0.57..90.40 rows=36 width=8) (actual rows= loops=)

  • Index Cond: (order_id = o.id)
  • Filter: (status = o.status)
24. 0.000 0.000 ↓ 0.0

Index Scan using tbl_order_item_order_idx on tbl_order_item item (cost=0.57..2.05 rows=105 width=12) (actual rows= loops=)

  • Index Cond: (order_id = o.id)