Query performance problem

Grega_Kešpret

We see disparities in query execution times which we do not understand. The tests we ran (see below outputs) are all reproducible on our production system. Our production system is running Vertica 6.1.1. celtra=> SELECT VERSION(); VERSION ------------------------------------ Vertica Analytic Database v6.1.1-0 (1 row) We have a base table sessions: CREATE TABLE sessions ( day DATE NOT NULL, creativeId CHAR(8) NOT NULL, sessionId INTEGER NOT NULL, ... PRIMARY KEY (day, creativeId, sessionId) ); and we defined two projections segmented by MODULARHASH: CREATE PROJECTION sessions_super_by_day AS SELECT * FROM sessions ORDER BY day, creativeId, sessionId SEGMENTED BY MODULARHASH(day, creativeId, sessionId) ALL NODES; CREATE PROJECTION sessions_super_by_creativeId AS SELECT * FROM sessions ORDER BY creativeId, day, sessionId SEGMENTED BY MODULARHASH(day, creativeId, sessionId) ALL NODES OFFSET 1; Next, we query both projections (not simultaneously) and measure query execution times. celtra=> SELECT count(*) FROM sessions_super_by_day; count ------------ 1893063056 (1 row) Time: First fetch (1 row): 3745.070 ms. All rows formatted: 3745.177 ms celtra=> SELECT count(*) FROM sessions_super_by_creativeId; count ------------ 1893063056 (1 row) Time: First fetch (1 row): 49087.502 ms. All rows formatted: 49087.583 ms Cardinalities of day and creativeId columns are both small (~100 and ~10000). There are the outputs of corresponding EXPLAIN statements for above queries: EXPLAIN VERBOSE SELECT count(*) FROM sessions_super_by_day; Access Path: LDISTRIB_UNSEGMENTED +-GROUPBY NOTHING [Cost: 1535186.000000, Rows: 1.000000 Disk(B): 0.000000 CPU(B): 15144504448.000000 Memory(B): 32.000000 Netwrk(B): 8.000000 Parallelism: 4.000000] [OutRowSz (B): 8] (PATH ID: 1) | Aggregates: count(*) | Execute on: All Nodes | LDISTRIB_UNSEGMENTED | +---> STORAGE ACCESS for sessions_super_by_day [Cost: 1442750.000000, Rows: 1893063056.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 4.000000] [OutRowSz (B): 8] (PATH ID: 2) | | Column Cost Aspects: [ Disk(B): 20609105920.000000 CPU(B): 0.000000 Memory(B): 15144504448.000000 Netwrk(B): 0.000000 Parallelism: 4.000000 ] | | Projection: mab.sessions_super_by_day | | Materialize: sessions_super_by_day.day | | Execute on: All Nodes | | Sort Key: (sessions_super_by_day.day, sessions_super_by_day.creativeId, sessions_super_by_day.sessionId) | | LDISTRIB_SEGMENTED EXPLAIN VERBOSE SELECT count(*) FROM sessions_super_by_creativeId; Access Path: LDISTRIB_UNSEGMENTED +-GROUPBY NOTHING [Cost: 370629.000000, Rows: 1.000000 Disk(B): 0.000000 CPU(B): 30289008896.000000 Memory(B): 32.000000 Netwrk(B): 8.000000 Parallelism: 4.000000] [OutRowSz (B): 8] (PATH ID: 1) | Aggregates: count(*) | Execute on: All Nodes | LDISTRIB_UNSEGMENTED | +---> STORAGE ACCESS for sessions_super_by_creativeId [Cost: 185758.000000, Rows: 1893063056.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 4.000000] [OutRowSz (B): 16] (PATH ID: 2) | | Column Cost Aspects: [ Disk(B): 14548992.000000 CPU(B): 0.000000 Memory(B): 15144504448.000000 Netwrk(B): 0.000000 Parallelism: 4.000000 ] | | Projection: mab.sessions_super_by_creativeId | | Materialize: sessions_super_by_creativeId.creativeId | | Execute on: All Nodes | | Sort Key: (sessions_super_by_creativeId.creativeId, sessions_super_by_creativeId.day, sessions_super_by_creativeId.sessionId) | | LDISTRIB_SEGMENTED Our first question is why is there such a big disparity in query execution times? When we modify our query to include a filter on 'day' column (WHERE condition), which effectively encompasses all data, the query response time on both projections becomes fast. celtra=> SELECT count(*) FROM sessions_super_by_day WHERE day < '2020-01-02'; count ------------ 1893063056 (1 row) Time: First fetch (1 row): 4181.928 ms. All rows formatted: 4182.025 ms celtra=> SELECT count(*) FROM sessions_super_by_creativeId WHERE day < '2020-01-02'; count ------------ 1893063056 (1 row) Time: First fetch (1 row): 4775.328 ms. All rows formatted: 4775.423 ms Again, we run EXPLAIN on above queries. EXPLAIN VERBOSE SELECT count(*) FROM sessions_super_by_day WHERE day < '2020-01-02'; Access Path: LDISTRIB_UNSEGMENTED +-GROUPBY NOTHING [Cost: 1552538.000000, Rows: 1.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 32.000000 Netwrk(B): 8.000000 Parallelism: 4.000000] [OutRowSz (B): 8] (PATH ID: 1) | Aggregates: count(*) | Execute on: All Nodes | LDISTRIB_UNSEGMENTED | +---> STORAGE ACCESS for sessions_super_by_day [Cost: 1552537.000000, Rows: 1893063055.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 4.000000] [OutRowSz (B): 0] (PATH ID: 2) | | Column Cost Aspects: [ Disk(B): 20370783669.297089 CPU(B): 20370783669.297089 Memory(B): 15144504439.302990 Netwrk(B): 0.000000 Parallelism: 4.000000 ] | | Projection: mab.sessions_super_by_day | | Filter: (sessions_super_by_day.day < '2020-01-02'::date)/* sel=1.000000 ndv= 77 */ | | Execute on: All Nodes | | Sort Key: (sessions_super_by_day.day, sessions_super_by_day.creativeId, sessions_super_by_day.sessionId) | | LDISTRIB_SEGMENTED EXPLAIN VERBOSE SELECT count(*) FROM sessions_super_by_creativeId WHERE day < '2020-01-02'; Access Path: LDISTRIB_UNSEGMENTED +-GROUPBY NOTHING [Cost: 1603061.000000, Rows: 1.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 32.000000 Netwrk(B): 8.000000 Parallelism: 4.000000] [OutRowSz (B): 8] (PATH ID: 1) | Aggregates: count(*) | Execute on: All Nodes | LDISTRIB_UNSEGMENTED | +---> STORAGE ACCESS for sessions_super_by_creativeId [Cost: 1603060.000000, Rows: 1893063055.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 4.000000] [OutRowSz (B): 0] (PATH ID: 2) | | Column Cost Aspects: [ Disk(B): 21123301376.000000 CPU(B): 21123301376.000000 Memory(B): 15144504439.302990 Netwrk(B): 0.000000 Parallelism: 4.000000 ] | | Projection: mab.sessions_super_by_creativeId | | Filter: (sessions_super_by_creativeId.day < '2020-01-02'::date)/* sel=1.000000 ndv= 77 */ | | Execute on: All Nodes | | Sort Key: (sessions_super_by_creativeId.creativeId, sessions_super_by_creativeId.day, sessions_super_by_creativeId.sessionId) | | LDISTRIB_SEGMENTED Our second question is why did inclusion of arbitrary filter (it does not actually filter anything) suddenly speed up the query on the 'slow' projection? We modify our query again, filtering on creativeId column instead of day column. Again, the filter is composed such that it encompasses all data (effectively nothing is filtered out). celtra=> SELECT count(*) FROM sessions_super_by_day WHERE creativeId < 'ffffffff'; count ------------ 1893063056 (1 row) Time: First fetch (1 row): 63830.530 ms. All rows formatted: 63830.608 ms celtra=> SELECT count(*) FROM sessions_super_by_creativeId WHERE creativeId < 'ffffffff'; count ------------ 1893063056 (1 row) Time: First fetch (1 row): 70580.566 ms. All rows formatted: 70580.664 ms Again, the output of EXPLAIN statements: EXPLAIN VERBOSE SELECT count(*) FROM sessions_super_by_day WHERE creativeId < 'ffffffff'; Access Path: LDISTRIB_UNSEGMENTED +-GROUPBY NOTHING [Cost: 186081.000000, Rows: 1.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 32.000000 Netwrk(B): 8.000000 Parallelism: 4.000000] [OutRowSz (B): 8] (PATH ID: 1) | Aggregates: count(*) | Execute on: All Nodes | LDISTRIB_UNSEGMENTED | +---> STORAGE ACCESS for sessions_super_by_day [Cost: 186080.000000, Rows: 1893063055.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 4.000000] [OutRowSz (B): 0] (PATH ID: 2) | | Column Cost Aspects: [ Disk(B): 18022400.000000 CPU(B): 18022400.000000 Memory(B): 15144504439.302990 Netwrk(B): 0.000000 Parallelism: 4.000000 ] | | Projection: mab.sessions_super_by_day | | Filter: (sessions_super_by_day.creativeId < 'ffffffff')/* sel=1.000000 ndv= 2885 */ | | Execute on: All Nodes | | Sort Key: (sessions_super_by_day.day, sessions_super_by_day.creativeId, sessions_super_by_day.sessionId) | | LDISTRIB_SEGMENTED EXPLAIN VERBOSE SELECT count(*) FROM sessions_super_by_creativeId WHERE creativeId < 'ffffffff'; Access Path: LDISTRIB_UNSEGMENTED +-GROUPBY NOTHING [Cost: 185916.000000, Rows: 1.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 32.000000 Netwrk(B): 8.000000 Parallelism: 4.000000] [OutRowSz (B): 8] (PATH ID: 1) | Aggregates: count(*) | Execute on: All Nodes | LDISTRIB_UNSEGMENTED | +---> STORAGE ACCESS for sessions_super_by_creativeId [Cost: 185915.000000, Rows: 1893063055.000000 Disk(B): 0.000000 CPU(B): 0.000000 Memory(B): 0.000000 Netwrk(B): 0.000000 Parallelism: 4.000000] [OutRowSz (B): 0] (PATH ID: 2) | | Column Cost Aspects: [ Disk(B): 15570621.668079 CPU(B): 15570621.668079 Memory(B): 15144504439.302990 Netwrk(B): 0.000000 Parallelism: 4.000000 ] | | Projection: mab.sessions_super_by_creativeId | | Filter: (sessions_super_by_creativeId.creativeId < 'ffffffff')/* sel=1.000000 ndv= 2885 */ | | Execute on: All Nodes | | Sort Key: (sessions_super_by_creativeId.creativeId, sessions_super_by_creativeId.day, sessions_super_by_creativeId.sessionId) | | LDISTRIB_SEGMENTED As can be seen from above, now queries on both projections are slow. What slowed down the queries? We tried adding different permutations of filters, which are not actually limiting any data&colon; - day < '2020-01-01' - creativeId < 'ffffffff' (referred to as "cid" below) - sessionId < 999999999999 (referred to as "sid" below) projection / filter by columns | no filter | day | cid | sid | day + cid | day + sid | cid + sid | day + cid + sid | -------------------------------|--------------|------------------------------------------------------------------------------------------------------- sessions_super_by_day | 3745.070 ms | 4181.928 ms | 63830.530 ms | 4329.763 ms | 6313.164 ms | 5753.121 ms | 6433.096 ms | 7947.076 ms | sessions_super_by_creativeId | 49087.502 ms | 4775.328 ms | 70580.566 ms | 5217.864 ms | 7269.565 ms | 6206.740 ms | 8210.272 ms | 8265.184 ms | We know for a fact that: - Slow queries take >10x more time to execute on the same data - Query plan outputs for the queries that are *slow* show orders of magnitude (> 1000x) *lower* Cost estimate, which is affected by order of magnitude smaller Disk estimates. Your prompt attention to this matter is appreciated. Best regards, Grega

