Redshift bad short query performance

In my experience, Redshift is not designed or tuned for low latency OLTP like performance. It is a cluster that cross compiles queries into C++ programs, compiles and link them into machine code not P-code like almost every OLTP query engine, distributes them to the cluster nodes, caches the plan and programs for later reuse, and lastly queues them for execution at pretty modest concurrence to support huge resources for executing big complex OLAP and big data queries. So yes, turning that big crank, especially for the first the first time execution of small simple queries, is not its sweet spot. If your SQL workload is dominated by such small queries, especially if they are quite varyable, i.e. not executed many times, then you will see a lot of high latency which is inherent in Redshift's design. Such a workload might be better suited on Aurora or RDS. However, feed it some huge analytic queries on terabytes or more of data and you'll see it shine and produces results that a non-clustered OLTP row store can never do.

Bottom line here is Redshift is designed and tuned to the opposite end of the relational database spectrum than an OLTP row store.

In my experience, Redshift is not designed or tuned for low latency OLTP like performance. It is a cluster that cross compiles queries into C++ programs, compiles and link them into machine code not P-code like almost every OLTP query engine, distributes them to the cluster nodes, caches the plan and programs for later reuse, and lastly queues them for execution at pretty modest concurrence to support huge resources for executing big complex OLAP and big data queries. So yes, turning that big crank, especially for the first the first time execution of small simple queries, is not its sweet spot. If your SQL workload is dominated by such small queries, especially if they are quite varyable, i.e. not executed many times, then you will see a lot of high latency which is inherent in Redshift's design. Such a workload might be better suited on Aurora or RDS. However, feed it some huge analytic queries on terabytes or more of data and you'll see it shine and produces results that a non-clustered OLTP row store can never do.

Bottom line here is Redshift is designed and tuned to the opposite end of the relational database spectrum than an OLTP row store.