explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BQdp

Settings
# exclusive inclusive rows x rows loops node
1. 22.268 6,817.991 ↓ 21.0 21 1

Limit (cost=942.06..950.48 rows=1 width=42) (actual time=6,817.838..6,817.991 rows=21 loops=1)

2.          

CTE t

3. 142.845 4,143.243 ↓ 9,901.2 1,000,021 1

Recursive Union (cost=0.43..91.29 rows=101 width=8) (actual time=0.319..4,143.243 rows=1,000,021 loops=1)

4. 0.002 0.318 ↑ 1.0 1 1

Limit (cost=0.43..0.85 rows=1 width=8) (actual time=0.317..0.318 rows=1 loops=1)

5. 0.316 0.316 ↑ 3,151,978.0 1 1

Index Scan using er_executionid_idx on execution_reports execution_reports_2 (cost=0.43..1,317,924.35 rows=3,151,978 width=8) (actual time=0.316..0.316 rows=1 loops=1)

  • Filter: ((transactiontime >= '2019-03-20 07:00:00'::timestamp without time zone) AND (transactiontime <= '2019-03-20 19:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 374
6. 0.000 4,000.080 ↑ 10.0 1 1,000,020

WorkTable Scan on t t_1 (cost=0.00..8.84 rows=10 width=8) (actual time=0.004..0.004 rows=1 loops=1,000,020)

  • Filter: (executionid IS NOT NULL)
7.          

SubPlan (forWorkTable Scan)

8. 4,000.080 4,000.080 ↑ 1.0 1 1,000,020

Limit (cost=0.43..0.86 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1,000,020)

  • -> Index Scan using er_executionid_idx on execution_reports execution_reports_1 (cost=0.43..450910.65 rows=1050659 width=8) (actual time=0.003..0.003 rows=1 l
  • Index Cond: (executionid < t_1.executionid)
  • Filter: ((transactiontime >= '2019-03-20 07:00:00'::timestamp without time zone) AND (transactiontime <= '2019-03-20 19:00:00'::timestamp without time zon
9. 486.500 6,795.723 ↓ 10,000.2 1,000,021 1

Nested Loop (cost=8.46..850.77 rows=100 width=42) (actual time=0.367..6,795.723 rows=1,000,021 loops=1)

10. 4,309.181 4,309.181 ↓ 10,000.2 1,000,021 1

CTE Scan on t (cost=0.00..2.02 rows=100 width=8) (actual time=0.323..4,309.181 rows=1,000,021 loops=1)

  • Filter: (executionid IS NOT NULL)
11. 0.000 2,000.042 ↑ 1.0 1 1,000,021

Sort (cost=8.46..8.47 rows=1 width=1,397) (actual time=0.002..0.002 rows=1 loops=1,000,021)

  • Sort Key: execution_reports.transactionid DESC
  • Sort Method: quicksort Memory: 25kB
12. 2,000.042 2,000.042 ↑ 1.0 1 1,000,021

Index Scan using er_executionid_idx on execution_reports (cost=0.43..8.45 rows=1 width=1,397) (actual time=0.001..0.002 rows=1 loops=1,000,021)

  • Index Cond: (executionid = t.executionid)
Planning time : 1.070 ms
Execution time : 6,827.784 ms