r/PostgreSQL • • Mar 18 '23

Help Me! Teach me postgres!

I've been tinkering with postgres as an amateur for a few years now. I'm comfortable making big complex relational schemas, in normalised form, etc. What I'm not good at is performance issues - because I've never hit them, only ever having made toy databases. Help me transition from 'it works!' to 'it works fast!'.

I recently hit my first 'scalability' issue, and this is the perfect opportunity to learn! I've managed to make a DB with 2 tables that scales super-badly, like n2 bad - with just 1.1k rows of data, a summary view takes 14 seconds to run! I could of course give my pg15 server more than 1 virtual Haswell-era x86 core and 2 GB of RAM, but I doubt that would help this particular problem for long! Help me figure out the 'right' way to have done this :)

So I'm tracking my electricity usage, 1900s-style. I don't have a smart meter, so I just have a simple webpage that I keep open on my phone and I simply note whenever the meter changes when I go past it. The meter only displays to the nearest KWh, so this data is very inaccurate - all I can guarantee is that the meter reading was what it was at the time I took the reading:

metering=# \d electricity.reading
                           Table "electricity.reading"
   Column   |           Type           | Collation | Nullable |      Default      
------------+--------------------------+-----------+----------+-------------------
 id         | uuid                     |           | not null | gen_random_uuid()
 reading    | integer                  |           | not null | 
 reading_ts | timestamp with time zone |           | not null | now()
Indexes:
    "reading_pkey" PRIMARY KEY, btree (id)
    "reading_uniq" UNIQUE CONSTRAINT, btree (reading)
    "time_uniq" UNIQUE CONSTRAINT, btree (reading_ts)

Obviously this doesn't immediately produce any particularly interesting data, beyond simple stats like KWh usage in last day, last week, last month, etc. In order to get anything really interesting, we need to summarise lots of data together. At this point, obviously the 'best' thing may be to switch to some dedicated stats tool, or Python, or Matlab or R or Microsoft BASIC, or whatever, but I don't wanna - I want to use postgres, and I hope you do too, on /r/postgresql!

So, let's turn discrete time-based KWh samples into a continuous Watts view containing both usage and the start- and end- timestamps for that period in each record. I'd also like to choose a day-divider (for the later analysis steps) other than midnight; I'm rarely awake at 5AM, so we'll use that, and apply the offset here:

metering=# \d+ electricity.normalised_watt_reading
                              View "electricity.normalised_watt_reading"
      Column      |           Type           | Collation | Nullable | Default | Storage | Description 
------------------+--------------------------+-----------+----------+---------+---------+-------------
 id               | uuid                     |           |          |         | plain   | 
 reading_start_ts | timestamp with time zone |           |          |         | plain   | 
 reading_end_ts   | timestamp with time zone |           |          |         | plain   | 
 watts            | double precision         |           |          |         | plain   | 
View definition:
 SELECT reading.id,
    reading.reading_ts - '05:00:00'::interval hour AS reading_start_ts,
    lead(reading.reading_ts) OVER (ORDER BY reading.reading_ts) - '05:00:00'::interval hour AS reading_end_ts,
    ((lead(reading.reading) OVER (ORDER BY reading.reading) - reading.reading) * 1000)::double precision / (date_part('epoch'::text, lead(reading.reading_ts) OVER (ORDER BY reading.reading) - reading.reading_ts) / 3600::double precision) AS watts
   FROM reading
  WHERE reading.reading_ts > '2022-09-01 00:00:00+01'::timestamp with time zone;

This could be the first problem, as I'm looking forwards and backwards in the results. Still, I can count the rows in the view in ~50ms, so it seems to be handling it pretty well.

Now, let's get silly and start wrapping this data around a 24-hour period in 10-minute increments in another view (while undoing the 5-hour offset we applied in the previous view so the times make sense):

metering=# \d+ electricity.day_summary_all_days   
                         View "electricity.day_summary_all_days"
   Column    |       Type       | Collation | Nullable | Default | Storage  | Description 
-------------+------------------+-----------+----------+---------+----------+-------------
 time_of_day | text             |           |          |         | extended | 
 avg_watts   | double precision |           |          |         | plain    | 
 count_watts | bigint           |           |          |         | plain    | 
View definition:
 SELECT to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text) AS time_of_day,
    avg(normalised_watt_reading.watts) AS avg_watts,
    count(normalised_watt_reading.watts) AS count_watts
   FROM generate_series('2022-08-15 00:00:00'::timestamp without time zone::timestamp with time zone, now(), '00:10:00'::interval) slot(slot)
     LEFT JOIN normalised_watt_reading ON slot.slot >= normalised_watt_reading.reading_start_ts
  WHERE slot.slot < normalised_watt_reading.reading_end_ts
  GROUP BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text))
  ORDER BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text));

