2018-06-05 20:24 GMT+02:00 armand pirvu <armand.pirvu@xxxxxxxxx>:
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
