hi
we have one table T1, and 4 projections on it P1..P4.
P1 is basically only there to filter by ID1 (segmented and ordered by it) and returning only ID1 and ID2.
P2 is segmented and ordered by ID2.
Now if we query T1 by ID1 and join T1 alias T1_2 by ID2 then the query is fast enough for us (less than 1 second. By plan then getting MERGE join and being really fast.
P3 is basically only there to filter by ID3 (segmented and ordered by it) and returning only ID3 and ID4.
P4 is segmented and ordered by ID4.
Now if we query T1 by ID3 and join T1 alias T1_3 by ID4 then the query is NOT fast enough for us (more than 5 seconds). By plan we see that it is using then projection P2 to do the join (and also being then HASH join as the conditions for MERGE are not met) rather than P4 we expected.
If we query T1 by ID3 and join P4 alias T1_3 by ID4 then the query is fast enough for us (less than 1 second).
So if the Query Otimizer would choose the P4 projection in the second query (join over ID4) then we would be happy. Any thoughts, why is this not happening?
Br,
pj
Vertica not choosing the best projection for join?
Moderator: NorbertKrupa
Return to “Vertica Performance Tuning”
Jump to
- General
- ↳ Welcome to vertica-forums.com!
- ↳ Board Announcements
- ↳ Request for New Forums Categories
- ↳ Announcements, Events and Activities
- ↳ Vertica Links
- ↳ General
- ↳ Employment
- ↳ Vertica Certification
- Newbie
- ↳ New to Vertica
- ↳ New to Vertica Database Administration
- ↳ New to Vertica Database Development
- ↳ New to Vertica SQL
- Using Vertica
- ↳ Vertica Database Administration
- ↳ Vertica Database Development
- ↳ Vertica SQL
- ↳ Vertica SQL Functions
- ↳ Vertica Database Designer (DBD)
- ↳ Vertica User Defined Functions (UDFs)
- ↳ Vertica External Procedures
- ↳ Vertica Analytics
- ↳ Vertica Management Console
- ↳ Vertica Error Codes
- ↳ Vertica Backup & Recovery
- ↳ Vertica Installation
- ↳ Vertica Security
- ↳ Vertica Performance Tuning
- ↳ Vertica Administration Tools
- ↳ Vertica Upgrade
- ↳ Vertica Migration
- ↳ Vertica and the Operating System
- ↳ Vertica Data Load
- ↳ Vertica Tips, Lessons and Examples
- ↳ Vertica "How to..."
- Connecting to Vertica
- ↳ ADO.NET
- ↳ Cognos
- ↳ DBeaver
- ↳ dbVisualizer
- ↳ HDFS Connector
- ↳ Hadoop Connector
- ↳ HCatalog Connector
- ↳ Informatica PowerCenter
- ↳ JDBC
- ↳ Microsoft SQL Server Analysis Services (SSAS)
- ↳ MicroStrategy
- ↳ Microsoft SQL Server Integration Services (SSIS)
- ↳ ODBC
- ↳ Perl
- ↳ PHP
- ↳ Python
- ↳ R Language Integration
- ↳ Rhapsody Interface Engine
- ↳ SQL Workbench/J
- ↳ SQuirreL SQL Client
- ↳ Talend
- ↳ Tableau
- ↳ Toad for Data Analysts
- ↳ Vertica SDK API
- ↳ VoltDB
- ↳ vSQL
- Vertica Portfolio
- ↳ Vertica Premium Edition
- ↳ Vertica Express Edition
- ↳ Vertica Community Edition
- ↳ Vertica SQL on Hadoop
- ↳ Vertica in the AWS Cloud
- ↳ Vertica on Microsoft Azure
- All times are UTC+01:00
- Top
- Delete all board cookies
Powered by phpBB ® | phpBB3 theme by KomiDesign