How to optimize a merge query?
we observe a low performance with merge queries in our database. We merge around 500.000 rows into a table of more than 30 million rows.
Here an example of a log: Merged 441585 rows into table X. Elapsed time: 00:02:26.83
We optimzed our merge query according to Vertica recommendations:
1: Including all columns on the merge query
2: both target and source projections are sorted on the same columns, i.e. primary key columns
3. identically segmented target and source projections
We use the same query on two different databases, one having only one node and the other having three nodes.
The same merge query performs much better on the one-node database. Here an according log:
Merged 540672 rows into table X. Elapsed time: 00:00:31.92
I.e. on the three nodes database the same command takes 5 times longer.
Can you give us a hint on how to improve performance?