4

I have three tables: units (apartment units), leases, and line_items (a better name would have been recurring rents). A unit has many leases and lease has many line items (associated with each rent change). My goal is, given a set of a months, to list each unit with its most recent rent to it.

 Table public.units Column | Type | Collation | Nullable | Default ----------------+-----------------------------+-----------+----------+----------------------------------- id | integer | | not null | nextval('units_id_seq'::regclass) number | character varying | | | bedrooms | integer | | | bathrooms | integer | | | square_footage | integer | | | community_id | integer | | | building_id | integer | | | created_at | timestamp without time zone | | not null | updated_at | timestamp without time zone | | not null | slug | character varying | | | status | integer | | not null | 0 note | text | | | half_bathrooms | integer | | | display_number | character varying | | | Indexes: units_pkey PRIMARY KEY, btree (id) index_units_on_building_id btree (building_id) index_units_on_community_id btree (community_id) index_units_on_slug btree (slug) Foreign-key constraints: fk_rails_5850136a38 FOREIGN KEY (building_id) REFERENCES buildings(id) fk_rails_b860cf198b FOREIGN KEY (community_id) REFERENCES communities(id) Referenced by: TABLE leases CONSTRAINT fk_rails_29210439a5 FOREIGN KEY (unit_id) REFERENCES units(id) TABLE market_rents CONSTRAINT fk_rails_7270410e47 FOREIGN KEY (unit_id) REFERENCES units(id) TABLE incomes_unit_budgets CONSTRAINT fk_rails_791330e8fe FOREIGN KEY (unit_id) REFERENCES units(id) Table public.leases Column | Type | Collation | Nullable | Default | Storage | Stats target | Description ---------------------+-----------------------------+-----------+----------+------------------------------------+----------+--------------+------------- id | integer | | not null | nextval('leases_id_seq'::regclass) | plain | | start | date | | | | plain | | unit_id | integer | | | | plain | | created_at | timestamp without time zone | | not null | | plain | | updated_at | timestamp without time zone | | not null | | plain | | vacate | date | | | | plain | | community_id | bigint | | not null | | plain | | Indexes: leases_pkey PRIMARY KEY, btree (id) index_leases_on_community_id btree (community_id) index_leases_on_unit_id btree (unit_id) Foreign-key constraints: fk_rails_29210439a5 FOREIGN KEY (unit_id) REFERENCES units(id) fk_rails_46ad3e9d34 FOREIGN KEY (community_id) REFERENCES communities(id) Referenced by: TABLE line_items CONSTRAINT fk_rails_02720bca5b FOREIGN KEY (lease_id) REFERENCES leases(id) TABLE terms CONSTRAINT fk_rails_49f65f38eb FOREIGN KEY (lease_id) REFERENCES leases(id) Table public.line_items Column | Type | Collation | Nullable | Default -----------------+-----------------------------+-----------+----------+---------------------------------------- id | integer | | not null | nextval('line_items_id_seq'::regclass) name | character varying | | | lease_id | integer | | | amount_cents | integer | | not null | 0 amount_currency | character varying | | not null | 'USD'::character varying created_at | timestamp without time zone | | not null | updated_at | timestamp without time zone | | not null | start | date | | | expiration | date | | | Indexes: line_items_pkey PRIMARY KEY, btree (id) index_line_items_on_lease_id btree (lease_id) index_line_items_on_start btree (start) Foreign-key constraints: fk_rails_02720bca5b FOREIGN KEY (lease_id) REFERENCES leases(id)

The following query does exactly what I would hope, but is extremely slow (8 seconds) on a relatively small data set (~1500 units, ~5000 leases, ~15000 line items):

with last_rent as ( SELECT line_items.* FROM (SELECT line_items.*, leases.unit_id, row_number() over ( partition by unit_id, date_trunc('month', line_items.start) order by line_items.start desc ) FROM line_items INNER JOIN leases ON leases.id = line_items.lease_id WHERE line_items.name = 'RNT' AND leases.community_id IN (X)) as line_items WHERE line_items.row_number = 1 ), month_series as ( SELECT id as unit_id, community_id, bedrooms, bathrooms, generate_series( '2019-03-01', '2019-03-01'::date, interval '1 month' ) - interval '1 day' dt FROM units WHERE units.community_id IN (X) ORDER BY units.id ASC ), unit_rent_month as ( select unit_id, community_id, bedrooms, bathrooms, amount_cents, date_trunc('month', dt)::date as period from month_series left join lateral ( select amount_cents from last_rent where last_rent.unit_id = month_series.unit_id and last_rent.start <= month_series.dt order by unit_id, last_rent.start desc limit 1 ) last_rent on true order by dt, unit_id ) select * from unit_rent_month;

