Re: select count () 20 times slower than select count(*)

[Ilya Kasnacheev]

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 <dav_z...@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/
>