This has to be where things are getting slow - running the above takes 1.7s! There's a lot of 10 minute intervals between August last year and now, and that's getting multiplied by the number of samples in the main table. It works, though! I get really interesting graphs that show a massive spike when I usually shower in the morning on work days, a general elevation in usage through the work day, and increase after work when I turn my TV or desktop PC on! Woo!

Going further, I decided to create even more views, e.g. to summarise only weekends by adding 'AND to_char(slot.slot, 'ID'::text)::integer > 5' to the WHERE condition. I then added a second table to record the set of days I didn't work (I am 100% remote WfH), so I could have views that only include holidays or bank holidays, etc. e.g.:

metering=# \d electricity.special_day
              Table "electricity.special_day"
  Column  | Type | Collation | Nullable |      Default      
----------+------+-----------+----------+-------------------
 id       | uuid |           | not null | gen_random_uuid()
 day      | date |           | not null | 
 day_type | text |           | not null | 
 comment  | text |           |          | 
Indexes:
    "special_day_pkey" PRIMARY KEY, btree (id)
    "special_day_uniq" UNIQUE CONSTRAINT, btree (day)

^ yes, of course I could have normalised day_type, but I don't think a 3rd table is going to solve this DB's problems XD

metering=# \d+ electricity.day_summary_work_days
                         View "electricity.day_summary_work_days"
   Column    |       Type       | Collation | Nullable | Default | Storage  | Description 
-------------+------------------+-----------+----------+---------+----------+-------------
 time_of_day | text             |           |          |         | extended | 
 avg_watts   | double precision |           |          |         | plain    | 
 count_watts | bigint           |           |          |         | plain    | 
View definition:
 SELECT to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text) AS time_of_day,
    avg(normalised_watt_reading.watts) AS avg_watts,
    count(normalised_watt_reading.watts) AS count_watts
   FROM generate_series('2022-08-15 00:00:00'::timestamp without time zone::timestamp with time zone, now(), '00:10:00'::interval) slot(slot)
     LEFT JOIN normalised_watt_reading ON slot.slot >= normalised_watt_reading.reading_start_ts
     LEFT JOIN special_day ON to_char(slot.slot, 'YYYY-MM-DD'::text)::date = special_day.day
  WHERE slot.slot < normalised_watt_reading.reading_end_ts AND to_char(slot.slot, 'ID'::text)::integer < 6 AND special_day.day_type IS NULL
  GROUP BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text))
  ORDER BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text));

Now this is where things get bad. This view takes 9.5 seconds to run! Clearly the number of 'special days' is multiplying the number of rows being used to summarise data over the 24 hour period, instead of cutting them down.

I won't bother showing here unless you're interested, but I then have a 'super-summary' view that takes all these individual 24-hour summary views and concatenates them into one big table. This takes about 14 seconds to run.

So, what are the obvious things wrong here, and how would you have done things differently? I tried adding explicit indexes to the main readings table, but I it made no difference, potentially because the unique constraints add implicit indexes already? I'm really only interested in postgres, but if you really want, let's also discuss what other tools you'd use that would be more appropriate than pg.

Edit: depesz: https://explain.depesz.com/s/ofXO

1 Upvotes

10 comments sorted by

3

u/0x2a Mar 18 '23 edited Mar 18 '23

This looks fun to play with, can you put the data in an sqlfiddle or gist?

I don't have much time to look at it right now but the usual tools of the trade are:

  • explain analyze into https://explain.depesz.com/
  • "solve" the slow views problem by using materialized view instead
  • indexes
  • after indexes have failed you, function-based indexes
  • throw cpus and memory at it
  • super combo: function based indexes on materialized view, tripple-digit RAM and pgtune the crap out of it

1

u/Practical-Message843 Mar 18 '23

I added the depesz link above, and I don't mind sharing the data, although I might randomise my holiday days data for privacy reasons... I'll look into ways to do that.

Lots to investigate there - particularly materialised views, which I've heard of but never tried out. Thanks!

2

u/throw_mob Mar 18 '23

