Release 1.0.109768 adds support for running multiple, concurrent, VACUUM commands.
I heard about this roughly six months ago, in a conf call with previous client and head of Redshift R&D. I couldn’t say anything as it was obviously disclosed with an expectation of confidentiality.
To my eye everything in Redshift for many years has been under the hood weak or very weak, so my expectation is for more of the same - and I would strongly advise you not to let your hopes and wishes fill in for facts about this functionality.
AWS as ever have published nothing useful in the way of facts - no details of implmentation or weaknesses, so your imagination has free reign.
Regarding implementation my guess is that it’s been bolted on the side, rather than being major changes to database kernel, which would mean something like VACUUM is actually running on data stored in S3, and that this data once sorted is being pushed down to the cluster - but that’s a blind guess, I’ve not looked yet.
A question here would be if this new VACUUM makes VACUUM slower, so although you can run more of them, they take longer.
One or two other thoughts : we’re still limited by WLM, so if we have say 15 slots, we’re not going to want more than say 5 VACUUMs running anyway.
Commits still will have their serial phase, one transaction only, which remains a bottleneck of some kind (although I recall it looked to me like VACUUM had its own commit queue in some way, so maybe there’s something special here).
I’m actually totally busy at the moment with client, which is unfortunate, as I need a non-concurrent VACUUM cluster to compare with. I’m going to try to get that investigation done while I can, but no promises.
Last night I had two or three hours and began to put together some test code.
I make N tables, and the run N processes (in Python test script),
each of which has its own connection to database, where each VACUUMs one
table. The processes are made ready, then all started at once (well, in
a loop which issues start), then I wait until all are
complete.
Cluster is 2xdc2.large, identical tables, exactly 10gb user data per slice, one column for distkey (int2), one for sorting (int8). Both columns encode raw, not null. Diststyle key on first column, sort on second column. Both fully vacuumed and analyzed, and then fully unsorted by update setting second column to equal itself (which does the job).
Results format is
region / node type / number nodes in cluster / process number / iteration : start / end / duration.
Critical weakness here is that timing ATM is taken in the Python script, not from system tables - until I actually look at system tables and see what’s going on, these results are who-knows-what, but better than nothing given I’m busy!
One process/table;
eu-central-1 / dc2.large / 2 / 0 / 0 : 2025-04-01 00:39:09.154433, 2025-04-01 00:40:10.485559, 0:01:01.331126
eu-central-1 / dc2.large / 2 / 0 / 1 : 2025-04-01 00:42:17.740001, 2025-04-01 00:43:18.979638, 0:01:01.239637
eu-central-1 / dc2.large / 2 / 0 / 2 : 2025-04-01 00:45:23.996200, 2025-04-01 00:46:25.179312, 0:01:01.183112
eu-central-1 / dc2.large / 2 / 0 / 3 : 2025-04-01 00:48:30.434793, 2025-04-01 00:49:31.448401, 0:01:01.013608
eu-central-1 / dc2.large / 2 / 0 / 4 : 2025-04-01 00:51:34.554437, 2025-04-01 00:52:35.259249, 0:01:00.704812Two processes/tables (same cluster);
eu-central-1 / dc2.large / 2 / 0 / 0 : 2025-04-01 00:16:08.816010, 2025-04-01 00:17:23.402303, 0:01:14.586293
eu-central-1 / dc2.large / 2 / 1 / 0 : 2025-04-01 00:16:08.816030, 2025-04-01 00:17:50.132471, 0:01:41.316441
eu-central-1 / dc2.large / 2 / 0 / 1 : 2025-04-01 00:22:16.627552, 2025-04-01 00:23:34.409495, 0:01:17.781943
eu-central-1 / dc2.large / 2 / 1 / 1 : 2025-04-01 00:22:16.609307, 2025-04-01 00:23:58.166632, 0:01:41.557325
eu-central-1 / dc2.large / 2 / 0 / 2 : 2025-04-01 00:28:13.582008, 2025-04-01 00:29:31.898736, 0:01:18.316728
eu-central-1 / dc2.large / 2 / 1 / 2 : 2025-04-01 00:28:13.583973, 2025-04-01 00:29:55.451212, 0:01:41.867239Three processes/tables (same cluster);
eu-central-1 / dc2.large / 2 / 2 / 0 : 2025-04-01 01:03:05.702546, 2025-04-01 01:05:57.488791, 0:02:51.786245
eu-central-1 / dc2.large / 2 / 1 / 0 : 2025-04-01 01:03:05.702399, 2025-04-01 01:06:13.151063, 0:03:07.448664
eu-central-1 / dc2.large / 2 / 0 / 0 : 2025-04-01 01:03:05.691261, 2025-04-01 01:06:13.181311, 0:03:07.490050
eu-central-1 / dc2.large / 2 / 0 / 1 : 2025-04-01 01:12:42.508357, 2025-04-01 01:15:49.735315, 0:03:07.226958
eu-central-1 / dc2.large / 2 / 2 / 1 : 2025-04-01 01:12:42.506527, 2025-04-01 01:15:49.809496, 0:03:07.302969
eu-central-1 / dc2.large / 2 / 1 / 1 : 2025-04-01 01:12:42.508424, 2025-04-01 01:15:49.809496, 0:03:07.301072The almost exactly identical run time for each VACUUM is remarkable. I would not expect this, and I think something is going on.
And now for one process on a 1.0.109616 (non-current VACUUM cluster);
us-east-1 / dc2.large / 2 / 0 / 0 : 2025-04-01 09:14:31.368754, 2025-04-01 09:15:30.817685, 0:00:59.448931
us-east-1 / dc2.large / 2 / 0 / 1 : 2025-04-01 09:17:36.926600, 2025-04-01 09:18:36.418519, 0:00:59.491919
us-east-1 / dc2.large / 2 / 0 / 2 : 2025-04-01 09:20:43.390479, 2025-04-01 09:21:42.484615, 0:00:59.094136So question regularly asked is whether PK/FK/U actually does anything useful.
Kane Morgan recently joined the RS Slack and pointed out a SO post which provided a good lead.
I’m now showinng some proofs for that post, which basically asserts that if you select distinct on a PK you get to skip the unique step.
I made a cluser, 2xdc2.large.
I’ve made a table, like so;
create table test_1
(
column_1 int8 not null encode raw primary key,
column_2 int8 not null encode raw
)
diststyle even
compound sortkey( column_1 );Then inserted the following;
insert into test_1( column_1, column_2 ) values (1,1),(2,2),(3,3),(4,4),(5,5),(5,5);
Note value 5 is duplicated once.
We now issue the following query, and get the step plan for it. What we expect is RS simply fetches every row, because it’s PK. There will be no unique step, and we’ll see the 5 value twice.
dev=# select column_1 from test_1;
column_1
----------
2
5
3
4
1
5
(6 rows)Now for the step plan;
dev=# select * from sf.queries_step_plan where qid = pg_last_query_id();
qid | strm | seg | stp | node | slice | t | ttype | mrows | erows | rrows | bytes | pkts | st | start_ts | end_ts | dur | notes
------+------+-----+-----+------+-------+---+-------------+-------+-------+-------+-------+------+----+----------------------------+----------------------------+----------+----------------------------
3042 | 0 | 0 | | -1 | -1 | l | compilation | | | | | | | 2025-04-01 18:46:38.481255 | 2025-04-01 18:46:38.603731 | 0.122476 | compile off-load
3042 | 0 | 1 | | -1 | -1 | l | compilation | | | | | | | 2025-04-01 18:46:38.603764 | 2025-04-01 18:46:38.603773 | 0.000009 | leader-node cache
3042 | 0 | 0 | 0 | 0 | 0 | f | scan | 1 | 1 | 1 | 16 | | | 2025-04-01 18:46:38.604683 | 2025-04-01 18:46:38.605502 | 0.000819 | user table public.test_1
3042 | 0 | 0 | 0 | 0 | 1 | f | scan | 1 | 1 | 1 | 16 | | | 2025-04-01 18:46:38.604778 | 2025-04-01 18:46:38.605436 | 0.000658 | user table public.test_1
3042 | 0 | 0 | 0 | 1 | 2 | f | scan | 2 | 2 | 2 | 32 | | | 2025-04-01 18:46:38.604624 | 2025-04-01 18:46:38.60543 | 0.000806 | user table public.test_1
3042 | 0 | 0 | 0 | 1 | 3 | f | scan | 2 | 2 | 2 | 32 | | | 2025-04-01 18:46:38.604689 | 2025-04-01 18:46:38.605557 | 0.000868 | user table public.test_1
3042 | 0 | 0 | 1 | 0 | 0 | f | project | | | 1 | | | | 2025-04-01 18:46:38.604683 | 2025-04-01 18:46:38.605502 | 0.000819 |
3042 | 0 | 0 | 1 | 0 | 1 | f | project | | | 1 | | | | 2025-04-01 18:46:38.604778 | 2025-04-01 18:46:38.605436 | 0.000658 |
3042 | 0 | 0 | 1 | 1 | 2 | f | project | | | 2 | | | | 2025-04-01 18:46:38.604624 | 2025-04-01 18:46:38.60543 | 0.000806 |
3042 | 0 | 0 | 1 | 1 | 3 | f | project | | | 2 | | | | 2025-04-01 18:46:38.604689 | 2025-04-01 18:46:38.605557 | 0.000868 |
3042 | 0 | 0 | 2 | 0 | 0 | f | project | | | 1 | | | | 2025-04-01 18:46:38.604683 | 2025-04-01 18:46:38.605502 | 0.000819 |
3042 | 0 | 0 | 2 | 0 | 1 | f | project | | | 1 | | | | 2025-04-01 18:46:38.604778 | 2025-04-01 18:46:38.605436 | 0.000658 |
3042 | 0 | 0 | 2 | 1 | 2 | f | project | | | 2 | | | | 2025-04-01 18:46:38.604624 | 2025-04-01 18:46:38.60543 | 0.000806 |
3042 | 0 | 0 | 2 | 1 | 3 | f | project | | | 2 | | | | 2025-04-01 18:46:38.604689 | 2025-04-01 18:46:38.605557 | 0.000868 |
3042 | 0 | 0 | 3 | 0 | 0 | f | return | | | 1 | 8 | 1 | | 2025-04-01 18:46:38.604683 | 2025-04-01 18:46:38.605502 | 0.000819 |
3042 | 0 | 0 | 3 | 0 | 1 | f | return | | | 1 | 8 | 1 | | 2025-04-01 18:46:38.604778 | 2025-04-01 18:46:38.605436 | 0.000658 |
3042 | 0 | 0 | 3 | 1 | 2 | f | return | | | 2 | 16 | 1 | | 2025-04-01 18:46:38.604624 | 2025-04-01 18:46:38.60543 | 0.000806 |
3042 | 0 | 0 | 3 | 1 | 3 | f | return | | | 2 | 16 | 1 | | 2025-04-01 18:46:38.604689 | 2025-04-01 18:46:38.605557 | 0.000868 |
3042 | 0 | 1 | 0 | -1 | -1 | l | scan | 0 | 0 | 6 | 48 | | | 2025-04-01 18:46:38.604002 | 2025-04-01 18:46:38.606118 | 0.002116 | network to row-store table
3042 | 0 | 1 | 1 | -1 | -1 | l | return | | | 6 | 42 | 0 | | 2025-04-01 18:46:38.604002 | 2025-04-01 18:46:38.606118 | 0.002116 |
(20 rows)So we see every row was scanned (1/1/2/2 from the four nodes, the scan step) and the final return returns 6 rows.
Now let’s do the same, but on column_2.
dev=# select column_2 from test_1;
column_2
----------
2
5
1
5
4
3
(6 rows)Step plan below, and what we expect is all rows and read and returned
(it’s a normal column, no PK, and we’re not using
distinct).
dev=# select * from sf.queries_step_plan where qid = pg_last_query_id();
qid | strm | seg | stp | node | slice | t | ttype | mrows | erows | rrows | bytes | pkts | st | start_ts | end_ts | dur | notes
------+------+-----+-----+------+-------+---+-------------+-------+-------+-------+-------+------+----+----------------------------+----------------------------+----------+----------------------------
3083 | 0 | 0 | | -1 | -1 | l | compilation | | | | | | | 2025-04-01 18:50:41.169428 | 2025-04-01 18:50:41.169478 | 0.000050 | leader-node cache
3083 | 0 | 1 | | -1 | -1 | l | compilation | | | | | | | 2025-04-01 18:50:41.169718 | 2025-04-01 18:50:41.169751 | 0.000033 | leader-node cache
3083 | 0 | 0 | 0 | 0 | 0 | f | scan | 0 | 0 | 0 | 0 | | | 2025-04-01 18:50:41.170771 | 2025-04-01 18:50:41.171872 | 0.001101 | user table public.test_1
3083 | 0 | 0 | 0 | 0 | 1 | f | scan | 2 | 2 | 2 | 32 | | | 2025-04-01 18:50:41.170895 | 2025-04-01 18:50:41.171945 | 0.001050 | user table public.test_1
3083 | 0 | 0 | 0 | 1 | 2 | f | scan | 2 | 2 | 2 | 32 | | | 2025-04-01 18:50:41.170616 | 2025-04-01 18:50:41.171387 | 0.000771 | user table public.test_1
3083 | 0 | 0 | 0 | 1 | 3 | f | scan | 2 | 2 | 2 | 32 | | | 2025-04-01 18:50:41.170682 | 2025-04-01 18:50:41.171448 | 0.000766 | user table public.test_1
3083 | 0 | 0 | 1 | 0 | 0 | f | project | | | 0 | | | | 2025-04-01 18:50:41.170771 | 2025-04-01 18:50:41.171872 | 0.001101 |
3083 | 0 | 0 | 1 | 0 | 1 | f | project | | | 2 | | | | 2025-04-01 18:50:41.170895 | 2025-04-01 18:50:41.171945 | 0.001050 |
3083 | 0 | 0 | 1 | 1 | 2 | f | project | | | 2 | | | | 2025-04-01 18:50:41.170616 | 2025-04-01 18:50:41.171387 | 0.000771 |
3083 | 0 | 0 | 1 | 1 | 3 | f | project | | | 2 | | | | 2025-04-01 18:50:41.170682 | 2025-04-01 18:50:41.171448 | 0.000766 |
3083 | 0 | 0 | 2 | 0 | 0 | f | project | | | 0 | | | | 2025-04-01 18:50:41.170771 | 2025-04-01 18:50:41.171872 | 0.001101 |
3083 | 0 | 0 | 2 | 0 | 1 | f | project | | | 2 | | | | 2025-04-01 18:50:41.170895 | 2025-04-01 18:50:41.171945 | 0.001050 |
3083 | 0 | 0 | 2 | 1 | 2 | f | project | | | 2 | | | | 2025-04-01 18:50:41.170616 | 2025-04-01 18:50:41.171387 | 0.000771 |
3083 | 0 | 0 | 2 | 1 | 3 | f | project | | | 2 | | | | 2025-04-01 18:50:41.170682 | 2025-04-01 18:50:41.171448 | 0.000766 |
3083 | 0 | 0 | 3 | 0 | 0 | f | return | | | 0 | 0 | 0 | | 2025-04-01 18:50:41.170771 | 2025-04-01 18:50:41.171872 | 0.001101 |
3083 | 0 | 0 | 3 | 0 | 1 | f | return | | | 2 | 16 | 1 | | 2025-04-01 18:50:41.170895 | 2025-04-01 18:50:41.171945 | 0.001050 |
3083 | 0 | 0 | 3 | 1 | 2 | f | return | | | 2 | 16 | 1 | | 2025-04-01 18:50:41.170616 | 2025-04-01 18:50:41.171387 | 0.000771 |
3083 | 0 | 0 | 3 | 1 | 3 | f | return | | | 2 | 16 | 1 | | 2025-04-01 18:50:41.170682 | 2025-04-01 18:50:41.171448 | 0.000766 |
3083 | 0 | 1 | 0 | -1 | -1 | l | scan | 0 | 0 | 6 | 48 | | | 2025-04-01 18:50:41.169931 | 2025-04-01 18:50:41.172507 | 0.002576 | network to row-store table
3083 | 0 | 1 | 1 | -1 | -1 | l | return | | | 6 | 42 | 0 | | 2025-04-01 18:50:41.169931 | 2025-04-01 18:50:41.172507 | 0.002576 |
(20 rows)And we see again, and as expected, all 6 rows. Note we see here a different pattern of reading, 2/2/2 rather than 1/1/2/2. Long time ago it used to be only the slice owning a block could read that block, but it looks like for a long time all slices on a node can read all blocks on that node, and so here RS has one slice read the blocks for both slices on the first node. That kind of cross-over happens quite a lot, and sometimes it looks wrong to me, you see idle slices which on the face of it should be working.
Okay, now time to use distinct on
column_2.
dev=# select distinct column_2 from test_1;
column_2
----------
5
1
3
2
4
(5 rows)And the step plan now and rather shockingly does not show a
unique step, but a WINDOW FUNCTION!? TF?
So okay this is unexpected, and I’ll think about it later, but we
know what it’s for as the only difference is the
distinct.
dev=# select * from sf.queries_step_plan where qid = pg_last_query_id();
qid | strm | seg | stp | node | slice | t | ttype | mrows | erows | rrows | bytes | pkts | st | start_ts | end_ts | dur | notes
------+------+-----+-----+------+-------+---+-------------+-------+-------+-------+-------+------+----+----------------------------+----------------------------+----------+----------------------------
3122 | 0 | 0 | | -1 | -1 | l | compilation | | | | | | | 2025-04-01 18:54:08.8406 | 2025-04-01 18:54:08.964961 | 0.124361 | compile off-load
3122 | 0 | 1 | | -1 | -1 | l | compilation | | | | | | | 2025-04-01 18:54:08.964994 | 2025-04-01 18:54:08.966242 | 0.001248 | leader-node cache
3122 | 0 | 2 | | -1 | -1 | l | compilation | | | | | | | 2025-04-01 18:54:08.840525 | 2025-04-01 18:54:08.840559 | 0.000034 | leader-node cache
3122 | 0 | 0 | 0 | 0 | 0 | f | scan | 0 | 0 | 0 | 0 | | | 2025-04-01 18:54:08.971537 | 2025-04-01 18:54:08.972492 | 0.000955 | user table public.test_1
3122 | 0 | 0 | 0 | 0 | 1 | f | scan | 2 | 2 | 2 | 32 | | | 2025-04-01 18:54:08.971518 | 2025-04-01 18:54:08.972557 | 0.001039 | user table public.test_1
3122 | 0 | 0 | 0 | 1 | 2 | f | scan | 4 | 4 | 4 | 64 | | | 2025-04-01 18:54:08.969278 | 2025-04-01 18:54:08.970655 | 0.001377 | user table public.test_1
3122 | 0 | 0 | 0 | 1 | 3 | f | scan | 0 | 0 | 0 | 0 | | | 2025-04-01 18:54:08.969917 | 2025-04-01 18:54:08.970655 | 0.000738 | user table public.test_1
3122 | 0 | 0 | 1 | 0 | 0 | f | project | | | 0 | | | | 2025-04-01 18:54:08.971537 | 2025-04-01 18:54:08.972492 | 0.000955 |
3122 | 0 | 0 | 1 | 0 | 1 | f | project | | | 2 | | | | 2025-04-01 18:54:08.971518 | 2025-04-01 18:54:08.972557 | 0.001039 |
3122 | 0 | 0 | 1 | 1 | 2 | f | project | | | 4 | | | | 2025-04-01 18:54:08.969278 | 2025-04-01 18:54:08.970655 | 0.001377 |
3122 | 0 | 0 | 1 | 1 | 3 | f | project | | | 0 | | | | 2025-04-01 18:54:08.969917 | 2025-04-01 18:54:08.970655 | 0.000738 |
3122 | 0 | 0 | 2 | 0 | 0 | f | window | | | 0 | 0 | | m | 2025-04-01 18:54:08.971537 | 2025-04-01 18:54:08.972492 | 0.000955 | grouped, unsorted
3122 | 0 | 0 | 2 | 0 | 1 | f | window | | | 2 | 32 | | m | 2025-04-01 18:54:08.971518 | 2025-04-01 18:54:08.972557 | 0.001039 | grouped, unsorted
3122 | 0 | 0 | 2 | 1 | 2 | f | window | | | 3 | 48 | | m | 2025-04-01 18:54:08.969278 | 2025-04-01 18:54:08.970655 | 0.001377 | grouped, unsorted
3122 | 0 | 0 | 2 | 1 | 3 | f | window | | | 0 | 0 | | m | 2025-04-01 18:54:08.969917 | 2025-04-01 18:54:08.970655 | 0.000738 | grouped, unsorted
3122 | 0 | 0 | 3 | 0 | 0 | f | distribute | | | 0 | 0 | 0 | | 2025-04-01 18:54:08.971537 | 2025-04-01 18:54:08.972492 | 0.000955 |
3122 | 0 | 0 | 3 | 0 | 1 | f | distribute | | | 2 | 16 | 2 | | 2025-04-01 18:54:08.971518 | 2025-04-01 18:54:08.972557 | 0.001039 |
3122 | 0 | 0 | 3 | 1 | 2 | f | distribute | | | 3 | 24 | 3 | | 2025-04-01 18:54:08.969278 | 2025-04-01 18:54:08.970655 | 0.001377 |
3122 | 0 | 0 | 3 | 1 | 3 | f | distribute | | | 0 | 0 | 0 | | 2025-04-01 18:54:08.969917 | 2025-04-01 18:54:08.970655 | 0.000738 |
3122 | 0 | 1 | 0 | 0 | 0 | f | scan | 0 | 0 | 1 | 8 | | | 2025-04-01 18:54:08.967749 | 2025-04-01 18:54:08.973366 | 0.005617 | network to row-store table
3122 | 0 | 1 | 0 | 0 | 1 | f | scan | 0 | 0 | 1 | 8 | | | 2025-04-01 18:54:08.96796 | 2025-04-01 18:54:08.974081 | 0.006121 | network to row-store table
3122 | 0 | 1 | 0 | 1 | 2 | f | scan | 0 | 0 | 2 | 16 | | | 2025-04-01 18:54:08.96713 | 2025-04-01 18:54:08.974455 | 0.007325 | network to row-store table
3122 | 0 | 1 | 0 | 1 | 3 | f | scan | 0 | 0 | 1 | 8 | | | 2025-04-01 18:54:08.967313 | 2025-04-01 18:54:08.974484 | 0.007171 | network to row-store table
3122 | 0 | 1 | 1 | 0 | 0 | f | window | | | 1 | 16 | | m | 2025-04-01 18:54:08.967749 | 2025-04-01 18:54:08.973366 | 0.005617 | grouped, unsorted
3122 | 0 | 1 | 1 | 0 | 1 | f | window | | | 1 | 16 | | m | 2025-04-01 18:54:08.96796 | 2025-04-01 18:54:08.974081 | 0.006121 | grouped, unsorted
3122 | 0 | 1 | 1 | 1 | 2 | f | window | | | 2 | 32 | | m | 2025-04-01 18:54:08.96713 | 2025-04-01 18:54:08.974455 | 0.007325 | grouped, unsorted
3122 | 0 | 1 | 1 | 1 | 3 | f | window | | | 1 | 16 | | m | 2025-04-01 18:54:08.967313 | 2025-04-01 18:54:08.974484 | 0.007171 | grouped, unsorted
3122 | 0 | 1 | 2 | 0 | 0 | f | project | | | 1 | | | | 2025-04-01 18:54:08.967749 | 2025-04-01 18:54:08.973366 | 0.005617 |
3122 | 0 | 1 | 2 | 0 | 1 | f | project | | | 1 | | | | 2025-04-01 18:54:08.96796 | 2025-04-01 18:54:08.974081 | 0.006121 |
3122 | 0 | 1 | 2 | 1 | 2 | f | project | | | 2 | | | | 2025-04-01 18:54:08.96713 | 2025-04-01 18:54:08.974455 | 0.007325 |
3122 | 0 | 1 | 2 | 1 | 3 | f | project | | | 1 | | | | 2025-04-01 18:54:08.967313 | 2025-04-01 18:54:08.974484 | 0.007171 |
3122 | 0 | 1 | 3 | 0 | 0 | f | return | | | 1 | 8 | 1 | | 2025-04-01 18:54:08.967749 | 2025-04-01 18:54:08.973366 | 0.005617 |
3122 | 0 | 1 | 3 | 0 | 1 | f | return | | | 1 | 8 | 1 | | 2025-04-01 18:54:08.96796 | 2025-04-01 18:54:08.974081 | 0.006121 |
3122 | 0 | 1 | 3 | 1 | 2 | f | return | | | 2 | 16 | 1 | | 2025-04-01 18:54:08.96713 | 2025-04-01 18:54:08.974455 | 0.007325 |
3122 | 0 | 1 | 3 | 1 | 3 | f | return | | | 1 | 8 | 1 | | 2025-04-01 18:54:08.967313 | 2025-04-01 18:54:08.974484 | 0.007171 |
3122 | 0 | 1 | 4 | 0 | 0 | f | merge | | | 0 | | | | 2025-04-01 18:54:08.967749 | 2025-04-01 18:54:08.973366 | 0.005617 |
3122 | 0 | 1 | 4 | 0 | 1 | f | merge | | | 0 | | | | 2025-04-01 18:54:08.96796 | 2025-04-01 18:54:08.974081 | 0.006121 |
3122 | 0 | 1 | 4 | 1 | 2 | f | merge | | | 0 | | | | 2025-04-01 18:54:08.96713 | 2025-04-01 18:54:08.974455 | 0.007325 |
3122 | 0 | 1 | 4 | 1 | 3 | f | merge | | | 0 | | | | 2025-04-01 18:54:08.967313 | 2025-04-01 18:54:08.974484 | 0.007171 |
3122 | 0 | 1 | 5 | 0 | 0 | f | window | | | 0 | 0 | | m | 2025-04-01 18:54:08.967749 | 2025-04-01 18:54:08.973366 | 0.005617 | grouped, sorted
3122 | 0 | 1 | 5 | 0 | 1 | f | window | | | 0 | 0 | | m | 2025-04-01 18:54:08.96796 | 2025-04-01 18:54:08.974081 | 0.006121 | grouped, sorted
3122 | 0 | 1 | 5 | 1 | 2 | f | window | | | 0 | 0 | | m | 2025-04-01 18:54:08.96713 | 2025-04-01 18:54:08.974455 | 0.007325 | grouped, sorted
3122 | 0 | 1 | 5 | 1 | 3 | f | window | | | 0 | 0 | | m | 2025-04-01 18:54:08.967313 | 2025-04-01 18:54:08.974484 | 0.007171 | grouped, sorted
3122 | 0 | 1 | 6 | 0 | 0 | f | project | | | 0 | | | | 2025-04-01 18:54:08.967749 | 2025-04-01 18:54:08.973366 | 0.005617 |
3122 | 0 | 1 | 6 | 0 | 1 | f | project | | | 0 | | | | 2025-04-01 18:54:08.96796 | 2025-04-01 18:54:08.974081 | 0.006121 |
3122 | 0 | 1 | 6 | 1 | 2 | f | project | | | 0 | | | | 2025-04-01 18:54:08.96713 | 2025-04-01 18:54:08.974455 | 0.007325 |
3122 | 0 | 1 | 6 | 1 | 3 | f | project | | | 0 | | | | 2025-04-01 18:54:08.967313 | 2025-04-01 18:54:08.974484 | 0.007171 |
3122 | 0 | 2 | 0 | -1 | -1 | l | scan | 0 | 0 | 5 | 40 | | | 2025-04-01 18:54:08.968239 | 2025-04-01 18:54:08.975428 | 0.007189 | network to row-store table
3122 | 0 | 2 | 1 | -1 | -1 | l | return | | | 5 | 35 | 0 | | 2025-04-01 18:54:08.968239 | 2025-04-01 18:54:08.975428 | 0.007189 |
(49 rows)Now let’s do the same but for column_1, which as it is
PK, we expect not to bother with the window function.
dev=# select distinct column_1 from test_1;
column_1
----------
2
5
1
5
4
3
(6 rows)Step plan indeed as expected shows no window function. The PK IS changing behaviour, in a very direct and clear manner.
We can expect this behaviour also for unique.
dev=# select * from sf.queries_step_plan where qid = pg_last_query_id();
qid | strm | seg | stp | node | slice | t | ttype | mrows | erows | rrows | bytes | pkts | st | start_ts | end_ts | dur | notes
------+------+-----+-----+------+-------+---+-------------+-------+-------+-------+-------+------+----+----------------------------+----------------------------+----------+----------------------------
3159 | 0 | 0 | | -1 | -1 | l | compilation | | | | | | | 2025-04-01 18:57:17.500804 | 2025-04-01 18:57:17.500838 | 0.000034 | leader-node cache
3159 | 0 | 1 | | -1 | -1 | l | compilation | | | | | | | 2025-04-01 18:57:17.501001 | 2025-04-01 18:57:17.50103 | 0.000029 | leader-node cache
3159 | 0 | 0 | 0 | 0 | 0 | f | scan | 1 | 1 | 1 | 16 | | | 2025-04-01 18:57:17.50214 | 2025-04-01 18:57:17.503022 | 0.000882 | user table public.test_1
3159 | 0 | 0 | 0 | 0 | 1 | f | scan | 1 | 1 | 1 | 16 | | | 2025-04-01 18:57:17.502235 | 2025-04-01 18:57:17.503066 | 0.000831 | user table public.test_1
3159 | 0 | 0 | 0 | 1 | 2 | f | scan | 0 | 0 | 0 | 0 | | | 2025-04-01 18:57:17.501862 | 2025-04-01 18:57:17.502515 | 0.000653 | user table public.test_1
3159 | 0 | 0 | 0 | 1 | 3 | f | scan | 4 | 4 | 4 | 64 | | | 2025-04-01 18:57:17.501939 | 2025-04-01 18:57:17.50258 | 0.000641 | user table public.test_1
3159 | 0 | 0 | 1 | 0 | 0 | f | project | | | 1 | | | | 2025-04-01 18:57:17.50214 | 2025-04-01 18:57:17.503022 | 0.000882 |
3159 | 0 | 0 | 1 | 0 | 1 | f | project | | | 1 | | | | 2025-04-01 18:57:17.502235 | 2025-04-01 18:57:17.503066 | 0.000831 |
3159 | 0 | 0 | 1 | 1 | 2 | f | project | | | 0 | | | | 2025-04-01 18:57:17.501862 | 2025-04-01 18:57:17.502515 | 0.000653 |
3159 | 0 | 0 | 1 | 1 | 3 | f | project | | | 4 | | | | 2025-04-01 18:57:17.501939 | 2025-04-01 18:57:17.50258 | 0.000641 |
3159 | 0 | 0 | 2 | 0 | 0 | f | project | | | 1 | | | | 2025-04-01 18:57:17.50214 | 2025-04-01 18:57:17.503022 | 0.000882 |
3159 | 0 | 0 | 2 | 0 | 1 | f | project | | | 1 | | | | 2025-04-01 18:57:17.502235 | 2025-04-01 18:57:17.503066 | 0.000831 |
3159 | 0 | 0 | 2 | 1 | 2 | f | project | | | 0 | | | | 2025-04-01 18:57:17.501862 | 2025-04-01 18:57:17.502515 | 0.000653 |
3159 | 0 | 0 | 2 | 1 | 3 | f | project | | | 4 | | | | 2025-04-01 18:57:17.501939 | 2025-04-01 18:57:17.50258 | 0.000641 |
3159 | 0 | 0 | 3 | 0 | 0 | f | return | | | 1 | 8 | 1 | | 2025-04-01 18:57:17.50214 | 2025-04-01 18:57:17.503022 | 0.000882 |
3159 | 0 | 0 | 3 | 0 | 1 | f | return | | | 1 | 8 | 1 | | 2025-04-01 18:57:17.502235 | 2025-04-01 18:57:17.503066 | 0.000831 |
3159 | 0 | 0 | 3 | 1 | 2 | f | return | | | 0 | 0 | 0 | | 2025-04-01 18:57:17.501862 | 2025-04-01 18:57:17.502515 | 0.000653 |
3159 | 0 | 0 | 3 | 1 | 3 | f | return | | | 4 | 32 | 1 | | 2025-04-01 18:57:17.501939 | 2025-04-01 18:57:17.50258 | 0.000641 |
3159 | 0 | 1 | 0 | -1 | -1 | l | scan | 0 | 0 | 6 | 48 | | | 2025-04-01 18:57:17.501261 | 2025-04-01 18:57:17.503627 | 0.002366 | network to row-store table
3159 | 0 | 1 | 1 | -1 | -1 | l | return | | | 6 | 42 | 0 | | 2025-04-01 18:57:17.501261 | 2025-04-01 18:57:17.503627 | 0.002366 |
(20 rows)Home 3D Друк Blog Bring-Up Times Cross-Region Benchmarks Email Forums Mailing Lists Redshift Price Tracker Redshift Version Tracker Replacement System Tables Reserved Instances Marketplace Slack System Table Tracker The Known Universe White Papers