vertica joins weird behaviour

varchar(1024) is going to be inherently slower than an int. That's true in any database. But the more likely reason is that it's not sorted on that column, and it is sorted on flow_id. We'd need to know the projection definitions to know for sure. If you want to boost the performance of this, try adding new_item_value into the order by clause of the projection definition. Or just run the query through database designer, and see what it suggests.

Thanks for the reply
Ok, I'll try creating a projection but still, 100 seconds difference is very huge for such small tables. I'll update here again

I tried creating a projection which database designer suggested, now the query returns very fast.
Thank You for your help.

I was thinking about this, and I think I know what's happening. Run an EXPLAIN on both versions. In a columnar database, there is a cost for selecting columns. The cost of selecting an int column is far less than selecting a varchar(1024) column. What's probably happening here is that it may be reversing the inner/outer join between the two version. The column is coming from the same table, soit might not be, but it's possible. An explain would show you the difference between the two.

Yes, Vertica is reversing the inner/outer join between the two versions according to the EXPLAIN command. What is the difference between the two? Is there any option to hint the Vertica to use the same strategy(int) for the varchar version?

Syntactic join might do it.
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Hints/SyntacticJoin.htm