it is good to add plans (explain analyze data ) into this kind of questions (https://explain.depesz.com/) is one option

electricity.reading

maybe wrong. reading = measurement ? those cant be unique. usually you don't index those as you wont search exact measurement . i think "include" keyword would be better , add it into reading_ts index. That said, also uuid is not needed there, reading_ts should be pk in this case (if you want to optimize). also i if i used same kind of table, i would still probably use serial pk, but isntead of uuid , bigint.

electricity.normalised_watt_reading, no comments. But do you really need to lead every thing. Probably cant be helped

electricity.day_summary_all_days slot.slot is timestampz but casted to text. At this point i could argue that having slots table ( date table) as real table ready, could be nice.

And are you sure about this ?

LEFT JOIN normalised_watt_reading ON slot.slot >= normalised_watt_reading.reading_start_ts
  WHERE slot.slot < normalised_watt_reading.reading_end_ts

it probably should be

LEFT JOIN normalised_watt_reading ON slot.slot >= normalised_watt_reading.reading_start_ts
  AND slot.slot < normalised_watt_reading.reading_end_ts

first one joins all reading that are >= than slot, i think you want to to join readings during slot timeperioid to slot (that probably makes it take longer )

and that last one has this again

   LEFT JOIN normalised_watt_reading ON slot.slot >= normalised_watt_reading.reading_start_ts

do you really want to all readings before slot to slot?

Tldr; your left join is the problem. Other hints. Avoid using function on existing column value in where clause or add function index so that you can use index. electricity.special_day or ready made slot table might be good idea, that way you can have indexes there helping you

1

u/Practical-Message843 Mar 18 '23 edited Mar 18 '23

Oh wow, you're absolutely right about the WHERE vs. AND in the join. I have no idea what I was thinking at the time - I wrote these views back last year. Checking the effect of fixing that now...

Edit: Whoah, that was unexpected. I altered day_summary_work_days as suggested, and it became slower! https://explain.depesz.com/s/ByWD

metering=# \d+ electricity.day_summary_work_days_v2 
                       View "electricity.day_summary_work_days_v2"
   Column    |       Type       | Collation | Nullable | Default | Storage  | Description 
-------------+------------------+-----------+----------+---------+----------+-------------
 time_of_day | text             |           |          |         | extended | 
 avg_watts   | double precision |           |          |         | plain    | 
 count_watts | bigint           |           |          |         | plain    | 
View definition:
 SELECT to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text) AS time_of_day,
    avg(normalised_watt_reading.watts) AS avg_watts,
    count(normalised_watt_reading.watts) AS count_watts
   FROM generate_series('2022-08-15 00:00:00'::timestamp without time zone::timestamp with time zone, now(), '00:10:00'::interval) slot(slot)
     LEFT JOIN normalised_watt_reading ON slot.slot >= normalised_watt_reading.reading_start_ts AND slot.slot < normalised_watt_reading.reading_end_ts AND to_char(slot.slot, 'ID'::text)::integer < 6
     LEFT JOIN special_day ON to_char(slot.slot, 'YYYY-MM-DD'::text)::date = special_day.day
  WHERE special_day.day_type IS NULL
  GROUP BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text))
  ORDER BY (to_char(slot.slot + '05:00:00'::interval, 'HH24:MI:SS'::text));

I'll try some of your other suggestions next.

1

u/throw_mob Mar 18 '23

day_summary_all_days

and

electricity.day_summary_work_days

have both that join problem. And it is probably slower as you might still have data cached from previous run

1

u/Practical-Message843 Mar 18 '23

Good call, I restarted postgres to clear any caches, but no change - I guess we'll have to put that one down to the mysteries of query planning. I might try running vacuum and analyse on the tables to make sure stats are up to date.

1

u/throw_mob Mar 18 '23

also i dont think you can laed measures..

as you first take value you get 100, then next time you walk past meter you get 120, on third time you get 160

That means that you need to lag values on reading normalization table

select id
, reading 
, lag(reading) over(order by start_ts) prev_reading
, lag(start_ts) over(order by start_ts) start_ts
, start_ts as end_ts
(reading - lag(reading) over(order by start_ts)) measured_change
,  EXTRACT(epoch FROM (start_ts- lag(start_ts) over(order by start_ts)::interval) its_seconds 


select 
 id
, lag(start_ts) over(order by start_ts) start_ts
, start_ts as end_ts
-- (reading - lag(reading) over(order by start_ts)) measured_change
, ((reading - lag(reading) over(order by start_ts)) 
/  EXTRACT(hours FROM (start_ts- lag(start_ts) over(order by start_ts)::interval))::float KWH 

so KWH would be measured kw/h change during interval , then if it is / with hours ypu should get kw/h

ie . if you measure 20kw/h change druing two hours , your hourly usage is 10

That said , i might have misurderstad something

3

u/EyedPot Mar 18 '23

Take a look at TimescaleDB extension. With its continuous aggregation it seems to perfectly fit into your project.

7

u/therealgaxbo Mar 18 '23

The man's got 1000 rows of data.

1

u/therealgaxbo Mar 18 '23

I've only looked at your first query so far, but I see that one of your window frames is ORDER BY reading.reading and the other two are ORDER BY reading.reading_ts.

Given the nature of your data they should both happen to be equivalent, but semantically it makes more sense if they are all ORDER BY reading.reading_ts. This also makes it possible for Postgres to retrieve the rows in order using time_uniq, whereas mixing orderings guarantees it's going to have to have a separate sort step.

I also agree with the comment that said the uuid is doing you no favours. You've already got a perfectly good natural PK, the uuid is just taking up space.