explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lSOx

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 99,204.824 ↓ 10.0 10 1

Nested Loop Left Join (cost=12,550.24..12,562.02 rows=1 width=335) (actual time=99,204.735..99,204.824 rows=10 loops=1)

  • Join Filter: ((vsf.scorecard_name)::text = (btrim((cr1.metadata ->> 'scorecardName'::text))))
2. 0.012 99,204.731 ↓ 10.0 10 1

Limit (cost=12,550.24..12,550.25 rows=1 width=362) (actual time=99,204.718..99,204.731 rows=10 loops=1)

3.          

CTE score_values

4. 1.879 9,311.287 ↓ 199.0 2,189 1

GroupAggregate (cost=10,981.87..10,982.09 rows=11 width=36) (actual time=9,308.652..9,311.287 rows=2,189 loops=1)

  • Group Key: ((cr2_1.metadata ->> 'scheduleId'::text))
5. 6.295 9,309.408 ↓ 218.5 2,404 1

Sort (cost=10,981.87..10,981.90 rows=11 width=36) (actual time=9,308.646..9,309.408 rows=2,404 loops=1)

  • Sort Key: ((cr2_1.metadata ->> 'scheduleId'::text))
  • Sort Method: quicksort Memory: 209kB
6. 9,303.113 9,303.113 ↓ 218.5 2,404 1

Seq Scan on custom_reports cr2_1 (cost=0.00..10,981.68 rows=11 width=36) (actual time=839.973..9,303.113 rows=2,404 loops=1)

  • Filter: ((metadata IS NOT NULL) AND (report_type = 6) AND ((metadata ->> 'isScheduledExport'::text) = '1'::text) AND (date > (CURRENT_DATE - '14 days'::interval day)))
  • Rows Removed by Filter: 64388
7. 1.803 99,204.719 ↓ 10.0 10 1

Sort (cost=1,568.16..1,568.16 rows=1 width=362) (actual time=99,204.716..99,204.719 rows=10 loops=1)

  • Sort Key: cr1.ch_score DESC, cr1.id DESC, ((cr1.metadata ->> 'scorecardName'::text))
  • Sort Method: top-N heapsort Memory: 33kB
8. 85,978.107 99,202.916 ↓ 1,084.0 1,084 1

Nested Loop Left Join (cost=92.11..1,568.15 rows=1 width=362) (actual time=11,943.055..99,202.916 rows=1,084 loops=1)

  • Join Filter: (cht.id = CASE WHEN ((cr1.metadata ->> 'templateId'::text) <> ''::text) THEN ((cr1.metadata ->> 'templateId'::text))::integer ELSE 0 END)
  • Rows Removed by Join Filter: 39797
9. 1,232.719 13,211.801 ↓ 1,084.0 1,084 1

Nested Loop (cost=92.11..1,560.70 rows=1 width=961) (actual time=11,942.565..13,211.801 rows=1,084 loops=1)

10. 18.083 11,974.714 ↓ 1,092.0 1,092 1

Hash Right Join (cost=91.95..1,559.89 rows=1 width=961) (actual time=11,942.508..11,974.714 rows=1,092 loops=1)

  • Hash Cond: (prior_select.id = cr1.id)
11. 14.149 14.149 ↑ 1.1 53,633 1

Seq Scan on prior_select (cost=0.00..1,239.13 rows=61,013 width=20) (actual time=0.011..14.149 rows=53,633 loops=1)

12. 1.155 11,942.482 ↓ 1,092.0 1,092 1

Hash (cost=91.94..91.94 rows=1 width=945) (actual time=11,942.482..11,942.482 rows=1,092 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1147kB
13. 34.677 11,941.327 ↓ 1,092.0 1,092 1

Nested Loop (cost=0.29..91.94 rows=1 width=945) (actual time=9,308.688..11,941.327 rows=1,092 loops=1)

14. 9,312.685 9,312.685 ↓ 199.0 2,189 1

CTE Scan on score_values cr2 (cost=0.00..0.22 rows=11 width=36) (actual time=9,308.655..9,312.685 rows=2,189 loops=1)

15. 2,593.965 2,593.965 ↓ 0.0 0 2,189

Index Scan using custom_reports_pkey on custom_reports cr1 (cost=0.29..8.33 rows=1 width=913) (actual time=1.185..1.185 rows=0 loops=2,189)

  • Index Cond: (id = cr2.max_id)
  • Filter: ((metadata IS NOT NULL) AND ((metadata ->> 'isScheduledExport'::text) = '1'::text) AND (((metadata ->> 'total_number'::text))::integer >= 20))
  • Rows Removed by Filter: 1
16. 4.368 4.368 ↑ 1.0 1 1,092

Index Scan using users_id_pkey on users u (cost=0.15..0.77 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1,092)

  • Index Cond: (id = ((cr1.metadata ->> 'createdById'::text))::integer)
  • Filter: ((active IS TRUE) AND ((email)::text !~~ '%@contentanalyticsinc.com'::text) AND (lower((username)::text) <> 'admin'::text))
  • Rows Removed by Filter: 0
17. 13.008 13.008 ↑ 1.8 38 1,084

Seq Scan on ch_templates cht (cost=0.00..5.69 rows=69 width=36) (actual time=0.001..0.012 rows=38 loops=1,084)

18. 0.080 0.080 ↓ 0.0 0 10

Seq Scan on vendor_scorecards_favorites vsf (cost=0.00..11.75 rows=1 width=521) (actual time=0.008..0.008 rows=0 loops=10)

  • Filter: (user_id = 123)
  • Rows Removed by Filter: 106
Planning time : 1.697 ms
Execution time : 99,205.022 ms