[Deleted User]

Hi Grega, Thanks for your detailed posting!, and for your interesting question. Can I ask you to run two more queries?: select count(distinct day) from sessions; select count(distinct creativeId) from sessions; I suspect that the value returned by the first query will be dramatically smaller than the value returned by the second query. If I'm wrong, well, then back to the drawing board :-) But if I'm right, I think the key to what's going on here lies in one of your last statements: > - Slow queries take >10x more time to execute on the same data Actually, this statement is not quite true: The queries are not operating on the same data. They are operating on the same *rows*, yes. But Vertica is not a row-store database; it is a column-store database. And the queries are operating on different columns. They are therefore totally different queries -- they may never touch the same data, even the same data files, on disk at all. Vertica has various optimizations for working with columns with a low number of distinct values, largely based on the fact that it stores each column independently compressed and encoded. If you think about it, "count(*)" doesn't need to read the whole table; it just needs to count the records in any one column. By default, I believe (though I haven't checked recently) that it picks some column that you're already scanning for some other reason (ie., a predicate). If you're not scanning a column already, it just picks one. I think it defaults to the first column, because the Database Designer will typically produce at least one projection with a low-cardinality first column (because it compresses extremely well so doesn't use much disk space, and greatly speeds up this type of query -- the first column in the sort order typically compresses best because it's, well, sorted). On that note: You typically shouldn't be creating your own projections for production systems. I don't mean to be rude; I'm glad that you're experimenting with this functionality. What I mean is, rather, that that's not how Vertica is supposed to be used. Just because you can do it doesn't mean it's the right way. Projection design is, in general, supposed to be performed automatically by Vertica's Database Designer, in adminTools. It will generally resolve these sorts of issues for you; you can simply write your queries directly against the tables (ignoring projections altogether at query-time), and let Vertica sort out how to query optimally. The DBD looks at a sample of your real data and your real queries; once we know both with certainty, we can in fact produce theoretically-optimal projection designs completely automatically. (As proof, if you think about it, we could brute-force it -- generate all possible projection designs and test all possible queries against them. Then we would, of course, know for sure what's fastest. Though what we actually do in practice is much smarter and more efficient than that.) The main use cases for manual projection creation are for restoring an existing schema that the DBD already created, and for working around bugs or limitations in the current DBD or the current query optimizer. If you're hitting an instance of the latter case, please let us know so we can fix it. (If you have an enterprise account, please file a support case.)

