Re: limit and query planner

> On Jun 5, 2018, at 1:32 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>
>
> 2018-06-05 20:24 GMT+02:00 armand pirvu <armand(dot)pirvu(at)gmail(dot)com <mailto: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
>
>
>
>
>
>

Thank you Pavel

I put the counts to show that the number of records retrieved without limit relative to a plain select count(*) is far more than 5% and an index is just from this very reason deemed useless, aka the restriction is really non existent

— Armand