explain.depesz.com

PostgreSQL's explain analyze made readable

Result: V7Hx

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 81,107.022 ↓ 0.0 0 1

Unique (cost=1,390,145.01..1,390,145.02 rows=1 width=93) (actual time=81,107.022..81,107.022 rows=0 loops=1)

2. 0.021 81,107.021 ↓ 0.0 0 1

Sort (cost=1,390,145.01..1,390,145.01 rows=1 width=93) (actual time=81,107.021..81,107.021 rows=0 loops=1)

  • Sort Key: con.ucpid__c, wo.c_property_frequency, pdb.i_nbr_trips, (sum(CASE WHEN (gst_pdb_trip_day_dtl_bidl.f_theo_slot >= (0.4 * gst_pdb_trip_day_dtl_bidl.f_act_slot)) THEN gst_pdb_trip_day_dtl_bidl.f_theo_slot ELSE (gst_pdb_trip_day_dtl_bidl.f_act_slot * 0.4) END)), (COALESCE(ov.f_worth, wo.f_property_worth))
  • Sort Method: quicksort Memory: 25kB
3. 0.009 81,107.000 ↓ 0.0 0 1

Merge Right Join (cost=1,389,650.37..1,390,145.01 rows=1 width=93) (actual time=81,107.000..81,107.000 rows=0 loops=1)

  • Merge Cond: (pdb_trip.i_dmid = (con.winet_id__c)::text)
4. 0.021 81,106.740 ↑ 52,710.0 1 1

GroupAggregate (cost=1,081,950.85..1,082,188.16 rows=52,710 width=112) (actual time=81,106.740..81,106.740 rows=1 loops=1)

  • Group Key: pdb_trip.i_dmid, pdb_trip.c_prop_cd
5. 60.996 81,106.719 ↑ 26,392.0 2 1

Sort (cost=1,081,950.85..1,081,977.24 rows=52,784 width=16) (actual time=81,106.718..81,106.719 rows=2 loops=1)

  • Sort Key: pdb_trip.i_dmid
  • Sort Method: quicksort Memory: 982kB
6. 2,583.105 81,045.723 ↑ 4.1 12,738 1

Bitmap Heap Scan on pdb_trip (cost=959,930.29..1,081,122.78 rows=52,784 width=16) (actual time=78,471.422..81,045.723 rows=12,738 loops=1)

  • Recheck Cond: ((d_end_dt > (CURRENT_DATE - '30 days'::interval day)) AND (c_prop_cd = 'LAS'::text))
  • Filter: (i_rated_days >= 1)
  • Rows Removed by Filter: 3931
  • Heap Blocks: exact=16609
7. 170.761 78,462.618 ↓ 0.0 0 1

BitmapAnd (cost=959,930.29..959,930.29 rows=61,885 width=0) (actual time=78,462.618..78,462.618 rows=0 loops=1)

8. 223.773 223.773 ↑ 3.6 613,643 1

Bitmap Index Scan on pdb_trip_end_dt_idx (cost=0.00..246,757.26 rows=2,218,096 width=0) (actual time=223.773..223.773 rows=613,643 loops=1)

  • Index Cond: (d_end_dt > (CURRENT_DATE - '30 days'::interval day))
9. 78,068.084 78,068.084 ↑ 1.0 6,677,188 1

Bitmap Index Scan on pdb_trip_prop_cd_idx (cost=0.00..713,167.70 rows=6,737,059 width=0) (actual time=78,068.084..78,068.084 rows=6,677,188 loops=1)

  • Index Cond: (c_prop_cd = 'LAS'::text)
10. 0.015 0.251 ↓ 0.0 0 1

Materialize (cost=307,699.52..307,772.37 rows=1 width=83) (actual time=0.251..0.251 rows=0 loops=1)

11. 0.002 0.236 ↓ 0.0 0 1

Nested Loop Left Join (cost=307,699.52..307,772.37 rows=1 width=83) (actual time=0.236..0.236 rows=0 loops=1)

12. 0.003 0.234 ↓ 0.0 0 1

Nested Loop Left Join (cost=307,699.41..307,772.16 rows=1 width=79) (actual time=0.234..0.234 rows=0 loops=1)