Grega_Kešpret

Hi Adam, you are correct about the cardinality of day being smaller than cardinality of creativeId. celtra=> select count(distinct day) from sessions; count ------- 99 (1 row) whereas celtra=> select count(distinct creativeId) from sessions; count ------- 14456 (1 row) If what you have said is true, namely that Vertica: - uses the column it is already using (i.e. predicate) - or uses the first column - and that 'creativeId' compresses much better than 'day' that would explain why all queries where we are counting projection sorted by day without where condition (first column picked: day) are faster and why queries that pick creativeId are slower. However, sessionId has much, much greater cardinality than even creativeId. Actually, each "row" has unique sessionId. But counting by sessionId is still fast. Very good compressibility of sessionId may well explain this behaviour. I still have one question. The main reason why we "bumped" into this performance difference (probably due to the compressibility of columns) is that by default, when doing "select count(*) from mab.sessions;" Vertica chooses projection sessions_super_by_creativeId. And that is slow. Can we influence which projection is targeted with such a query at design time? Otherwise we could add arbitrary predicate just to explicitly target better compressed column. Thank you for your prompt response. Best regards, Grega Kespret

[Deleted User]

Hi Grega, I'm glad to hear that this makes sense. Regarding choosing a projection or column, I'm surprised to hear that we're choosing the sessions_super_by_creativeId projection. I'm not sure why that's happening; others may step in to answer that. If you have an enterprise account, feel free to contact support. Rather than making up an arbitrary predicate, does "select count(day) from mab.sessions" do what you'd like here?

