I was wondering if anyone else has done any analysis of the performance of the NOT EXISTS vs. NOT IN predicates in Vertica queries? In Oracle there was/is always a great debate on when to use which. I'm wondering if because Vertica is using columnar storage and lacks indexes there would be no significant differences between the two.
Both methods are wicked fast in my database:
Code: Select all
dbadmin=> select count(*) from sales_01 where sales_date_key not in (select post_date_key from sales_temp);
count
-----------
137927565
(1 row)
Time: First fetch (1 row): 625.314 ms. All rows formatted: 625.352 ms
dbadmin=> select count(*) from sales_01 where not exists (select 1 from sales_temp where post_date_key = sales_date_key);
count
-----------
137927565
(1 row)
Thanks!