My strategy was to isolate the last rent of each month (in case of early move outs or cancellations) by using a window function. I created another table (month_series) which lists all the unit_ids and the months. I tried doing a lateral join between last rent and month series.

The analyze looks like this:

CTE Scan on unit_rent_month (cost=2563655.29..2593175.29 rows=1476000 width=24) (actual time=8019.924..8020.607 rows=1476 loops=1) Output: unit_rent_month.unit_id, unit_rent_month.community_id, unit_rent_month.bedrooms, unit_rent_month.bathrooms, unit_rent_month.amount_cents, unit_rent_month.period Buffers: shared hit=990 CTE last_rent -> Subquery Scan on line_items (cost=1682.58..2163.78 rows=60 width=56) (actual time=44.584..63.921 rows=12010 loops=1) Output: line_items.id, line_items.name, line_items.lease_id, line_items.amount_cents, line_items.amount_currency, line_items.created_at, line_items.updated_at, line_items.start, line_items.expiration, line_items.unit_id, line_items.row_number Filter: (line_items.row_number = 1) Rows Removed by Filter: 63 Buffers: shared hit=269 -> WindowAgg (cost=1682.58..2013.40 rows=12030 width=68) (actual time=44.582..60.093 rows=12073 loops=1) Output: line_items_1.id, line_items_1.name, line_items_1.lease_id, line_items_1.amount_cents, line_items_1.amount_currency, line_items_1.created_at, line_items_1.updated_at, line_items_1.start, line_items_1.expiration, leases.unit_id, row_number() OVER (?), leases.unit_id, (date_trunc('month'::text, (line_items_1.start)::timestamp with time zone)) Buffers: shared hit=269 -> Sort (cost=1682.58..1712.65 rows=12030 width=56) (actual time=44.524..45.478 rows=12073 loops=1) Output: line_items_1.start, leases.unit_id, (date_trunc('month'::text, (line_items_1.start)::timestamp with time zone)), line_items_1.id, line_items_1.name, line_items_1.lease_id, line_items_1.amount_cents, line_items_1.amount_currency, line_items_1.created_at, line_items_1.updated_at, line_items_1.expiration Sort Key: leases.unit_id, (date_trunc('month'::text, (line_items_1.start)::timestamp with time zone)), line_items_1.start DESC Sort Method: quicksort Memory: 2082kB Buffers: shared hit=269 -> Hash Join (cost=378.43..867.28 rows=12030 width=56) (actual time=7.626..33.901 rows=12073 loops=1) Output: line_items_1.start, leases.unit_id, date_trunc('month'::text, (line_items_1.start)::timestamp with time zone), line_items_1.id, line_items_1.name, line_items_1.lease_id, line_items_1.amount_cents, line_items_1.amount_currency, line_items_1.created_at, line_items_1.updated_at, line_items_1.expiration Inner Unique: true Hash Cond: (line_items_1.lease_id = leases.id) Buffers: shared hit=269 -> Seq Scan on public.line_items line_items_1 (cost=0.00..395.25 rows=12736 width=44) (actual time=0.012..5.813 rows=12736 loops=1) Output: line_items_1.id, line_items_1.name, line_items_1.lease_id, line_items_1.amount_cents, line_items_1.amount_currency, line_items_1.created_at, line_items_1.updated_at, line_items_1.start, line_items_1.expiration Filter: ((line_items_1.name)::text = 'RNT'::text) Rows Removed by Filter: 4884 Buffers: shared hit=175 -> Hash (cost=292.99..292.99 rows=6835 width=8) (actual time=7.537..7.537 rows=6837 loops=1) Output: leases.unit_id, leases.id Buckets: 8192 Batches: 1 Memory Usage: 332kB Buffers: shared hit=94 -> Seq Scan on public.leases (cost=0.00..292.99 rows=6835 width=8) (actual time=0.010..4.954 rows=6837 loops=1) Output: leases.unit_id, leases.id Filter: (leases.community_id = ANY ('{X}'::bigint[])) Rows Removed by Filter: 401 Buffers: shared hit=94 CTE month_series -> Result (cost=0.28..33371.24 rows=1476000 width=24) (actual time=0.065..11.553 rows=1476 loops=1) Output: units.id, units.community_id, units.bedrooms, units.bathrooms, ((generate_series('2019-03-01 00:00:00-08'::timestamp with time zone, ('2019-03-01'::date)::timestamp with time zone, '1 mon'::interval)) - '1 day'::interval) Buffers: shared hit=712 -> ProjectSet (cost=0.28..7541.24 rows=1476000 width=24) (actual time=0.062..9.869 rows=1476 loops=1) Output: generate_series('2019-03-01 00:00:00-08'::timestamp with time zone, ('2019-03-01'::date)::timestamp with time zone, '1 mon'::interval), units.id, units.community_id, units.bedrooms, units.bathrooms Buffers: shared hit=712 -> Index Scan using units_pkey on public.units (cost=0.28..146.48 rows=1476 width=16) (actual time=0.038..4.365 rows=1476 loops=1) Output: units.id, units.number, units.bedrooms, units.bathrooms, units.square_footage, units.community_id, units.building_id, units.created_at, units.updated_at, units.slug, units.status, units.note, units.half_bathrooms, units.display_number Filter: (units.community_id = ANY ('{X}'::integer[])) Rows Removed by Filter: 201 Buffers: shared hit=712 CTE unit_rent_month -> Sort (cost=2524430.27..2528120.27 rows=1476000 width=32) (actual time=8019.918..8019.998 rows=1476 loops=1) Output: month_series.unit_id, month_series.community_id, month_series.bedrooms, month_series.bathrooms, last_rent.amount_cents, ((date_trunc('month'::text, month_series.dt))::date), month_series.dt Sort Key: month_series.dt, month_series.unit_id Sort Method: quicksort Memory: 164kB Buffers: shared hit=990 -> Nested Loop Left Join (cost=1.51..2302560.00 rows=1476000 width=32) (actual time=78.693..8018.611 rows=1476 loops=1) Output: month_series.unit_id, month_series.community_id, month_series.bedrooms, month_series.bathrooms, last_rent.amount_cents, (date_trunc('month'::text, month_series.dt))::date, month_series.dt Buffers: shared hit=984 -> CTE Scan on month_series (cost=0.00..29520.00 rows=1476000 width=24) (actual time=0.067..13.332 rows=1476 loops=1) Output: month_series.unit_id, month_series.community_id, month_series.bedrooms, month_series.bathrooms, month_series.dt Buffers: shared hit=712 -> Limit (cost=1.51..1.51 rows=1 width=12) (actual time=5.419..5.420 rows=1 loops=1476) Output: last_rent.amount_cents, last_rent.unit_id, last_rent.start Buffers: shared hit=272 -> Sort (cost=1.51..1.51 rows=1 width=12) (actual time=5.418..5.418 rows=1 loops=1476) Output: last_rent.amount_cents, last_rent.unit_id, last_rent.start Sort Key: last_rent.start DESC Sort Method: top-N heapsort Memory: 25kB Buffers: shared hit=272 -> CTE Scan on last_rent (cost=0.00..1.50 rows=1 width=12) (actual time=3.395..5.411 rows=8 loops=1476) Output: last_rent.amount_cents, last_rent.unit_id, last_rent.start Filter: ((last_rent.start <= month_series.dt) AND (last_rent.unit_id = month_series.unit_id)) Rows Removed by Filter: 12002 Buffers: shared hit=269 Planning time: 3.219 ms Execution time: 8021.388 ms

