You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by lawrencefinn <la...@gmail.com> on 2017/01/04 23:55:02 UTC
grouped index sort vs filter
I have an object with fields member_id, name, and priority. I have created a
grouped index on member_id+ name and member_id + priority. I want to run
queries like "select * from table where member_id = 123 order by member_id,
priority asc. The problem is that sometimes the query optimizer chooses the
wrong index for the where clause (member_id + name) and the sorting cannot
take advantage of the index. Is there a way to coerce the right index to be
used in the WHERE clause that matches the ORDER BY clause?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: grouped index sort vs filter
Posted by lawrencefinn <la...@gmail.com>.
Okay. Index hinting would be very useful for our use case and I can imagine
many others would want it too. Either way, thanks!
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p10324.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: grouped index sort vs filter
Posted by Sergi Vladykin <se...@gmail.com>.
There are no exact plans when we will have H2 released, now we work on some
other important improvements there. Anyway this is negotiable.
Sergi
2017-01-28 1:26 GMT+03:00 Denis Magda <dm...@apache.org>:
> The sql hints are planned for 2.0 at the moment. You can keep an eye on
> this ticket:
> https://issues.apache.org/jira/browse/IGNITE-4594
>
> This should happen in Q2. Here we also depends on H2 release. Sergi, as H2
> committer, do you have any idea when H2 guys plan to release the next
> version?
>
> —
> Denis
>
> On Jan 27, 2017, at 1:22 PM, lawrencefinn <la...@gmail.com> wrote:
>
> OMG YOU ARE THE BEST!!!!
>
> This code has been such a pain in the butt to do. When do you think the
> next release will be (im guessing 1.9.0?)
>
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p10294.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
>
>
Re: grouped index sort vs filter
Posted by Denis Magda <dm...@apache.org>.
The sql hints are planned for 2.0 at the moment. You can keep an eye on this ticket:
https://issues.apache.org/jira/browse/IGNITE-4594 <https://issues.apache.org/jira/browse/IGNITE-4594>
This should happen in Q2. Here we also depends on H2 release. Sergi, as H2 committer, do you have any idea when H2 guys plan to release the next version?
—
Denis
> On Jan 27, 2017, at 1:22 PM, lawrencefinn <la...@gmail.com> wrote:
>
> OMG YOU ARE THE BEST!!!!
>
> This code has been such a pain in the butt to do. When do you think the
> next release will be (im guessing 1.9.0?)
>
>
>
>
> --
> View this message in context: http://apache-ignite-users.70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p10294.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: grouped index sort vs filter
Posted by lawrencefinn <la...@gmail.com>.
OMG YOU ARE THE BEST!!!!
This code has been such a pain in the butt to do. When do you think the
next release will be (im guessing 1.9.0?)
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p10294.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: grouped index sort vs filter
Posted by Sergi Vladykin <se...@gmail.com>.
Actually we allow to enforce join order in SQL (see [1]). I hope in the
next release we will support index hints as well (they are already merged
to H2 master).
[1]
http://ignite.apache.org/releases/1.8.0/javadoc/org/apache/ignite/cache/query/SqlFieldsQuery.html#setEnforceJoinOrder(boolean)
Sergi
2017-01-27 5:40 GMT+03:00 lawrencefinn <la...@gmail.com>:
> Sorry for the delay, been working hard on this. We have changed the data
> model a lot but here it is from the git history:
> Here is the class: LineItem - http://pastebin.com/hTuXa5E3
>
> The query is something like select li._val from lineitem li where member_id
> = 123 order by member_id, name asc.
>
> Our solution around this works but is a bit of a pain in the butt. We
> created "fake" boolean fields/keys for each of the composite keys with the
> value of false. So in this example we created a key called "f_name". We
> updated the composite key "member_name" to actually take in 3 fields -
> member_id, f_name, and name. Then our query becomes something like
> select li._val from lineitem li where member_id = 123 and f_name = false
> order by member_id, f_name, name
>
> Including the fake field in the where clause forces the correct index to be
> used. We actually did this to support descending and ascending indexes for
> the same fields too.
>
> Here is an example of what it looks like in the end:
> @QuerySqlField(index = true, name = "member_id", orderedGroups = {
> @QuerySqlField.Group(name = "member_name", order = 0),
> @QuerySqlField.Group(descending = true, name =
> "member_name_desc", order = 0),
> })
> private Long memberId;
>
> @QuerySqlField(index = true, orderedGroups = {
> @QuerySqlField.Group(name = MEMBER_NAME, order = 2),
> @QuerySqlField.Group(descending = true, name =
> "member_name_desc", order = 2)
> })
> private String name;
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(name =
> MEMBER_NAME,
> order = 1)})
> protected boolean f_name = false;
> @QuerySqlField(orderedGroups = {@QuerySqlField.Group(descending =
> true,
> name = "member_name_desc", order = 1)})
> protected boolean f_name_desc = false;
>
>
> I REALLY wish H2 had the ability to force join order or hint indexes. It
> looks like that is WAY out in their roadmap :(
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p10277.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: grouped index sort vs filter
Posted by lawrencefinn <la...@gmail.com>.
Sorry for the delay, been working hard on this. We have changed the data
model a lot but here it is from the git history:
Here is the class: LineItem - http://pastebin.com/hTuXa5E3
The query is something like select li._val from lineitem li where member_id
= 123 order by member_id, name asc.
Our solution around this works but is a bit of a pain in the butt. We
created "fake" boolean fields/keys for each of the composite keys with the
value of false. So in this example we created a key called "f_name". We
updated the composite key "member_name" to actually take in 3 fields -
member_id, f_name, and name. Then our query becomes something like
select li._val from lineitem li where member_id = 123 and f_name = false
order by member_id, f_name, name
Including the fake field in the where clause forces the correct index to be
used. We actually did this to support descending and ascending indexes for
the same fields too.
Here is an example of what it looks like in the end:
@QuerySqlField(index = true, name = "member_id", orderedGroups = {
@QuerySqlField.Group(name = "member_name", order = 0),
@QuerySqlField.Group(descending = true, name =
"member_name_desc", order = 0),
})
private Long memberId;
@QuerySqlField(index = true, orderedGroups = {
@QuerySqlField.Group(name = MEMBER_NAME, order = 2),
@QuerySqlField.Group(descending = true, name =
"member_name_desc", order = 2)
})
private String name;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(name = MEMBER_NAME,
order = 1)})
protected boolean f_name = false;
@QuerySqlField(orderedGroups = {@QuerySqlField.Group(descending = true,
name = "member_name_desc", order = 1)})
protected boolean f_name_desc = false;
I REALLY wish H2 had the ability to force join order or hint indexes. It
looks like that is WAY out in their roadmap :(
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p10277.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: grouped index sort vs filter
Posted by vkulichenko <va...@gmail.com>.
Hi,
Can you still provide a test case?
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p9977.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: grouped index sort vs filter
Posted by lawrencefinn <la...@gmail.com>.
I made a mistake when I said sometimes. It is consistently choosing one
index as of now. It's just that the specified index is not useful for
sorting.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p9976.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: grouped index sort vs filter
Posted by Sergi Vladykin <se...@gmail.com>.
Hi,
What does it mean *sometimes* optimizer chooses wrong index? Does it mean
that you can run this query with correct plan at the first time and another
time it will run with wrong plan?
Can you share a reproducible test case then?
Sergi
2017-01-05 17:47 GMT+03:00 lawrencefinn <la...@gmail.com>:
> It's pretty significant, and it get's more significant with joins. Below
> is
> attached some data from a simple example. When the index is used for
> sorting, it is SUPER fast. Just hoping to take advantage of that :(
> *****Wrong index ****
> [SELECT
> LI._VAL AS __C0,
> MEMBER_ID AS __C1,
> NAME AS __C2
> FROM "lineitem".LINEITEM LI
> /* "lineitem"."member_lifetime_budget": MEMBER_ID = 12 */
> WHERE MEMBER_ID = 12
> ORDER BY 2, 3
> LIMIT 25]
>
> *Time 162*
>
> ***** Correct index ****
> [SELECT
> LI._VAL AS __C0,
> MEMBER_ID AS __C1,
> NAME AS __C2
> FROM "lineitem".LINEITEM LI
> /* "lineitem"."member_name": MEMBER_ID = 12 */
> WHERE MEMBER_ID = 12
> ORDER BY 2, 3
> LIMIT 25
> /* index sorted */]
> Time 7
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p9901.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: grouped index sort vs filter
Posted by lawrencefinn <la...@gmail.com>.
It's pretty significant, and it get's more significant with joins. Below is
attached some data from a simple example. When the index is used for
sorting, it is SUPER fast. Just hoping to take advantage of that :(
*****Wrong index ****
[SELECT
LI._VAL AS __C0,
MEMBER_ID AS __C1,
NAME AS __C2
FROM "lineitem".LINEITEM LI
/* "lineitem"."member_lifetime_budget": MEMBER_ID = 12 */
WHERE MEMBER_ID = 12
ORDER BY 2, 3
LIMIT 25]
*Time 162*
***** Correct index ****
[SELECT
LI._VAL AS __C0,
MEMBER_ID AS __C1,
NAME AS __C2
FROM "lineitem".LINEITEM LI
/* "lineitem"."member_name": MEMBER_ID = 12 */
WHERE MEMBER_ID = 12
ORDER BY 2, 3
LIMIT 25
/* index sorted */]
Time 7
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p9901.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: grouped index sort vs filter
Posted by vkulichenko <va...@gmail.com>.
Currently there is no way to manually control which index to use. Do you see
a difference in performance? I'm not sure ordering is taking advantage of
the index there in any case.
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/grouped-index-sort-vs-filter-tp9885p9886.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.