Simple select slow? What kind of performance can I expect?

Johan_Olofsson

Hi!

Sorry for a possibly extreme newbie question, but I wonder what kind of select performance I can expect from Vertica. I have started to use Vertica for a customer solution, and find that my small, simple queries seem to take unproportional long to execute when compared to their old RDBSM (Postgres 9.1).

For example, I have this table, customers, with the below scheme:

CREATE TABLE customers (
    id varchar(32) NOT NULL primary key,
    name varchar(255),
    vat varchar(255),
    address1 varchar(255),
    address2 varchar(255),      zip varchar(255),      city varchar(255),      country varchar(255),      url varchar(255),      active boolean,      created datetime,      lastlogin datetime,      pagelicenses integer,      userlicenses integer,      parent_id varchar(32),      monitor_enabled boolean DEFAULT false,      monitor_starts datetime,      monitor_recipients varchar(8000),      monitor_alarm boolean DEFAULT false,      monitor_lastvisit datetime,      monitor_lastrun datetime,      monitor_interval integer,      created_by varchar(200),      brand_prefix varchar(255),      available_pagelicenses integer DEFAULT (-1),      active_workflows boolean DEFAULT false,      max_load_visits integer DEFAULT 0  );

The ID's all contain a unique string representation of a UUID and the table holds 1203 "rows".

When I run the below super simple query it takes something like 8-10ms to execute whereas the exact same question on the exact same data in Postgres 9.1 is almost "unmeasurable" ( <0.3ms) ??

select id from customers where id='8a8484e52c50fef7012c5f8c7de9030b';                    id                  ----------------------------------   8a8484e52c50fef7012c5f8c7de9030b  (1 row)    Time: First fetch (1 row): 9.198 ms. All rows formatted: 9.238 ms

This is run on a one node Vertica v7.0.1 with no other clients connected.
Also, the execution time is very persistent, if I run the same query over and over again it will vary between 8-12ms.

Is this expected performance or is there anything I can look into to get the speed up??
I really need these queries to execute in sub-ms time...

I have of course used the DBD and included this query with the sample queries and also opted for "optimize for query".


Here's my queryplan:

------------------------------    QUERY PLAN DESCRIPTION:    ------------------------------      explain select id from customers where id='8a8484e52c50fef7012c5f8c7de9030b';      Access Path:   +-STORAGE ACCESS for customers [Cost: 2, Rows: 7] (PATH ID: 1)   |  Projection: public.customers_super   |  Materialize: customers.id   |  Filter: (customers.id = '8a8484e52c50fef7012c5f8c7de9030b')         ------------------------------    -----------------------------------------------    PLAN: BASE QUERY PLAN (GraphViz Format)   -----------------------------------------------    digraph G {   graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: explain select id from   customers where id='8a8484e52c50fef7012c5f8c7de9030b';\n\n   All Nodes Vector: \n\n  node[0]=v_pf3_node0001 (initiator) Up\n", labelloc=t,    labeljust=l ordering=out]   0[label = "Root \nOutBlk=[UncTuple]", color = "green", shape = "house"];   1[label = "NewEENode \nOutBlk=[UncTuple]", color = "green", shape = "box"];   2[label = "StorageUnionStep: customers_super\nUnc: Varchar(32)", color = "purple",    shape = "box"];   3[label = "ScanStep: customers_super\n(customers.id =    '8a8484e52c50fef7012c5f8c7de9030b')\nid\nUnc: Varchar(32)", color = "brown",    shape = "box"];   1->0 [label = "V[0]",color = "black"];   2->1 [label = "0",color = "blue"];   3->2 [label = "0",color = "blue"];   }

Now, this doesn't tell me very much so my hope is that someone could enlighten me on wether there is anything I can tweak to speed up things, or if this is expected (and acceptable) performance.

Thanks in advance,
Johan
Sweden

norbert_krupa

Just looking at your execution plan, it looks like you're using the default super projection on a fairly wide table. You'll get much better performance if you were to run Database Designer and provide some sample queries.