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