Vertica performance on select from view with union

a couple of questions, as my rep is too low to post them via comments

1. What are you trying to do with the output and to what extent can you change the data model?

   • If both the table layout and output has to be exactly as in your example, then I don't see any major improvements. Outer join + isnull isn't too bad.

     • To improve performance, you could:

       • ensure there are indexes on the keys you're joining on
       • start using synthetic keys for plans (should be better then a varchar)

     • If changing the DDL or data is ok, you can also:

       • create a dedicated table to contain the default values (with no field for tariff_plan). let's call this one the "default_tariff" table and the other one "specific_tariff_types"

2. About the last requirement, I'm guessing this means its possible to have tariff plans for which no default exists? A full outer join should work too.

3. I guess you're using a TVF to sidestep the headaches of putting this in a normal view ? I have a feeling it should be possible to use a normal view without performance penalties, though the query would become more complex.

here is a proposal:

CREATE FUNCTION get_tariffplan_for
(   
    @customerplan as varchar(50)
)
RETURNS TABLE 
AS RETURN 
(
    select p.tariff_type,
        ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo, 
        ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,
        ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,
        CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback
    from default_tariff_values as p
    full outer join specific_tariff_values s
        on (p.tariff_type = s.tariff_type) and (s.tariff_plan = @customerplan)
);

edit: here is a proposal (without changing the data model)

CREATE FUNCTION get_tariffplan_for
(   
    @customerplan as varchar(50)
)
RETURNS TABLE 
AS RETURN 
(
    select p.tariff_type,
        ISNULL(s.tariff_foo, p.tariff_foo) as tariff_foo, 
        ISNULL(s.tariff_bar, p.tariff_bar) as tariff_bar,
        ISNULL(s.tariff_plan, p.tariff_plan) as tariff_plan,
        CASE WHEN s.id IS NULL THEN 1 ELSE 0 END as isfallback,
        CASE WHEN p.id IS NULL then 1 else 0 END as isexclusive
    from 
    (select tariff_type,tariff_foo,tariff_bar,tariff_plan 
    from tariff_values where tariff_plan = 'default') 
    as p
    full outer join 
        (select tariff_type,tariff_foo,tariff_bar,tariff_plan 
        from tariff_values where tariff_plan = @customerplan) 
        as s
        on (p.tariff_type = s.tariff_type)
);

There are a few things going on here:

• your CREATE VIEW statement contains a SUM() function. Even though your ALGORITHM is specified as MERGE, it is really going to be TEMPTABLE as discussed in the documentation here:

  If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs: Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

• So we're using temporary tables. I suspect you're not seeing 'blocking' due to this benefit of TEMPTABLE algorithm:

  A reason to choose TEMPTABLE explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE algorithm so that other clients that use the view are not blocked as long.

• However, the problem with the TEMPTABLE algorithm is that they do not have/use indexes [src]:

  View processing is not optimized:

  It is not possible to create an index on a view.

  Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

So now we need to know the underlying structure of your table as to why the direct query blocks:

• If it's not InnoDB, consider switching to use row-level locks instead of table-locks of MyISAM
• If there's no index on (number, bet_amount) consider creating one.

If the above points have already been done, and you still for some reason have blocking on a query directly against the salestable table, then you can institute a poor-man's materialized view by creating triggers that modify a temporary table with each number and a running total.