explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QQ32

Settings
# exclusive inclusive rows x rows loops node
1. 0.197 74.877 ↑ 16.7 12 1

Group (cost=139.69..8,484.87 rows=200 width=64) (actual time=3.084..74.877 rows=12 loops=1)

  • Group Key: months.date
2.          

CTE months

3. 0.040 0.170 ↑ 83.3 12 1

Sort (cost=67.36..69.86 rows=1,000 width=32) (actual time=0.165..0.170 rows=12 loops=1)

  • Sort Key: (to_char(series.month, 'YYYY-MM'::text)) DESC
  • Sort Method: quicksort Memory: 17kB
4. 0.070 0.130 ↑ 83.3 12 1

Subquery Scan on series (cost=0.00..17.53 rows=1,000 width=32) (actual time=0.054..0.130 rows=12 loops=1)

5. 0.055 0.060 ↑ 83.3 12 1

ProjectSet (cost=0.00..5.03 rows=1,000 width=8) (actual time=0.037..0.060 rows=12 loops=1)

6. 0.005 0.005 ↑ 1.0 1 1

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

7. 0.043 0.232 ↑ 83.3 12 1

Sort (cost=69.83..72.33 rows=1,000 width=32) (actual time=0.213..0.232 rows=12 loops=1)

  • Sort Key: months.date DESC
  • Sort Method: quicksort Memory: 17kB
8. 0.189 0.189 ↑ 83.3 12 1

CTE Scan on months (cost=0.00..20.00 rows=1,000 width=32) (actual time=0.173..0.189 rows=12 loops=1)

9.          

SubPlan (forGroup)

10. 1.020 74.448 ↑ 1.0 1 12

Aggregate (cost=41.69..41.70 rows=1 width=32) (actual time=6.203..6.204 rows=1 loops=12)

11. 1.848 73.428 ↓ 1.5 3 12

GroupAggregate (cost=41.62..41.66 rows=2 width=64) (actual time=6.005..6.119 rows=3 loops=12)

  • Group Key: (COALESCE(pt.name, t.name, 'Untagged'::text))
12. 1.476 71.580 ↓ 10.0 20 12

Sort (cost=41.62..41.63 rows=2 width=58) (actual time=5.950..5.965 rows=20 loops=12)

  • Sort Key: (COALESCE(pt.name, t.name, 'Untagged'::text))
  • Sort Method: quicksort Memory: 19kB
13. 1.554 70.104 ↓ 10.0 20 12

Nested Loop Left Join (cost=2.35..41.61 rows=2 width=58) (actual time=3.175..5.842 rows=20 loops=12)

14. 1.193 64.980 ↓ 10.0 20 12

Hash Left Join (cost=2.21..40.85 rows=2 width=19) (actual time=3.143..5.415 rows=20 loops=12)

  • Hash Cond: (ts.tag_id = t.tag_id)
15. 63.492 63.492 ↓ 10.0 20 12

Seq Scan on transactions ts (cost=0.00..38.62 rows=2 width=6) (actual time=3.096..5.291 rows=20 loops=12)

  • Filter: ((amount > 0) AND (tag_id <> ALL ('{99,98,8,31}'::integer[])) AND (to_char((date)::timestamp with time zone, 'YYYY-MM'::text) = months.date))
  • Rows Removed by Filter: 1045
16. 0.135 0.295 ↑ 1.0 54 1

Hash (cost=1.54..1.54 rows=54 width=19) (actual time=0.295..0.295 rows=54 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 7kB
17. 0.160 0.160 ↑ 1.0 54 1

Seq Scan on tags t (cost=0.00..1.54 rows=54 width=19) (actual time=0.066..0.160 rows=54 loops=1)

18. 3.570 3.570 ↑ 1.0 1 238

Index Scan using pk_tags on tags pt (cost=0.14..0.38 rows=1 width=15) (actual time=0.015..0.015 rows=1 loops=238)

  • Index Cond: (tag_id = t.parent_tag_id)
Planning time : 1.474 ms