Totalling Unique Serial numbers every week

user_16529459 0
Viewed 70

I'm trying to write a query that will search my database and on a weekly basis finds the total unique serial numbers of devices. My current code is:

SELECT date_part('week', "timestamp") , count(DISTINCT serialno) 
FROM eddi_minute em 
GROUP BY date_part('week', "timestamp")

Unfortunately, the dataset I'm searching is huge (~600Gb) so its taking an incredibly long time to search. I want to be able to search once a week, every week for a short time i.e. for 1 minute a.k.a.

select count(distinct serialno) as Devices
        from eddi_minute em where "timestamp" >= '2021-06-23 00:01:00' and "timestamp" < '2021-06-23 00:02:00';

but for every week over a whole year so that I can press enter once and it does this for the whole database and to avoid counting unnecessarily.

In an ideal world, my idea would be to create a table of the times I want to search and then do a left join with that and my database to cut down on the data I'm searching but I only have read permissions to the server, so that is not an option. Is there an easy way I can do this?? Apologies if anything here is unclear, I'll elaborate if any of it is not properly explained.

The indexes for the table are

CREATE UNIQUE INDEX "PK_4c94f05e4de575488f4a0c2905d" ON ONLY public.eddi_minute USING btree (serialno, "timestamp")

The explain analyse result was:

GroupAggregate  (cost=41219561.55..90787854.96 rows=200 width=16) (actual time=7065790.406..8172419.446 rows=53 loops=1)
  Group Key: (date_part('week'::text, em."timestamp"))
  ->  Gather Merge  (cost=41219561.55..88747442.16 rows=408082059 width=16) (actual time=7052726.256..7834672.575 rows=408057194 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=41218561.53..41643646.99 rows=170034187 width=16) (actual time=6956066.331..7201252.404 rows=136019065 loops=3)
              Sort Key: (date_part('week'::text, em."timestamp"))
              Sort Method: external merge  Disk: 3368720kB
              Worker 0:  Sort Method: external merge  Disk: 3640792kB
              Worker 1:  Sort Method: external merge  Disk: 3371808kB
              ->  Parallel Append  (cost=0.00..9256242.79 rows=170034187 width=16) (actual time=0.435..2825202.379 rows=136019065 loops=3)
                    ->  Parallel Seq Scan on eddi_minute_p2021_05 em_11  (cost=0.00..1725776.58 rows=34898767 width=16) (actual time=0.011..1722528.987 rows=83740195 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2021_06 em_12  (cost=0.00..1488905.33 rows=30102507 width=16) (actual time=1.266..1488189.219 rows=72252984 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2021_04 em_10  (cost=0.00..1428581.36 rows=28905149 width=16) (actual time=149.934..1290294.249 rows=69366177 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2021_03 em_9  (cost=0.00..1290438.50 rows=26110040 width=16) (actual time=69.475..483281.530 rows=20887814 loops=3)
                    ->  Parallel Seq Scan on eddi_minute_p2021_02 em_8  (cost=0.00..922294.02 rows=18661202 width=16) (actual time=195.734..931653.840 rows=44786882 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2021_01 em_7  (cost=0.00..823415.96 rows=16660557 width=16) (actual time=102.708..834900.144 rows=39985282 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2020_12 em_6  (cost=0.00..293130.95 rows=5931036 width=16) (actual time=182.465..296634.818 rows=14234537 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2020_11 em_5  (cost=0.00..111271.35 rows=2251388 width=16) (actual time=195.367..110910.685 rows=5403366 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2020_10 em_4  (cost=0.00..105311.10 rows=2130808 width=16) (actual time=146.920..109340.586 rows=5113938 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2020_09 em_3  (cost=0.00..93692.39 rows=1895711 width=16) (actual time=87.456..94169.812 rows=4549714 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2020_08 em_2  (cost=0.00..86189.97 rows=1743918 width=16) (actual time=0.007..88029.891 rows=4185403 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2020_07 em_1  (cost=0.00..33400.45 rows=675796 width=16) (actual time=1.046..14190.279 rows=1621911 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2021_07 em_13  (cost=0.00..3438.66 rows=88773 width=16) (actual time=0.006..51.229 rows=150887 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_default em_26  (cost=0.00..45.20 rows=1456 width=16) (actual time=0.016..0.639 rows=2477 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2021_08 em_14  (cost=0.00..15.00 rows=400 width=16) (actual time=0.000..0.000 rows=0 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2021_09 em_15  (cost=0.00..15.00 rows=400 width=16) (actual time=0.000..0.515 rows=0 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2021_10 em_16  (cost=0.00..15.00 rows=400 width=16) (actual time=0.000..0.000 rows=0 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2021_11 em_17  (cost=0.00..15.00 rows=400 width=16) (actual time=0.000..0.000 rows=0 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2021_12 em_18  (cost=0.00..15.00 rows=400 width=16) (actual time=0.000..0.000 rows=0 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2022_01 em_19  (cost=0.00..15.00 rows=400 width=16) (actual time=0.000..0.000 rows=0 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2022_02 em_20  (cost=0.00..15.00 rows=400 width=16) (actual time=0.000..0.000 rows=0 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2022_03 em_21  (cost=0.00..15.00 rows=400 width=16) (actual time=0.000..0.001 rows=0 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2022_04 em_22  (cost=0.00..15.00 rows=400 width=16) (actual time=0.000..0.000 rows=0 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2022_05 em_23  (cost=0.00..15.00 rows=400 width=16) (actual time=0.000..0.000 rows=0 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2022_06 em_24  (cost=0.00..15.00 rows=400 width=16) (actual time=0.000..0.000 rows=0 loops=1)
                    ->  Parallel Seq Scan on eddi_minute_p2022_07 em_25  (cost=0.00..15.00 rows=400 width=16) (actual time=0.002..0.003 rows=0 loops=1)
Planning Time: 35.809 ms
Execution Time: 8172556.078 ms
postgresql sql
1 Answers

A few thoughts:

Although "timestamp" is valid column name, it is considered bad practice to use reserved names for objects. It might seem harmless but can get pretty annoying on the long run.

I believe an index in the column "timestamp" should significantly improve the performance of the second query:

CREATE INDEX idx_timestamp ON eddi_minute ("timestamp");

Regarding the first query: considering you have a 600GB (!) table, it might be interesting to create a partial index in the column "timestamp", so that the timestamps are indexed by the value you will use in your queries, e.g., week:

CREATE INDEX idx_timestamp_week ON eddi_minute (date_part('week', "timestamp"));

Note: although indexes speed up queries, they slow down other operations, like inserts, updates and deletes. If you create new indexes, test the performance of all relevant operations.

Demo: db<>fiddle

user_22753880
Related