[Deleted User]

Also, regarding creativeId vs sessionId, probably part of the issue is the data type. Vertica is often able to compress INTEGERs much better than small CHAR fields; similarly, they are much cheaper computationally to work with. If you have this flexibility, I'd be curious what performance you would see if you switched the creativeId column to be an integer too, perhaps a foreignkey into a table with the actual ID strings listed.

Grega_Kešpret

Hello Adam, indeed, count(day) acts as expected. Also, we will try to make creativeId INT if possible. Thank you.

Zvika_Gutkin_1

Hi Adam, I just tested compression of number vs varchar and found that vertica compressed the varchar column more than the number. This is not what I expected Can you clarify it ? here is what I did create table z_number1 (VISITOR_ID number) unsegmented all nodes; -- insert 19,907,614 rows which 546 are distinct insert /*+ direct */ into z_number1 select * from public.z1_number; create table z1_char1 (VISITOR_ID varchar) unsegmented all nodes; ---- insert 19,907,614 rows which 546 are distinct insert /*+ direct */ into z1_char1 select * from public.z1_char; commit; SELECT ANALYZE_HISTOGRAM('public.z_number1'); SELECT ANALYZE_HISTOGRAM('public.z1_char1'); SELECT p.projection_schema,p.anchor_table_name AS table_name,ps.projection_name, -- 460,140,454 cast(SUM(ps.wos_row_count::number + ps.ros_row_count::number) as number) AS row_count_sum, count(*) as count_nodes, (SUM(ps.wos_used_bytes + ps.ros_used_bytes)) AS B_count, (SUM(ps.wos_used_bytes + ps.ros_used_bytes)/(count(*))) AS B_per_node FROM v_catalog.projections p JOIN v_monitor.projection_storage ps on p.projection_name = ps.projection_name and p.projection_schema = ps.anchor_table_schema where p.projection_schema ilike '' and upper(p.anchor_table_name) ilike '' GROUP BY p.projection_schema,p.anchor_table_name ,ps.projection_name ORDER BY p.projection_schema,p.anchor_table_name ,ps.projection_name; results number => 2501067 bytes varchar => 120125 bytes -- creating the same tables but encoded the column as RLE create table z1_char2 (VISITOR_ID varchar encoding rle ) unsegmented all nodes; insert /*+ direct */ into z1_char2 select * from public.z1_char; create table z1_number2 (VISITOR_ID number encoding rle ) unsegmented all nodes; insert /*+ direct */ into z1_number2 select * from public.z1_number; commit; SELECT ANALYZE_HISTOGRAM('public.z1_char2'); SELECT ANALYZE_HISTOGRAM('public.z1_number2'); results number => 121219 bytes varchar => 120125 bytes Thanks Zvika