Based on what the analyze is saying, I believe the issue to be the subquery and the lateral join. Would anyone help me figure how to optimize this? Happy to share more information if needed.

EDIT: Here is my final query:

SELECT id as unit_id, community_id, bedrooms, bathrooms, date_trunc('month', mon.dt)::date AS period, last_rent.amount_cents FROM "units" CROSS  JOIN generate_series(timestamp '2018-01-01'                           , timestamp '2018-12-01'                           , interval '1 month') mon(dt)   left join lateral (     SELECT li.amount_cents     FROM   leases l     JOIN   line_items li ON l.id = li.lease_id     WHERE  l."unit_id" = "units"."id"       AND li.start < mon.dt + interval '1 month'       AND    li.name  = 'RNT'     order by li.start desc     limit 1   ) last_rent on true WHERE "units"."community_id" IN (X) order by period, unit_id

EDIT 2:

For reference, here's a test case with rows and desired output

Line Item 1: id: 1 name: "RNT" lease_id: 1 amount_cents: 100,000 start: January 1, 2018 Line Item 2: id: 2 name: "RNT" lease_id: 2 amount_cents: 110,000 start: March 5, 2018 Lease 1: id: 1 start: January 1, 2018 vacate: March 4, 2018 unit_id: 1 Lease 2: id: 2 start: March 5, 2018 vacate: null unit_id: 1 Unit 1: id: 1 community_id: 1 number: 101 bedrooms: 1 bathrooms: 1

Output:

