Re: limit and query planner

2018-06-05 20:24 GMT+02:00 armand pirvu <armand(dot)pirvu(at)gmail(dot)com>:

> All
>
> Please see below
>
> explain analyze select * from sp_i2birst_reg_staging_test where evt_id =
> 'ACSF17'
> and status=0 limit 10;
> QUERY PLAN
>
> ------------------------------------------------------------
> -----------------------------------------------------------------------
> Limit (cost=0.00..1.29 rows=10 width=519) (actual time=0.110..0.439
> rows=10 loops=1)
> -> Seq Scan on sp_i2birst_reg_staging_test (cost=0.00..548.40
> rows=4239 width=519) (actual time=0.109..0.429 rows=10 loops=1)
> Filter: (((evt_id)::text = 'ACSF17'::text) AND (status = 0))
> Rows Removed by Filter: 115
> Planning time: 3.022 ms
> Execution time: 0.639 ms
> (6 rows)
>
>
>
> birstdb=# \d sp_i2birst_reg_staging_test
> Table "csischema.sp_i2birst_reg_
> staging_test"
> Column | Type |
> Modifiers
> ---------------+-----------------------------+--------------
> -----------------------------------------------------------
> action_id | bigint | not null default
> nextval('i2birst_reg_staging_action_id_seq'::regclass)
> reg_uid | integer | not null
> evt_uid | integer | not null
> evt_id | character varying(10) | not null
> operation | character varying(6) | not null
> status | smallint | not null
> category | character varying(20) | not null default
> ''::character varying
> add_date | timestamp with time zone | not null default now()
> mod_date | timestamp with time zone | not null default now()
> ingres_data | jsonb |
> thread_number | bigint | not null default 0
> start_time | timestamp without time zone |
> end_time | timestamp without time zone |
> Indexes:
> "sp_i2birst_reg_staging_test_pkey" PRIMARY KEY, btree (action_id)
> "sp_i2birst_reg_staging_test_idx" btree (status, evt_id, category)
> Check constraints:
> "sp_i2birst_reg_staging_test_status_check" CHECK (status = ANY
> (ARRAY[0, 1, 2, 3]))
>
> Even if add an index on evt_id and status same table scan
>
> But
>
> select count(*) from sp_i2birst_reg_staging_test;
> count
> -------
> 6860
>
> select count(*) from sp_i2birst_reg_staging_test where evt_id = 'ACSF17'
> and status=0 ;
> count
> -------
> 4239
>
> So I can see why the planner is choosing a table scan
>
> My question is: I suspect the limit simply limits the fethching to the
> first n-records retrieved and has no implications whatsoever on the
> planner, meaning the planner ignores it. Am I right or wrong ?
>

LIMIT is last clause and it is processed after aggregation.

probably you would select count(*) from (select * from
sp_i2birst_reg_staging_test where evt_id = 'ACSF17' LIMIT 10) s;

more you have not index on evt_id column - there is composite index, but
the chance can be low

Regards

Pavel

> Thanks
> — Armand
>
>
>
>
>
>