explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xqul : Optimization for: plan #MS8r

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 4.253 59.726 ↑ 1.5 264 1

HashAggregate (cost=5,940.95..5,944.95 rows=400 width=64) (actual time=59.685..59.726 rows=264 loops=1)

  • Group Key: sub1.v, sub3.v
2.          

CTE sub1

3. 10.250 20.012 ↑ 3.2 2,001 1

HashAggregate (cost=1,432.67..1,496.63 rows=6,396 width=7) (actual time=19.573..20.012 rows=2,001 loops=1)

  • Group Key: d.e, d.v
4. 9.762 9.762 ↑ 1.0 31,926 1

Seq Scan on datoms4 d (cost=0.00..1,272.28 rows=32,079 width=7) (actual time=0.012..9.762 rows=31,926 loops=1)

  • Filter: (a = 'release/year'::text)
  • Rows Removed by Filter: 32096
5.          

CTE sub2

6. 5.937 51.473 ↑ 4.4 10 1

HashAggregate (cost=3,822.06..3,822.50 rows=44 width=3) (actual time=51.472..51.473 rows=10 loops=1)

  • Group Key: d_1.v
7. 6.075 45.536 ↑ 3.2 32,014 1

Hash Join (cost=1,670.01..3,567.54 rows=101,808 width=3) (actual time=38.632..45.536 rows=32,014 loops=1)

  • Hash Cond: (sub1_1.e = d_1.e)
8. 20.595 20.595 ↑ 3.2 2,001 1

CTE Scan on sub1 sub1_1 (cost=0.00..127.92 rows=6,396 width=4) (actual time=19.575..20.595 rows=2,001 loops=1)

9. 7.873 18.866 ↓ 1.0 31,938 1

Hash (cost=1,272.28..1,272.28 rows=31,819 width=7) (actual time=18.866..18.866 rows=31,938 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1504kB
10. 10.993 10.993 ↓ 1.0 31,938 1

Seq Scan on datoms4 d_1 (cost=0.00..1,272.28 rows=31,819 width=7) (actual time=0.014..10.993 rows=31,938 loops=1)

  • Filter: (a = 'release/artist'::text)
  • Rows Removed by Filter: 32084
11.          

CTE sub3

12. 0.011 51.859 ↓ 4.0 8 1

Unique (cost=238.00..238.01 rows=2 width=3) (actual time=51.845..51.859 rows=8 loops=1)

13. 0.187 51.848 ↓ 49.5 99 1

Sort (cost=238.00..238.01 rows=2 width=3) (actual time=51.845..51.848 rows=99 loops=1)

  • Sort Key: d_2.v
  • Sort Method: quicksort Memory: 29kB
14. 0.050 51.661 ↓ 49.5 99 1

Hash Join (cost=6.56..237.99 rows=2 width=3) (actual time=51.604..51.661 rows=99 loops=1)

  • Hash Cond: (d_2.e = (sub2.v)::integer)
15. 0.028 0.091 ↓ 1.1 101 1

Bitmap Heap Scan on datoms4 d_2 (cost=5.13..235.97 rows=92 width=7) (actual time=0.071..0.091 rows=101 loops=1)

  • Recheck Cond: (a = 'artist/name'::text)
  • Heap Blocks: exact=18
16. 0.063 0.063 ↓ 1.2 108 1

Bitmap Index Scan on _tt6 (cost=0.00..5.10 rows=92 width=0) (actual time=0.063..0.063 rows=108 loops=1)

  • Index Cond: (a = 'artist/name'::text)
17. 0.042 51.520 ↑ 4.4 10 1

Hash (cost=0.88..0.88 rows=44 width=32) (actual time=51.520..51.520 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 51.478 51.478 ↑ 4.4 10 1

CTE Scan on sub2 (cost=0.00..0.88 rows=44 width=32) (actual time=51.473..51.478 rows=10 loops=1)

19. 2.282 55.473 ↓ 1.3 16,008 1

Nested Loop (cost=0.00..319.84 rows=12,792 width=64) (actual time=51.848..55.473 rows=16,008 loops=1)

20. 51.863 51.863 ↓ 4.0 8 1

CTE Scan on sub3 (cost=0.00..0.04 rows=2 width=32) (actual time=51.846..51.863 rows=8 loops=1)

21. 1.328 1.328 ↑ 3.2 2,001 8

CTE Scan on sub1 (cost=0.00..127.92 rows=6,396 width=32) (actual time=0.000..0.166 rows=2,001 loops=8)

Planning time : 0.587 ms
Execution time : 60.102 ms