| unit_id | community_id | bedrooms | bathrooms | period | amount_cents --------------------------------------------------------------------------- | 1 | 1 | 1 | 1 | Jan 1, 2018 | 100,0000 | 1 | 1 | 1 | 1 | Feb 1, 2018 | 100,0000 | 1 | 1 | 1 | 1 | Mar 1, 2018 | 110,0000 | 1 | 1 | 1 | 1 | Apr 1, 2018 | 110,0000 ... Repeating 110,000 for the rest of 2018.
Improve this question
2
  • Please consider instructions for performance questions: dba.stackexchange.com/tags/postgresql-performance/info And can you remove all those unnecessary double-quotes from identifiers. It's hard on the eyes. Commented Feb 22, 2019 at 20:00
  • Thanks, I'll review instructions and remove double quotes. Commented Feb 22, 2019 at 20:04

1 Answer 1

Reset to default
3

You have a lot of redundant work in your query. The first CTE last_rent retrieves the "first" row per month for every unit in the whole table and materializes the result, while only a fraction of that is reused in the later CTE unit_rent_month. Plus, you repeat the work there.

You can basically remove the first CTE last_rent completely. And while being at it, remove all CTEs. All you actually need is a single LATERAL subquery. This radically rewritten query should do the same, much faster (untested):

SELECT u.id AS unit_id, u.community_id, u.bedrooms, u.bathrooms , last_rent.amount_cents , date_trunc('month', mon.dt)::date AS period FROM units u CROSS JOIN generate_series(timestamp '2019-02-01' -- switched to lower bound , timestamp '2019-02-01' -- and see below , interval '1 month') mon(dt) LEFT JOIN LATERAL ( SELECT li.amount_cents FROM leases l JOIN line_items li ON l.id = li.lease_id WHERE l.unit_id = u.id AND l.community_id = u.community_id -- redundant? AND li.name = 'RNT' AND li.start < mon.dt + interval '1 month' AND li.start >= mon.dt -- also required, see below ORDER BY li.start DESC -- NULLS LAST ? LIMIT 1 ) last_rent ON true WHERE u.community_id IN (X) -- ? ORDER BY mon.dt, u.id;

Your example produces only a single month. The intention obviously is to optionally produce several consecutive months at once. I changed the generate_series() expression subtly - here is why:

I also switched to providing lower bounds like you did in your updated query. That's slightly more convenient.

The way you repeated the condition community_id IN (X) could result in different community_id matching between units and leases if the IN clause has more than one expression. I tightened that to match exactly with AND l.community_id = u.community_id. Adapt if that's not as desired. Seems like the column leases.community_id is redundant to begin with, then you can drop this predicate completely.

This predicate makes a logical difference:

 AND li.start >= mon.dt

You dropped it in your updated query, but maybe you should keep it to always get the latest amount_cents for the month of the result row - NULL if there is none. It's what your original query did.
If you drop it, you get the latest amount_cents in the given date range, possibly from a previous month. I.e. you can get a different result for a month if you change the lower bound of the date range.

Related:

Indexes

Depending on undisclosed details, you might get faster results, yet, with a different query style, or by adding one or more multicolumn or partial indexes. Shots in the dark, this might get you very fast index-only scans:

CREATE INDEX foo ON leases (unit_id, id); CREATE INDEX bar ON line_items (lease_id, start DESC, amount_cents) WHERE name = 'RNT';

Or, slightly better yet, in Postgres 11: 

CREATE INDEX bar ON line_items (lease_id, start DESC) INCLUDING (amount_cents) WHERE name = 'RNT';

WHERE name = 'RNT' typically only pays if 'RNT' is not too common.

Related (consider the preconditions mentioned there):

Improve this answer
10
  • 1
    Erwin, this works perfectly! My mind is blown and you've put my weak SQL skills on notice. Thank you so much for your help. If you have any books you would recommend to sharpen my skills, I would be obliged for the recommendation. I reduced the interval for generate series for the sake of this example. Commented Feb 22, 2019 at 21:14
  • @sunnyrjuneja: Your SQL skills aren't too bad. Most people wouldn't even manage to write such a complex query correctly (which you did). The best sources for me are the excellent manual and, well, educative posts here on dba.SE and on stackoverflow. And assorted blogs, PostgreSQL Weekly News and the Postgres mailing lists. I am sure there are good books to get started out there, but I started a long time ago, not up to date in this section. Commented Feb 22, 2019 at 21:26
  • 1
    sorry for the delay on this but 15 ms :) Commented Feb 25, 2019 at 19:27
  • 1
    @sunnyrjuneja: I added some more yet, concerning the predicates you dropped in your "final" version and indexes. Commented Feb 27, 2019 at 0:52
  • 1
    I wanted to address some points before I gave an update on the indexes. line_items.start and line_items.amount_cents are both non nullable. This is currently enforced on the application layer but I should update the schema to reflect that. You're right to point out that lease.community_id is redundant. I added that to simplify and improve performance of other queries. I need to investigate the predicate and will also update regarding that. Commented Feb 27, 2019 at 20:50

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.