primary keys

classic Classic list List threaded Threaded
6 messages Options
narges saleh narges saleh
Reply | Threaded
Open this post in threaded view
|

primary keys

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
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: primary keys

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 <[hidden email]>:
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
narges saleh narges saleh
Reply | Threaded
Open this post in threaded view
|

Re: primary keys

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 <[hidden email]> 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 <[hidden email]>:
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
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: primary keys

Hello!

Please show DML for your tables as well as query plans.

Regards,
--
Ilya Kasnacheev


чт, 18 июн. 2020 г. в 16:11, narges saleh <[hidden email]>:
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 <[hidden email]> 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 <[hidden email]>:
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
narges saleh narges saleh
Reply | Threaded
Open this post in threaded view
|

Re: primary keys

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 <[hidden email]> wrote:
Hello!

Please show DML for your tables as well as query plans.

Regards,
--
Ilya Kasnacheev


чт, 18 июн. 2020 г. в 16:11, narges saleh <[hidden email]>:
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 <[hidden email]> 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 <[hidden email]>:
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

f_kblb1u7g0 (2K) Download Attachment
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: primary keys

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 <[hidden email]>:
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 <[hidden email]> wrote:
Hello!

Please show DML for your tables as well as query plans.

Regards,
--
Ilya Kasnacheev


чт, 18 июн. 2020 г. в 16:11, narges saleh <[hidden email]>:
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 <[hidden email]> 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 <[hidden email]>:
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