You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by David <da...@gmx.de> on 2019/01/08 14:27:45 UTC
select count () 20 times slower than select count(*)
Hi all,
I have a simple Table on a one node cluster.
Have filled it with 100mil lines of random data.
but i dont understand why a
select count(*) from people; is 20 times faster than a
select count(id) from people; where id is indexed
is there a reason for this?
below table structure and explains
#### 8.246 seconds execution time
0: jdbc:ignite:thin://127.0.0.1/> select count(*) from people;
+--------------------------------+
| COUNT(*) |
+--------------------------------+
| 99993600 |
+--------------------------------+
1 row selected (8.246 seconds)
0: jdbc:ignite:thin://127.0.0.1/> explain select count(*) from people;
+--------------------------------+
| PLAN |
+--------------------------------+
| SELECT
COUNT(*) AS __C0_0
FROM PUBLIC.PEOPLE __Z0
/* PUBLIC.PEOPLE.__SCAN_ */
/* direct lookup */ |
| SELECT
CAST(SUM(__C0_0) AS BIGINT) AS __C0_0
FROM PUBLIC.__T0
/* PUBLIC."merge_scan" */ |
+--------------------------------+
2 rows selected (0.005 seconds)
#### 136.719 seconds executin time
0: jdbc:ignite:thin://127.0.0.1/> select count(id) from people;
+--------------------------------+
| COUNT(ID) |
+--------------------------------+
| 99993600 |
+--------------------------------+
1 row selected (136.719 seconds)
explain select count(id) from people;
+--------------------------------+
| PLAN |
+--------------------------------+
| SELECT
COUNT(__Z0.ID) AS __C0_0
FROM PUBLIC.PEOPLE __Z0
/* PUBLIC."_key_PK_proxy" */ |
| SELECT
CAST(SUM(__C0_0) AS BIGINT) AS __C0_0
FROM PUBLIC.__T0
/* PUBLIC."merge_scan" */ |
+--------------------------------+
2 rows selected (0.004 seconds)
sql = "CREATE TABLE IF NOT EXISTS People " +
"(id BIGINT, " +
"first_name varchar(20), " +
"last_name varchar(20), " +
"age int, " +
"current_city_id int, " +
"born_city_id int, " +
"gender varchar(1), " +
"PRIMARY KEY(id)) " +
"WITH \"template=PARTITIONED\""
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: select count () 20 times slower than select count(*)
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Index access may be slower than full scan since it needs more lookups.
It is unfortunate that Ignite's query planner cannot figure this out, but
it is not without explanation.
Regards,
--
Ilya Kasnacheev
вт, 8 янв. 2019 г. в 17:27, David <da...@gmx.de>:
> Hi all,
>
> I have a simple Table on a one node cluster.
> Have filled it with 100mil lines of random data.
>
> but i dont understand why a
> select count(*) from people; is 20 times faster than a
> select count(id) from people; where id is indexed
>
> is there a reason for this?
> below table structure and explains
>
> #### 8.246 seconds execution time
> 0: jdbc:ignite:thin://127.0.0.1/> select count(*) from people;
> +--------------------------------+
> | COUNT(*) |
> +--------------------------------+
> | 99993600 |
> +--------------------------------+
> 1 row selected (8.246 seconds)
>
> 0: jdbc:ignite:thin://127.0.0.1/> explain select count(*) from people;
> +--------------------------------+
> | PLAN |
> +--------------------------------+
> | SELECT
> COUNT(*) AS __C0_0
> FROM PUBLIC.PEOPLE __Z0
> /* PUBLIC.PEOPLE.__SCAN_ */
> /* direct lookup */ |
> | SELECT
> CAST(SUM(__C0_0) AS BIGINT) AS __C0_0
> FROM PUBLIC.__T0
> /* PUBLIC."merge_scan" */ |
> +--------------------------------+
> 2 rows selected (0.005 seconds)
>
> #### 136.719 seconds executin time
> 0: jdbc:ignite:thin://127.0.0.1/> select count(id) from people;
> +--------------------------------+
> | COUNT(ID) |
> +--------------------------------+
> | 99993600 |
> +--------------------------------+
> 1 row selected (136.719 seconds)
>
>
> explain select count(id) from people;
> +--------------------------------+
> | PLAN |
> +--------------------------------+
> | SELECT
> COUNT(__Z0.ID) AS __C0_0
> FROM PUBLIC.PEOPLE __Z0
> /* PUBLIC."_key_PK_proxy" */ |
> | SELECT
> CAST(SUM(__C0_0) AS BIGINT) AS __C0_0
> FROM PUBLIC.__T0
> /* PUBLIC."merge_scan" */ |
> +--------------------------------+
> 2 rows selected (0.004 seconds)
>
>
> sql = "CREATE TABLE IF NOT EXISTS People " +
> "(id BIGINT, " +
> "first_name varchar(20), " +
> "last_name varchar(20), " +
> "age int, " +
> "current_city_id int, " +
> "born_city_id int, " +
> "gender varchar(1), " +
> "PRIMARY KEY(id)) " +
> "WITH \"template=PARTITIONED\""
>
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>