Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A silly torturous PG plan
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=1124.07..1134.09 rows=201 width=136) (actual time=13.030..13.052 rows=13 loops=1)
CTE months
-> Function Scan on generate_series x (cost=0.00..25.00 rows=1000 width=4) (actual time=0.042..0.064 rows=12 loops=1)
CTE monthly_income
-> HashAggregate (cost=293.73..293.74 rows=1 width=42) (actual time=12.623..12.625 rows=10 loops=1)
-> Seq Scan on ledger (cost=0.00..293.52 rows=12 width=42) (actual time=10.223..12.497 rows=27 loops=1)
Filter: ((account ~ 'Income:Consulting'::text) AND ((jtags ->> 'Client'::text) IS NOT NULL))
Rows Removed by Filter: 2741
CTE retainer_income
-> HashAggregate (cost=621.10..623.10 rows=200 width=40) (actual time=0.202..0.206 rows=12 loops=1)
-> Nested Loop (cost=0.00..121.10 rows=8000 width=40) (actual time=0.004..0.021 rows=96 loops=1)
-> CTE Scan on months (cost=0.00..20.00 rows=1000 width=8) (actual time=0.000..0.002 rows=12 loops=1)
-> Materialize (cost=0.00..1.12 rows=8 width=32) (actual time=0.000..0.001 rows=8 loops=12)
-> Seq Scan on projects (cost=0.00..1.08 rows=8 width=32) (actual time=0.003..0.004 rows=8 loops=1)
CTE revenue
-> Merge Left Join (cost=81.50..109.08 rows=1000 width=200) (actual time=12.964..12.973 rows=12 loops=1)
Merge Cond: (months_1.xtn_month = retainer_income.xtn_month)
-> Merge Left Join (cost=69.86..74.94 rows=1000 width=72) (actual time=12.744..12.747 rows=12 loops=1)
Merge Cond: (months_1.xtn_month = monthly_income.xtn_month)
-> Sort (cost=69.83..72.33 rows=1000 width=8) (actual time=0.091..0.091 rows=12 loops=1)
Sort Key: months_1.xtn_month
Sort Method: quicksort Memory: 25kB
-> CTE Scan on months months_1 (cost=0.00..20.00 rows=1000 width=8) (actual time=0.045..0.073 rows=12 loops=1)
-> Materialize (cost=0.03..0.04 rows=1 width=68) (actual time=12.647..12.649 rows=10 loops=1)
-> Sort (cost=0.03..0.04 rows=1 width=68) (actual time=12.643..12.645 rows=10 loops=1)
Sort Key: monthly_income.xtn_month
Sort Method: quicksort Memory: 25kB
-> CTE Scan on monthly_income (cost=0.00..0.02 rows=1 width=68) (actual time=12.624..12.628 rows=10 loops=1)
-> Sort (cost=11.64..12.14 rows=200 width=136) (actual time=0.216..0.217 rows=12 loops=1)
Sort Key: retainer_income.xtn_month
Sort Method: quicksort Memory: 25kB
-> CTE Scan on retainer_income (cost=0.00..4.00 rows=200 width=136) (actual time=0.203..0.211 rows=12 loops=1)
CTE totals
-> Subquery Scan on x_1 (cost=69.64..73.14 rows=200 width=104) (actual time=13.027..13.030 rows=12 loops=1)
-> Sort (cost=69.64..70.14 rows=200 width=200) (actual time=13.024..13.025 rows=12 loops=1)
Sort Key: revenue.xtn_month
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=57.50..62.00 rows=200 width=200) (actual time=13.002..13.010 rows=12 loops=1)
-> CTE Scan on revenue (cost=0.00..20.00 rows=1000 width=200) (actual time=12.965..12.978 rows=12 loops=1)
-> CTE Scan on totals (cost=0.00..4.00 rows=200 width=136) (actual time=13.029..13.038 rows=12 loops=1)
-> Aggregate (cost=6.00..6.01 rows=1 width=128) (actual time=0.013..0.013 rows=1 loops=1)
-> CTE Scan on totals totals_1 (cost=0.00..4.00 rows=200 width=128) (actual time=0.001..0.002 rows=12 loops=1)
Total runtime: 13.520 ms
(43 rows)
Time: 26.224 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment