james727

redshift=> SELECT DISTINCT column1 from test_table;
 column1 
---------
       1
       3
       1
       2
(4 rows)

james727

redshift=> EXPLAIN SELECT DISTINCT column1 FROM test_table;
                        QUERY PLAN                        
----------------------------------------------------------
 XN Seq Scan on test_table  (cost=0.00..0.04 rows=4 width=4)

james727

redshift=> ALTER TABLE test_table ADD PRIMARY KEY(column1);
ALTER TABLE

james727

redshift=> SELECT DISTINCT column1 from test_table;
 column1 
---------
       2
       3
       1
(3 rows)

james727

redshift=> EXPLAIN SELECT DISTINCT column1 from test_table;
                           QUERY PLAN                           
----------------------------------------------------------------
 XN Unique  (cost=0.00..0.05 rows=3 width=4)
   ->  XN Seq Scan on test_table  (cost=0.00..0.04 rows=4 width=4)

james727

redshift=> CREATE TABLE test_table (column1 integer not null);
CREATE TABLE
redshift=> INSERT INTO test_table VALUES (1), (2), (3), (1);
INSERT 0 4

james727

UPDATE all_events 
  SET user_id = identify_staging.new_user_id
  FROM identify_staging 
  WHERE all_events.user_id = identify_staging.user_id
    AND time > $(MIN_EVENT_TIME);

james727

redshift=> EXPLAIN UPDATE all_events SET user_id = test_table.user_id FROM test_table WHERE all_events.event_id = test_table.event_id and all_events.user_id = test_table.user_id; 
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 XN Hash Join DS_DIST_NONE  (cost=15.00..355532837.33 rows=855 width=71)
   Hash Cond: (("outer".event_id = "inner".event_id) AND ("outer".user_id = "inner".user_id))
   ->  XN Seq Scan on all_events  (cost=0.00..1764430.88 rows=176443088 width=71)
   ->  XN Hash  (cost=10.00..10.00 rows=1000 width=16)
         ->  XN Seq Scan on test_table  (cost=0.00..10.00 rows=1000 width=16)

james727

redshift=> EXPLAIN UPDATE all_events SET user_id = test_table.user_id FROM test_table WHERE all_events.event_id = test_table.event_id;
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 XN Hash Join DS_BCAST_INNER  (cost=12.50..418648749.25 rows=11015 width=71)
   Hash Cond: ("outer".event_id = "inner".event_id)
   ->  XN Seq Scan on all_events  (cost=0.00..1764430.88 rows=176443088 width=63)
   ->  XN Hash  (cost=10.00..10.00 rows=1000 width=16)
         ->  XN Seq Scan on test_table  (cost=0.00..10.00 rows=1000 width=16)

james727

redshift=> CREATE TABLE test_table (LIKE all_events);
CREATE TABLE
redshift=> INSERT INTO test_table (SELECT * FROM all_events LIMIT 1000);
INSERT 0 1000