Hello! It seems that the index is not used. Please try creating an explicit index over these 3 fields.
Regards, -- Ilya Kasnacheev пт, 19 июн. 2020 г. в 00:38, narges saleh <[email protected]>: > Hello Ilya, > Here is the info. In this query only one table is involved. > There are about 4M records in the table and about 40,000 distinct accounts. > > thank you. > > > On Thu, Jun 18, 2020 at 9:50 AM Ilya Kasnacheev <[email protected]> > wrote: > >> Hello! >> >> Please show DML for your tables as well as query plans. >> >> Regards, >> -- >> Ilya Kasnacheev >> >> >> чт, 18 июн. 2020 г. в 16:11, narges saleh <[email protected]>: >> >>> Thanks Ilya. >>> Now I can see the complete plan, and it shows scanning the large tables >>> (but not the others). Increasing index size didn't help. >>> I only have primary keys on the caches and the fields in the primary >>> keys are the ones used in my where clause, so I am not sure >>> what's going on. >>> Currently, I am testing on one node only, so all the data should be in >>> one place. >>> >>> >>> On Thu, Jun 18, 2020 at 6:17 AM Ilya Kasnacheev < >>> [email protected]> wrote: >>> >>>> Hello! >>>> >>>> Please use !set outputFormat vertical to see complete execution plan. >>>> >>>> Index is created on primary key. There is no programmatic way to change >>>> its inline size other than specifying IGNITE_MAX_INDEX_PAYLOAD_SIZE >>>> system property or environment variable. >>>> >>>> If it is of complex type, some versions may not be able to search by >>>> its fields. >>>> >>>> Regards, >>>> -- >>>> Ilya Kasnacheev >>>> >>>> >>>> чт, 18 июн. 2020 г. в 13:13, narges saleh <[email protected]>: >>>> >>>>> Hi All, >>>>> >>>>> Shouldn't primary keys result in indexes and if so, shouldn't I be >>>>> able to see them when I list indexes? >>>>> Does inline index size applicable to primary keys too? >>>>> Additionally, when I do explain plan on a query which involves tables >>>>> with primary keys, shouldn't I see the primary key/index being used? Or >>>>> lack of a scan statement imply that an index is being used? >>>>> -------+ >>>>> | PLAN | >>>>> +--------------------------------+ >>>>> | SELECT >>>>> ID, NAME,TIMESTAMP >>>>> FROM PUBLIC.table1 >>>>> /* PU | >>>>> >>>>> sql is: select timestamp from table1 where id = 50 and name = 'John'; >>>>> //primary key is on id + name >>>>> >>>>
