explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WaCN

Settings
# exclusive inclusive rows x rows loops node
1. 0.841 11,989.013 ↓ 9.6 7,265 1

Limit (cost=15,993,365.80..17,278,578.72 rows=756 width=157) (actual time=9,012.205..11,989.013 rows=7,265 loops=1)

  • Output: a1.id, a1.name, (('http://www.'::text || a1.url)), ((SubPlan 2)), (date_part('year'::text, (COALESCE((SubPlan 3), (SubPlan 4)))::timestamp without time zone)), a1.c117701, a1.c200440, a1.c200016, a1.c117702, a1.c200053, a1.c200054, a1.top_level_industry, a1.sub_level_industry, a1.hq_country, a1.hq_state, a1.id, a3.""order"
  • Buffers: shared hit=13,629,396
2.          

Initplan (for Limit)

3. 0.007 0.007 ↑ 1.0 1 1

Result (cost=0.00..0.02 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Output: (((timezone('America/New_York'::text, now()))::date - '2 years'::interval))::date
4. 13.018 11,988.165 ↓ 9.6 7,265 1

Result (cost=15,993,365.77..17,278,578.70 rows=756 width=157) (actual time=9,012.203..11,988.165 rows=7,265 loops=1)

  • Output: a1.id, a1.name, (('http://www.'::text || a1.url)), (SubPlan 2), date_part('year'::text, (COALESCE((SubPlan 3), (SubPlan 4)))::timestamp without time zone), a1.c117701, a1.c200440, a1.c200016, a1.c117702, a1.c200053, a1.c200054, a1.top_level_industry, a1.sub_level_industry, a1.hq_country, a1.hq_state, a1.id, a3.""order"
  • Buffers: shared hit=13,629,396
5. 18.943 9,011.027 ↓ 9.6 7,265 1

Sort (cost=15,993,365.77..15,993,367.66 rows=756 width=145) (actual time=9,009.174..9,011.027 rows=7,265 loops=1)

  • Output: a1.id, a1.name, (('http://www.'::text || a1.url)), a1.c117701, a1.c200440, a1.c200016, a1.c117702, a1.c200053, a1.c200054, a1.top_level_industry, a1.sub_level_industry, a1.hq_country, a1.hq_state, a1.id, a3.""order"
  • Sort Key: a3.""order"" NULLS FIRST, a1.c200016 DESC NULLS LAST, a1.id
  • Sort Method: quicksort Memory: 1,984kB
  • Buffers: shared hit=10,918,325
6. 11.392 8,992.084 ↓ 9.6 7,265 1

Hash Left Join (cost=43,361.99..15,993,329.63 rows=756 width=145) (actual time=16.344..8,992.084 rows=7,265 loops=1)

  • Output: a1.id, a1.name, ('http://www.'::text || a1.url), a1.c117701, a1.c200440, a1.c200016, a1.c117702, a1.c200053, a1.c200054, a1.top_level_industry, a1.sub_level_industry, a1.hq_country, a1.hq_state, a1.id, a3.""order"
  • Inner Unique: true
  • Hash Cond: ((SubPlan 6) = a3.id)
  • Buffers: shared hit=10,918,325
7. 12.390 5,274.892 ↓ 9.6 7,265 1

Nested Loop (cost=43,295.00..15,961,329.34 rows=756 width=122) (actual time=15.152..5,274.892 rows=7,265 loops=1)

  • Output: a1.id, a1.name, a1.url, a1.c117701, a1.c200440, a1.c200016, a1.c117702, a1.c200053, a1.c200054, a1.top_level_industry, a1.sub_level_industry, a1.hq_country, a1.hq_state
  • Inner Unique: true
  • Buffers: shared hit=5,568,874
8. 4.672 17.172 ↓ 1.6 7,265 1

HashAggregate (cost=43,294.56..43,340.77 rows=4,621 width=4) (actual time=14.130..17.172 rows=7,265 loops=1)

  • Output: software_product_usage.software_customer_id
  • Group Key: software_product_usage.software_customer_id
  • Buffers: shared hit=8,637
9. 10.506 12.500 ↓ 1.6 7,265 1

Bitmap Heap Scan on public.software_product_usage (cost=221.77..43,282.99 rows=4,628 width=4) (actual time=3.122..12.500 rows=7,265 loops=1)

  • Output: software_product_usage.software_product_id, software_product_usage.software_customer_id, software_product_usage.first_verified, software_product_usage.last_verified, software_product_usage.intensity, software_product_usage.location_count, software_product_usage.digital_signature, software_product_usage.decile, software_product_usage.software_vendor_id, software_product_usage.id, software_product_usage.c129611
  • Recheck Cond: (software_product_usage.software_product_id = ANY ('{12392}'::integer[]))
  • Filter: ((software_product_usage.decile >= 5) AND (software_product_usage.last_verified >= (CURRENT_DATE - 730)))
  • Rows Removed by Filter: 8,023
  • Heap Blocks: exact=8,592
  • Buffers: shared hit=8,637
10. 1.994 1.994 ↓ 1.3 15,288 1

Bitmap Index Scan on software_product_usage_software_product_id_software_customer_id (cost=0.00..220.61 rows=11,739 width=0) (actual time=1.994..1.994 rows=15,288 loops=1)

  • Index Cond: (software_product_usage.software_product_id = ANY ('{12392}'::integer[]))
  • Buffers: shared hit=45
11. 58.120 5,245.330 ↑ 1.0 1 7,265

Index Scan using software_product_customers_pk on public.software_product_customers a1 (cost=0.43..3,445.45 rows=1 width=122) (actual time=0.722..0.722 rows=1 loops=7,265)

  • Output: a1.id, a1.name, a1.url, a1.stock_id, a1.private_company_id, a1.revenue, a1.employees, a1.top_level_industry, a1.sub_level_industry, a1.hq_address, a1.hq_city, a1.hq_state, a1.hq_zip, a1.hq_country, a1.hq_phone, a1.is_clean, a1.is_used, a1.c122140, a1.c122141, a1.c122142, a1.c122143, a1.c122144, a1.c122145, a1.c117696, a1.c117658, a1.c117659, a1.c117660, a1.c117661, a1.c117662, a1.c117663, a1.c117664, a1.c117665, a1.c117666, a1.c117667, a1.c117668, a1.c117669, a1.c117670, a1.c117699, a1.c117700, a1.c117701, a1.c117702, a1.c117703, a1.c117704, a1.c117705, a1.c117706, a1.c200015, a1.c200016, a1.c200026, a1.c200042, a1.c200043, a1.c200055, a1.c200056, a1.c200063, a1.c200053, a1.c200054, a1.c200064, a1.c200065, a1.c200075, a1.c200076, a1.c200127, a1.c200129, a1.c200225, a1.c200440, a1.c200443, a1.c200444, a1.c200445, a1.c200446, a1.c200447, a1.c200448, a1.c200449, a1.c200450, a1.c200451, a1.c200452, a1.c200453, a1.c200454, a1.c200455, a1.c200524, a1.c200525, a1.c200526, a1.c200558, a1.c200559, a1.c200563, a1.c200568
  • Index Cond: (a1.id = software_product_usage.software_customer_id)
  • Filter: (((SubPlan 21) IS NOT NULL) AND ((SubPlan 19) >= $20) AND ((SubPlan 18) IS NOT NULL) AND ((SubPlan 16) <> 21210) AND (SubPlan 9) AND (((alternatives: SubPlan 10 or hashed SubPlan 11) AND (SubPlan 14)) IS NOT NULL))
  • Buffers: shared hit=5,560,237
12.          

SubPlan (for Index Scan)

13. 0.000 29.060 ↑ 1.0 1 7,265

Aggregate (cost=8.59..8.60 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=7,265)

  • Output: max(software_product_usage_14.last_verified)
  • Buffers: shared hit=36,358
14. 29.060 29.060 ↑ 1.0 1 7,265

Index Scan using software_product_usage_software_product_id_software_customer_id on public.software_product_usage software_product_usage_14 (cost=0.57..8.59 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=7,265)

  • Output: software_product_usage_14.software_product_id, software_product_usage_14.software_customer_id, software_product_usage_14.first_verified, software_product_usage_14.last_verified, software_product_usage_14.intensity, software_product_usage_14.location_count, software_product_usage_14.digital_signature, software_product_usage_14.decile, software_product_usage_14.software_vendor_id, software_product_usage_14.id, software_product_usage_14.c129611
  • Index Cond: ((software_product_usage_14.software_product_id = ANY ('{12392}'::integer[])) AND (software_product_usage_14.software_customer_id = a1.id))
  • Buffers: shared hit=36,358
15. 0.000 14.530 ↑ 1.0 1 7,265

Aggregate (cost=8.59..8.60 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=7,265)

  • Output: max(software_product_usage_13.last_verified)
  • Buffers: shared hit=36,358
16. 14.530 14.530 ↑ 1.0 1 7,265

Index Scan using software_product_usage_software_product_id_software_customer_id on public.software_product_usage software_product_usage_13 (cost=0.57..8.59 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=7,265)

  • Output: software_product_usage_13.software_product_id, software_product_usage_13.software_customer_id, software_product_usage_13.first_verified, software_product_usage_13.last_verified, software_product_usage_13.intensity, software_product_usage_13.location_count, software_product_usage_13.digital_signature, software_product_usage_13.decile, software_product_usage_13.software_vendor_id, software_product_usage_13.id, software_product_usage_13.c129611
  • Index Cond: ((software_product_usage_13.software_product_id = ANY ('{12392}'::integer[])) AND (software_product_usage_13.software_customer_id = a1.id))
  • Buffers: shared hit=36,358
17. 14.530 2,956.855 ↑ 1.0 1 7,265

Aggregate (cost=844.67..844.69 rows=1 width=4) (actual time=0.407..0.407 rows=1 loops=7,265)

  • Output: CASE WHEN (avg(software_product_usage_12.decile) >= 9.5) THEN 21206 WHEN (avg(software_product_usage_12.decile) >= '9'::numeric) THEN 21207 WHEN (avg(software_product_usage_12.decile) >= '8'::numeric) THEN 21208 WHEN (avg(software_product_usage_12.decile) >= '5'::numeric) THEN 21209 ELSE 21210 END
  • Buffers: shared hit=2,674,713
18. 2,658.976 2,942.325 ↑ 21.0 1 7,265

Bitmap Heap Scan on public.software_product_usage software_product_usage_12 (cost=576.88..844.45 rows=21 width=4) (actual time=0.204..0.405 rows=1 loops=7,265)

  • Output: software_product_usage_12.software_product_id, software_product_usage_12.software_customer_id, software_product_usage_12.first_verified, software_product_usage_12.last_verified, software_product_usage_12.intensity, software_product_usage_12.location_count, software_product_usage_12.digital_signature, software_product_usage_12.decile, software_product_usage_12.software_vendor_id, software_product_usage_12.id, software_product_usage_12.c129611
  • Recheck Cond: (software_product_usage_12.software_customer_id = a1.id)
  • Filter: ((software_product_usage_12.decile >= 5) AND ((software_product_usage_12.software_product_id = ANY ('{12392}'::integer[])) OR (hashed SubPlan 17)))
  • Rows Removed by Filter: 407
  • Heap Blocks: exact=2,637,494
  • Buffers: shared hit=2,674,713
19. 283.335 283.335 ↓ 6.1 408 7,265

Bitmap Index Scan on software_product_usage_software_customer_id_index (cost=0.00..5.07 rows=67 width=0) (actual time=0.039..0.039 rows=408 loops=7,265)

  • Index Cond: (software_product_usage_12.software_customer_id = a1.id)
  • Buffers: shared hit=37,218
20.          

SubPlan (for Bitmap Heap Scan)

21. 0.007 0.014 ↓ 0.0 0 1

Hash Semi Join (cost=2.77..571.29 rows=206 width=4) (actual time=0.013..0.014 rows=0 loops=1)

  • Output: software_products_4.id
  • Hash Cond: (software_products_4.software_vendor_id = (unnest(NULL::integer[])))
  • Buffers: shared hit=1
22. 0.004 0.004 ↑ 13,721.0 1 1

Seq Scan on public.software_products software_products_4 (cost=0.00..530.21 rows=13,721 width=8) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: software_products_4.id, software_products_4.entity_id, software_products_4.hg_id, software_products_4.name, software_products_4.f116786, software_products_4.f116787, software_products_4.vendor, software_products_4.software_vendor_id, software_products_4.c200017, software_products_4.c200018, software_products_4.c200019, software_products_4.c200020
  • Buffers: shared hit=1
23. 0.001 0.003 ↓ 0.0 0 1

Hash (cost=1.52..1.52 rows=100 width=4) (actual time=0.002..0.003 rows=0 loops=1)

  • Output: (unnest(NULL::integer[]))
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
24. 0.001 0.002 ↓ 0.0 0 1

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: unnest(NULL::integer[])
25. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

26. 14.530 2,121.380 ↑ 1.0 1 7,265

Aggregate (cost=844.67..844.69 rows=1 width=4) (actual time=0.292..0.292 rows=1 loops=7,265)

  • Output: CASE WHEN (avg(software_product_usage_11.decile) >= 9.5) THEN 21206 WHEN (avg(software_product_usage_11.decile) >= '9'::numeric) THEN 21207 WHEN (avg(software_product_usage_11.decile) >= '8'::numeric) THEN 21208 WHEN (avg(software_product_usage_11.decile) >= '5'::numeric) THEN 21209 ELSE 21210 END
  • Buffers: shared hit=2,674,713
27. 1,845.300 2,106.850 ↑ 21.0 1 7,265

Bitmap Heap Scan on public.software_product_usage software_product_usage_11 (cost=576.88..844.45 rows=21 width=4) (actual time=0.158..0.290 rows=1 loops=7,265)

  • Output: software_product_usage_11.software_product_id, software_product_usage_11.software_customer_id, software_product_usage_11.first_verified, software_product_usage_11.last_verified, software_product_usage_11.intensity, software_product_usage_11.location_count, software_product_usage_11.digital_signature, software_product_usage_11.decile, software_product_usage_11.software_vendor_id, software_product_usage_11.id, software_product_usage_11.c129611
  • Recheck Cond: (software_product_usage_11.software_customer_id = a1.id)
  • Filter: ((software_product_usage_11.decile >= 5) AND ((software_product_usage_11.software_product_id = ANY ('{12392}'::integer[])) OR (hashed SubPlan 15)))
  • Rows Removed by Filter: 407
  • Heap Blocks: exact=2,637,494
  • Buffers: shared hit=2,674,713
28. 261.540 261.540 ↓ 6.1 408 7,265

Bitmap Index Scan on software_product_usage_software_customer_id_index (cost=0.00..5.07 rows=67 width=0) (actual time=0.036..0.036 rows=408 loops=7,265)

  • Index Cond: (software_product_usage_11.software_customer_id = a1.id)
  • Buffers: shared hit=37,218
29.          

SubPlan (for Bitmap Heap Scan)

30. 0.005 0.010 ↓ 0.0 0 1

Hash Semi Join (cost=2.77..571.29 rows=206 width=4) (actual time=0.009..0.010 rows=0 loops=1)

  • Output: software_products_3.id
  • Hash Cond: (software_products_3.software_vendor_id = (unnest(NULL::integer[])))
  • Buffers: shared hit=1
31. 0.003 0.003 ↑ 13,721.0 1 1

Seq Scan on public.software_products software_products_3 (cost=0.00..530.21 rows=13,721 width=8) (actual time=0.003..0.003 rows=1 loops=1)

  • Output: software_products_3.id, software_products_3.entity_id, software_products_3.hg_id, software_products_3.name, software_products_3.f116786, software_products_3.f116787, software_products_3.vendor, software_products_3.software_vendor_id, software_products_3.c200017, software_products_3.c200018, software_products_3.c200019, software_products_3.c200020
  • Buffers: shared hit=1
32. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=1.52..1.52 rows=100 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: (unnest(NULL::integer[]))
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
33. 0.002 0.002 ↓ 0.0 0 1

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: unnest(NULL::integer[])
34. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

35. 7.265 29.060 ↑ 1.0 1 7,265

Aggregate (cost=861.00..861.01 rows=1 width=1) (actual time=0.004..0.004 rows=1 loops=7,265)

  • Output: bool_and((alternatives: SubPlan 7 or hashed SubPlan 8))
  • Buffers: shared hit=36,341
36. 0.000 0.000 ↑ 100.0 1 7,265

Function Scan on pg_catalog.unnest i (cost=0.00..1.00 rows=100 width=4) (actual time=0.000..0.000 rows=1 loops=7,265)

  • Output: i.i
  • Function Call: unnest('{12392}'::integer[])
37.          

SubPlan (for Aggregate)

38. 21.795 21.795 ↑ 1.0 1 7,265

Index Scan using software_product_usage_software_product_id_software_customer_id on public.software_product_usage software_product_usage_5 (cost=0.57..8.60 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=7,265)

  • Index Cond: ((software_product_usage_5.software_product_id = i.i) AND (software_product_usage_5.software_customer_id = a1.id))
  • Filter: ((software_product_usage_5.decile >= 5) AND (software_product_usage_5.last_verified >= (CURRENT_DATE - 730)))
  • Buffers: shared hit=36,341
39. 0.000 0.000 ↓ 0.0 0

Index Scan using software_product_usage_software_customer_id_index on public.software_product_usage software_product_usage_6 (cost=0.57..273.26 rows=27 width=4) (never executed)

  • Output: software_product_usage_6.software_product_id
  • Index Cond: (software_product_usage_6.software_customer_id = a1.id)
  • Filter: ((software_product_usage_6.decile >= 5) AND (software_product_usage_6.last_verified >= (CURRENT_DATE - 730)))
40. 14.530 14.530 ↑ 1.0 1 7,265

Index Scan using software_product_usage_software_product_id_software_customer_id on public.software_product_usage software_product_usage_7 (cost=0.57..8.60 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=7,265)

  • Index Cond: ((software_product_usage_7.software_product_id = ANY ('{12392}'::integer[])) AND (software_product_usage_7.software_customer_id = a1.id))
  • Filter: ((software_product_usage_7.decile >= 5) AND (software_product_usage_7.last_verified >= (CURRENT_DATE - 730)))
  • Buffers: shared hit=36,341
41. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on public.software_product_usage software_product_usage_8 (cost=221.77..43,282.99 rows=4,628 width=4) (never executed)

  • Output: software_product_usage_8.software_customer_id
  • Recheck Cond: (software_product_usage_8.software_product_id = ANY ('{12392}'::integer[]))
  • Filter: ((software_product_usage_8.decile >= 5) AND (software_product_usage_8.last_verified >= (CURRENT_DATE - 730)))
42. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on software_product_usage_software_product_id_software_customer_id (cost=0.00..220.61 rows=11,739 width=0) (never executed)

  • Index Cond: (software_product_usage_8.software_product_id = ANY ('{12392}'::integer[]))
43. 7.265 21.795 ↑ 1.0 1 7,265

Aggregate (cost=861.00..861.01 rows=1 width=1) (actual time=0.003..0.003 rows=1 loops=7,265)

  • Output: bool_and((alternatives: SubPlan 12 or hashed SubPlan 13))
  • Buffers: shared hit=36,341
44. 0.000 0.000 ↑ 100.0 1 7,265

Function Scan on pg_catalog.unnest i_1 (cost=0.00..1.00 rows=100 width=4) (actual time=0.000..0.000 rows=1 loops=7,265)

  • Output: i_1.i
  • Function Call: unnest('{12392}'::integer[])
45.          

SubPlan (for Aggregate)

46. 14.530 14.530 ↑ 1.0 1 7,265

Index Scan using software_product_usage_software_product_id_software_customer_id on public.software_product_usage software_product_usage_9 (cost=0.57..8.60 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=7,265)

  • Index Cond: ((software_product_usage_9.software_product_id = i_1.i) AND (software_product_usage_9.software_customer_id = a1.id))
  • Filter: ((software_product_usage_9.decile >= 5) AND (software_product_usage_9.last_verified >= (CURRENT_DATE - 730)))
  • Buffers: shared hit=36,341
47. 0.000 0.000 ↓ 0.0 0

Index Scan using software_product_usage_software_customer_id_index on public.software_product_usage software_product_usage_10 (cost=0.57..273.26 rows=27 width=4) (never executed)

  • Output: software_product_usage_10.software_product_id
  • Index Cond: (software_product_usage_10.software_customer_id = a1.id)
  • Filter: ((software_product_usage_10.decile >= 5) AND (software_product_usage_10.last_verified >= (CURRENT_DATE - 730)))
48. 0.295 0.650 ↓ 1.0 1,833 1

Hash (cost=44.22..44.22 rows=1,822 width=8) (actual time=0.650..0.650 rows=1,833 loops=1)

  • Output: a3.""order"", a3.id
  • Buckets: 2,048 Batches: 1 Memory Usage: 88kB
  • Buffers: shared hit=26
49. 0.355 0.355 ↓ 1.0 1,833 1

Seq Scan on public.tag_values a3 (cost=0.00..44.22 rows=1,822 width=8) (actual time=0.005..0.355 rows=1,833 loops=1)

  • Output: a3.""order"", a3.id
  • Buffers: shared hit=26
50.          

SubPlan (for Hash Left Join)

51. 29.060 3,705.150 ↑ 1.0 1 14,530

Aggregate (cost=844.67..844.69 rows=1 width=4) (actual time=0.255..0.255 rows=1 loops=14,530)

  • Output: CASE WHEN (avg(software_product_usage_4.decile) >= 9.5) THEN 21206 WHEN (avg(software_product_usage_4.decile) >= '9'::numeric) THEN 21207 WHEN (avg(software_product_usage_4.decile) >= '8'::numeric) THEN 21208 WHEN (avg(software_product_usage_4.decile) >= '5'::numeric) THEN 21209 ELSE 21210 END
  • Buffers: shared hit=5,349,425
52. 3,167.528 3,676.090 ↑ 21.0 1 14,530

Bitmap Heap Scan on public.software_product_usage software_product_usage_4 (cost=576.88..844.45 rows=21 width=4) (actual time=0.140..0.253 rows=1 loops=14,530)

  • Output: software_product_usage_4.software_product_id, software_product_usage_4.software_customer_id, software_product_usage_4.first_verified, software_product_usage_4.last_verified, software_product_usage_4.intensity, software_product_usage_4.location_count, software_product_usage_4.digital_signature, software_product_usage_4.decile, software_product_usage_4.software_vendor_id, software_product_usage_4.id, software_product_usage_4.c129611
  • Recheck Cond: (software_product_usage_4.software_customer_id = a1.id)
  • Filter: ((software_product_usage_4.decile >= 5) AND ((software_product_usage_4.software_product_id = ANY ('{12392}'::integer[])) OR (hashed SubPlan 5)))
  • Rows Removed by Filter: 407
  • Heap Blocks: exact=5,274,988
  • Buffers: shared hit=5,349,425
53. 508.550 508.550 ↓ 6.1 408 14,530

Bitmap Index Scan on software_product_usage_software_customer_id_index (cost=0.00..5.07 rows=67 width=0) (actual time=0.035..0.035 rows=408 loops=14,530)

  • Index Cond: (software_product_usage_4.software_customer_id = a1.id)
  • Buffers: shared hit=74,436
54.          

SubPlan (for Bitmap Heap Scan)

55. 0.008 0.012 ↓ 0.0 0 1

Hash Semi Join (cost=2.77..571.29 rows=206 width=4) (actual time=0.011..0.012 rows=0 loops=1)

  • Output: software_products_2.id
  • Hash Cond: (software_products_2.software_vendor_id = (unnest(NULL::integer[])))
  • Buffers: shared hit=1
56. 0.002 0.002 ↑ 13,721.0 1 1

Seq Scan on public.software_products software_products_2 (cost=0.00..530.21 rows=13,721 width=8) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: software_products_2.id, software_products_2.entity_id, software_products_2.hg_id, software_products_2.name, software_products_2.f116786, software_products_2.f116787, software_products_2.vendor, software_products_2.software_vendor_id, software_products_2.c200017, software_products_2.c200018, software_products_2.c200019, software_products_2.c200020
  • Buffers: shared hit=1
57. 0.001 0.002 ↓ 0.0 0 1

Hash (cost=1.52..1.52 rows=100 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: (unnest(NULL::integer[]))
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
58. 0.001 0.001 ↓ 0.0 0 1

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: unnest(NULL::integer[])
59. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

60.          

SubPlan (for Result)

61. 14.530 2,942.325 ↑ 1.0 1 7,265

Aggregate (cost=844.67..844.69 rows=1 width=4) (actual time=0.405..0.405 rows=1 loops=7,265)

  • Output: CASE WHEN (avg(software_product_usage_1.decile) >= 9.5) THEN 21206 WHEN (avg(software_product_usage_1.decile) >= '9'::numeric) THEN 21207 WHEN (avg(software_product_usage_1.decile) >= '8'::numeric) THEN 21208 WHEN (avg(software_product_usage_1.decile) >= '5'::numeric) THEN 21209 ELSE 21210 END
  • Buffers: shared hit=2,674,713
62. 2,651.713 2,927.795 ↑ 21.0 1 7,265

Bitmap Heap Scan on public.software_product_usage software_product_usage_1 (cost=576.88..844.45 rows=21 width=4) (actual time=0.203..0.403 rows=1 loops=7,265)

  • Output: software_product_usage_1.software_product_id, software_product_usage_1.software_customer_id, software_product_usage_1.first_verified, software_product_usage_1.last_verified, software_product_usage_1.intensity, software_product_usage_1.location_count, software_product_usage_1.digital_signature, software_product_usage_1.decile, software_product_usage_1.software_vendor_id, software_product_usage_1.id, software_product_usage_1.c129611
  • Recheck Cond: (software_product_usage_1.software_customer_id = a1.id)
  • Filter: ((software_product_usage_1.decile >= 5) AND ((software_product_usage_1.software_product_id = ANY ('{12392}'::integer[])) OR (hashed SubPlan 1)))
  • Rows Removed by Filter: 407
  • Heap Blocks: exact=2,637,494
  • Buffers: shared hit=2,674,713
63. 276.070 276.070 ↓ 6.1 408 7,265

Bitmap Index Scan on software_product_usage_software_customer_id_index (cost=0.00..5.07 rows=67 width=0) (actual time=0.038..0.038 rows=408 loops=7,265)

  • Index Cond: (software_product_usage_1.software_customer_id = a1.id)
  • Buffers: shared hit=37,218
64.          

SubPlan (for Bitmap Heap Scan)

65. 0.005 0.012 ↓ 0.0 0 1

Hash Semi Join (cost=2.77..571.29 rows=206 width=4) (actual time=0.012..0.012 rows=0 loops=1)

  • Output: software_products.id
  • Hash Cond: (software_products.software_vendor_id = (unnest(NULL::integer[])))
  • Buffers: shared hit=1
66. 0.004 0.004 ↑ 13,721.0 1 1

Seq Scan on public.software_products (cost=0.00..530.21 rows=13,721 width=8) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: software_products.id, software_products.entity_id, software_products.hg_id, software_products.name, software_products.f116786, software_products.f116787, software_products.vendor, software_products.software_vendor_id, software_products.c200017, software_products.c200018, software_products.c200019, software_products.c200020
  • Buffers: shared hit=1
67. 0.001 0.003 ↓ 0.0 0 1

Hash (cost=1.52..1.52 rows=100 width=4) (actual time=0.002..0.003 rows=0 loops=1)

  • Output: (unnest(NULL::integer[]))
  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
68. 0.001 0.002 ↓ 0.0 0 1

ProjectSet (cost=0.00..0.52 rows=100 width=4) (actual time=0.002..0.002 rows=0 loops=1)

  • Output: unnest(NULL::integer[])
69. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)

70. 0.000 21.795 ↑ 1.0 1 7,265

Aggregate (cost=8.59..8.60 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=7,265)

  • Output: min(software_product_usage_2.first_verified)
  • Buffers: shared hit=36,358
71. 21.795 21.795 ↑ 1.0 1 7,265

Index Scan using software_product_usage_software_product_id_software_customer_id on public.software_product_usage software_product_usage_2 (cost=0.57..8.59 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=7,265)

  • Output: software_product_usage_2.software_product_id, software_product_usage_2.software_customer_id, software_product_usage_2.first_verified, software_product_usage_2.last_verified, software_product_usage_2.intensity, software_product_usage_2.location_count, software_product_usage_2.digital_signature, software_product_usage_2.decile, software_product_usage_2.software_vendor_id, software_product_usage_2.id, software_product_usage_2.c129611
  • Index Cond: ((software_product_usage_2.software_product_id = ANY ('{12392}'::integer[])) AND (software_product_usage_2.software_customer_id = a1.id))
  • Buffers: shared hit=36,358
72. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=846.69..846.70 rows=1 width=4) (never executed)

  • Output: min(software_product_usage_3.first_verified)
73. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=574.43..846.68 rows=5 width=4) (never executed)

  • Output: software_product_usage_3.first_verified
  • Hash Cond: (software_product_usage_3.software_product_id = software_products_1.id)
74. 0.000 0.000 ↓ 0.0 0

Index Scan using software_product_usage_software_customer_id_index on public.software_product_usage software_product_usage_3 (cost=0.57..272.59 rows=67 width=8) (never executed)

  • Output: software_product_usage_3.software_product_id, software_product_usage_3.software_customer_id, software_product_usage_3.first_verified, software_product_usage_3.last_verified, software_product_usage_3.intensity, software_product_usage_3.location_count, software_product_usage_3.digital_signature, software_product_usage_3.decile, software_product_usage_3.software_vendor_id, software_product_usage_3.id, software_product_usage_3.c129611
  • Index Cond: (software_product_usage_3.software_customer_id = a1.id)
75. 0.000 0.000 ↓ 0.0 0

Hash (cost=571.29..571.29 rows=206 width=4) (never executed)

  • Output: software_products_1.id
76. 0.000 0.000 ↓ 0.0 0

Hash Semi Join (cost=2.77..571.29 rows=206 width=4) (never executed)

  • Output: software_products_1.id
  • Hash Cond: (software_products_1.software_vendor_id = (unnest(NULL::integer[])))
77. 0.000 0.000 ↓ 0.0 0

Seq Scan on public.software_products software_products_1 (cost=0.00..530.21 rows=13,721 width=8) (never executed)

  • Output: software_products_1.id, software_products_1.entity_id, software_products_1.hg_id, software_products_1.name, software_products_1.f116786, software_products_1.f116787, software_products_1.vendor, software_products_1.software_vendor_id, software_products_1.c200017, software_products_1.c200018, software_products_1.c200019, software_products_1.c200020
78. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.52..1.52 rows=100 width=4) (never executed)

  • Output: (unnest(NULL::integer[]))
79. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..0.52 rows=100 width=4) (never executed)

  • Output: unnest(NULL::integer[])
80. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

Planning time : 2.146 ms
Execution time : 11,990.252 ms