[teiid-issues] [JBoss JIRA] (TEIID-5440) Couchbase translator - LET usage prevents index scan
Jan Stastny (JIRA)
issues at jboss.org
Fri Aug 10 10:29:00 EDT 2018
[ https://issues.jboss.org/browse/TEIID-5440?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13617705#comment-13617705 ]
Jan Stastny commented on TEIID-5440:
------------------------------------
Probably depends on whether the column in where criteria is also projected. That's why the first query is pushed differently.
> Couchbase translator - LET usage prevents index scan
> ----------------------------------------------------
>
> Key: TEIID-5440
> URL: https://issues.jboss.org/browse/TEIID-5440
> Project: Teiid
> Issue Type: Quality Risk
> Components: Misc. Connectors
> Affects Versions: 8.12.14.6_4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
>
> Couchbase translator excesively uses LET clause(1) just for purpose of naming columns.
> That's not correct usage of LET clause and affects query execution in couchbase.
> Simple query
> {code:sql}
> SELECT IntKey FROM SmallA;
> {code}
> is being pushed as
> {code:sql}
> SELECT `$cb_c1_IntKey` c1 FROM `dvqe` `$cb_t1` LET `$cb_c1_IntKey` = `$cb_t1`.`intkey` WHERE `$cb_t1`.`type` = 'smalla'
> {code}
> Which is fine and index on `type` column is scanned on couchbase side.
> But when I issue query
> {code:sql}
> SELECT * FROM SmallA;
> {code}
> it is pushed as
> {code:sql}
> SELECT
> `$cb_c1_documentID` c1,
> `$cb_c2_FloatNum` c2,
> `$cb_c3_IntKey` c3,
> `$cb_c4_BigIntegerValue` c4,
> `$cb_c5_StringKey` c5,
> `$cb_c6_CharValue` c6,
> `$cb_c7_LongNum` c7,
> `$cb_c8_type` c8,
> `$cb_c9_DoubleNum` c9,
> `$cb_c10_ObjectValue` c10,
> `$cb_c11_ShortValue` c11,
> `$cb_c12_BigDecimalValue` c12,
> `$cb_c13_DateValue` c13,
> `$cb_c14_BooleanValue` c14,
> `$cb_c15_TimestampValue` c15,
> `$cb_c16_ByteNum` c16,
> `$cb_c17_StringNum` c17,
> `$cb_c18_TimeValue` c18,
> `$cb_c19_IntNum` c19
> FROM `dvqe` `$cb_t1`
> LET `$cb_c1_documentID` = META(`$cb_t1`).id,
> `$cb_c2_FloatNum` = `$cb_t1`.`floatnum`,
> `$cb_c3_IntKey` = `$cb_t1`.`intkey`,
> `$cb_c4_BigIntegerValue` = `$cb_t1`.`bigintegervalue`,
> `$cb_c5_StringKey` = `$cb_t1`.`stringkey`,
> `$cb_c6_CharValue` = `$cb_t1`.`charvalue`,
> `$cb_c7_LongNum` = `$cb_t1`.`longnum`,
> `$cb_c8_type` = `$cb_t1`.`type`,
> `$cb_c9_DoubleNum` = `$cb_t1`.`doublenum`,
> `$cb_c10_ObjectValue` = `$cb_t1`.`objectvalue`,
> `$cb_c11_ShortValue` = `$cb_t1`.`shortvalue`,
> `$cb_c12_BigDecimalValue` = `$cb_t1`.`bigdecimalvalue`,
> `$cb_c13_DateValue` = `$cb_t1`.`datevalue`,
> `$cb_c14_BooleanValue` = `$cb_t1`.`booleanvalue`,
> `$cb_c15_TimestampValue` = `$cb_t1`.`timestampvalue`,
> `$cb_c16_ByteNum` = `$cb_t1`.`bytenum`,
> `$cb_c17_StringNum` = `$cb_t1`.`stringnum`,
> `$cb_c18_TimeValue` = `$cb_t1`.`timevalue`,
> `$cb_c19_IntNum` = `$cb_t1`.`intnum`
> WHERE `$cb_c8_type` = 'smalla'
> {code}
> The way how WHERE criteria is being pushed (either column or LET variable reference) affects execution plan and prevents index scan in the latter case. (WHERE `$cb_t1`.`type` = 'smalla' vs. WHERE `$cb_c8_type` = 'smalla').
> (1) https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/let.html
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
More information about the teiid-issues
mailing list