13. 0.013 0.231 ↓ 0.0 0 1

Nested Loop Left Join (cost=307,699.32..307,772.05 rows=1 width=73) (actual time=0.231..0.231 rows=0 loops=1)

14. 0.002 0.218 ↓ 0.0 0 1

Merge Left Join (cost=307,699.21..307,771.88 rows=1 width=59) (actual time=0.218..0.218 rows=0 loops=1)

  • Merge Cond: ((con.winet_id__c)::text = (gst_pdb_trip_day_dtl_bidl.i_dmid)::text)
15. 0.044 0.216 ↓ 0.0 0 1

Sort (cost=164.34..164.35 rows=1 width=27) (actual time=0.216..0.216 rows=0 loops=1)

  • Sort Key: con.winet_id__c
  • Sort Method: quicksort Memory: 25kB
16. 0.018 0.172 ↓ 0.0 0 1

Nested Loop (cost=0.23..164.34 rows=1 width=27) (actual time=0.172..0.172 rows=0 loops=1)

17. 0.118 0.118 ↓ 2.0 2 1

Index Scan using segment_member__subscriberkey_fk on segment_member sm (cost=0.11..160.22 rows=1 width=15) (actual time=0.102..0.118 rows=2 loops=1)

  • Index Cond: ((subscriber_key)::text = ANY ('{UCP-0251643555,UCP-0251513810}'::text[]))
  • Filter: ((segment_id)::text = '1938'::text)
  • Rows Removed by Filter: 2
18. 0.036 0.036 ↓ 0.0 0 2

Index Scan using contact_ucpid__c on contact con (cost=0.11..4.12 rows=1 width=27) (actual time=0.018..0.018 rows=0 loops=2)

  • Index Cond: ((ucpid__c)::text = (sm.subscriber_key)::text)
  • Filter: dominant__c
19. 0.000 0.000 ↓ 0.0 0

Materialize (cost=307,534.87..307,604.23 rows=6,606 width=44) (never executed)

20. 0.000 0.000 ↓ 0.0 0

GroupAggregate (cost=307,534.87..307,581.11 rows=6,606 width=48) (never executed)

  • Group Key: gst_pdb_trip_day_dtl_bidl.i_dmid, gst_pdb_trip_day_dtl_bidl.c_prop_cd
21. 0.000 0.000 ↓ 0.0 0

Sort (cost=307,534.87..307,538.17 rows=6,607 width=24) (never executed)

  • Sort Key: gst_pdb_trip_day_dtl_bidl.i_dmid
22. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on gst_pdb_trip_day_dtl_bidl (cost=5,167.77..307,451.02 rows=6,607 width=24) (never executed)

  • Recheck Cond: (d_date = ((now() - '5 days'::interval))::date)
  • Filter: ((c_prop_cd)::text = 'LAS'::text)
23. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on gst_pdb_trip_day_dtl_bidl_d_date_idx (cost=0.00..5,167.44 rows=160,880 width=0) (never executed)

  • Index Cond: (d_date = ((now() - '5 days'::interval))::date)
24. 0.000 0.000 ↓ 0.0 0

Index Scan using shelf_property_worth_uidx on shelf_property_worth wo (cost=0.11..0.21 rows=2 width=26) (never executed)

  • Index Cond: (((i_dmid)::text = (con.winet_id__c)::text) AND ((c_prop_cd)::text = 'LAS'::text))
25. 0.000 0.000 ↓ 0.0 0

Index Scan using shelf_property_worth_override_i_dmid_idx on shelf_property_worth_override ov (cost=0.09..0.11 rows=1 width=18) (never executed)

  • Index Cond: ((i_dmid)::text = (con.winet_id__c)::text)
  • Filter: ((c_prop_cd)::text = 'LAS'::text)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using pdb_12_month_plus_uidx on pdb_12_month_plus pdb (cost=0.11..0.21 rows=1 width=16) (never executed)

  • Index Cond: (((i_dmid)::text = (con.winet_id__c)::text) AND ((c_prop_cd)::text = 'LAS'::text))
Planning time : 6.581 ms
Execution time : 81,111.993 ms