FW: Column Cardinality and its influence on a Projection's sort order

Hi Mike,

I think that a more important factor in the sort order here is the order of the predicates in the query. What happens with DBD when the predicate order is switched in the query?

Generally speaking, in OLTP environments, you want to create an index starting with the column with the highest cardinality. In Vertica, since we are not generally looking for a singleton result set, I think that the overriding factor in sort would be whether the column was in the high in the group by listing.

Hello,
Also wondering how DBD treats this.
When talking about cardinality, should we also consider the predicate (filtering) factor? If a predicate on a high cardinality column is restrictive enough, does it make sense to put it first in sort order?

Thanks,
Ning

select count (distinct transaction_type)
  from online_sales.online_sales_fact;
--------------
2 

  select count (distinct customer_key)
  from online_sales.online_sales_fact;
---------------
50000
/* Note that customer_key is not a PK, I am not trying to perform a singleton select */ 

select sum(sales_dollar_amount)
from online_sales.online_sales_fact
where transaction_type = 'purchase'
and customer_key = 1;

CREATE PROJECTION online_sales_fact_DBD_1_seg_d01_b0 /*+basename(online_sales_fact_DBD_1_seg_d01),createtype(D)*/
(
customer_key ENCODING RLE,
sales_dollar_amount ENCODING BLOCKDICT_COMP,
transaction_type ENCODING RLE
)
AS
SELECT customer_key,
        sales_dollar_amount,
        transaction_type
FROM online_sales.online_sales_fact
ORDER BY customer_key,
          transaction_type,
          sales_dollar_amount
SEGMENTED BY MODULARHASH (customer_key) ALL NODES OFFSET 0;

CREATE PROJECTION online_sales_fact_DBD_1_seg_d01_b1 /*+basename(online_sales_fact_DBD_1_seg_d01),createtype(D)*/
(
customer_key ENCODING RLE,
sales_dollar_amount ENCODING BLOCKDICT_COMP,
transaction_type ENCODING RLE
)
AS
SELECT customer_key,
        sales_dollar_amount,
        transaction_type
FROM online_sales.online_sales_fact
ORDER BY customer_key,
          transaction_type,
          sales_dollar_amount
SEGMENTED BY MODULARHASH (customer_key) ALL NODES OFFSET 1;

select sum(sales_dollar_amount)
from online_sales.online_sales_fact
where
    customer_key = 1
and
      transaction_type = 'purchase'
;

-- Add column
alter table online_sales.online_sales_fact
add column transaction_type_int integer
default case when transaction_type = 'purchase' then 1
else 2 end;

-- Amended input query
select sum(sales_dollar_amount)
from online_sales.online_sales_fact
where
      transaction_type_int = 1
and
    customer_key = 1
;

CREATE PROJECTION online_sales_fact_DBD_1_seg_q3b_b0 /*+basename(online_sales_fact_DBD_1_seg_q3b),createtype(D)*/
(
customer_key ENCODING RLE,
sales_dollar_amount ENCODING BLOCKDICT_COMP,
transaction_type_int ENCODING RLE
)
AS
SELECT customer_key,
        sales_dollar_amount,
        transaction_type_int
FROM online_sales.online_sales_fact
ORDER BY customer_key,
          transaction_type_int,
          sales_dollar_amount
SEGMENTED BY MODULARHASH (customer_key) ALL NODES OFFSET 0;

CREATE PROJECTION online_sales_fact_DBD_1_seg_q3b_b1 /*+basename(online_sales_fact_DBD_1_seg_q3b),createtype(D)*/
(
customer_key ENCODING RLE,
sales_dollar_amount ENCODING BLOCKDICT_COMP,
transaction_type_int ENCODING RLE
)
AS
SELECT customer_key,
        sales_dollar_amount,
        transaction_type_int
FROM online_sales.online_sales_fact
ORDER BY customer_key,
          transaction_type_int,
          sales_dollar_amount
SEGMENTED BY MODULARHASH (customer_key) ALL NODES OFFSET 1;

Appears that what you are proving is that when you provide sample queries to DBD they become a very large factor in design even when a low-cardinality column at the beginning of the sort order might save space and not have much impact at all on the query you provided.

If you run a comprehensive design without any queries then low-cardinality columns would likely be at the beginning of your sort order. If you provide a sample query file with more queries that only accessed only transaction_type (more queries than those that include customer_key) then DBD would likely make transaction_type the high sort order column.

You might want to provide your results in a JIRA story. I know that improving DBD is on the roadmap. There might already be info like this in JIRA if you want to search.

Hello,
The basis of this thread is that I have a customer with a query that is running 40x faster after they ignored our documentation / training and instead, they designed a projection sorted on high-cardinality,low-cardinality.
They are questioning our documented advice.

I am not really trying to prove the DBD behaviour, perhaps I am trying to _understand _the DBD behaviour. I agree that the most recent update on this thread (https://forum.vertica.com/discussion/comment/241569#Comment_241569) certainly did focus on the DBD behaviour. But originally I had been focussing more generally on the behaviour of the optimiser itself (see my initial post on this thread about the optimiser's choice of projection).

I sidetracked to the DBD behaviour, simply to understand whether the DBD does behave as we might expect, because there are many documented Vertica examples in which we advocate defining the projection's sort order with low-cardinality columns before the high-cardinality columns, eg https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/ConfiguringTheDB/PhysicalSchema/ChoosingSortOrderBestPractices.htm
My sidetrack just confirmed that the DBD behaves in the same way as the optimiser; ie. they both preferred to use a projection that is storted on high-cardinality before low-cardinality columns.

RLE can be great as saving storage space, and most certainly, if we ran a comprehensive DBD and if the queries we provide include lots of examples with predicates using a low-cardinality column, then the DBD's suggested projections might well be different.

My customer is not so concerned about saving space, RLE etc. They want to know why we advise a general method which gives them 40x worse performance.

I'll start a JIRA on this topic, but I don't want to prejudice the outcome with a hint of "Fix the DBD". In my sidetrack example, the DBD is making the correct decision ie. to make the query perform faster.
We need to provide better advice on how to choose the sort order for projections.
I can only find a single instance in our documentation in which we make this type of "high->low sort order" suggestion -
https://my.vertica.com/docs/latest/index.htm#Authoring/AdministratorsGuide/ConfiguringTheDB/PhysicalSchema/ChoosingSortOrderBestPractices.htm -
Sort on Columns in Important Queries
- "If that query uses a high-cardinality column such as Social Security number, you may sacrifice storage by placing this column early in the sort order of a projection, but your most important query will be optimized."

The bulk of our advice is to sort projections on low cardinailty columns first.