6 added 1088 characters in body
source | link
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.
SELECT id as unit_id, community_id, bedrooms, bathrooms, date_trunc('month', mon.dt)::date AS period
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
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.
5 added 693 characters in body
source | link

EDIT: Here is my final query:

SELECT id as unit_id, community_id, bedrooms, bathrooms, date_trunc('month', mon.dt)::date AS period
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: Here is my final query:

SELECT id as unit_id, community_id, bedrooms, bathrooms, date_trunc('month', mon.dt)::date AS period
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
    Tweeted twitter.com/StackDBAs/status/1099187197343866880
4 tags, format
source | link

I have three tables: unitsunits (apartment units) leases, leases, and line itemsline_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 theeach unit and thewith its most recent rent for that unitto it.

                                                  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.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).:

The analyzeanalyze looks like this:

Based on what the analyzeanalyze 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.

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, list the unit and the most recent rent for that unit.

                                                  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.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.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).

The analyze looks like this:

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.

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):

The analyze looks like this:

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.

3 added 5216 characters in body
source | link
2 edited tags
| link
1
source | link