I have a table INVENTORY
in PGv9.6.11 and when a run query with WHERE _column_ IN()
, query planner using seq scan
even have index
on that _column_
CREATE TABLE INVENTORY
(
process_id bigint,
load_dttm timestamp(6) without time zone,
md5_checksum character varying(200) ,
storeid character varying(200),
inventorystatus character varying(200),
inventorytype character varying(200),
materialdescription character varying(200),
serial_nbr character varying(200) ,
activityid character varying(200) ,
channel character varying(200) ,
lastupdateddate timestamp(6) without time zone,
src_file_date timestamp(6) without time zone,
run_id character varying(40) "
)
I have created indexes in a different ways but none of them are using query planner ,
Reguler Index
:
Create index idx_test1 on INVENTORY (inventorystatus)
partial Index
:
Create index idx_test2 on INVENTORY (inventorystatus) where inventorystatus = ANY ('{Received,Returned}')
and
Create index idx_test3 on INVENTORY (inventorystatus) where inventorystatus in ('Received','Returned')
When I run explain analyze
on the user query:
EXPLAIN ANALYZE select * from INVENTORY
where INVENTORYSTATUS in ('Received','Returned')
And Query planner is:
Seq Scan on inventory as inventory (cost=0..121384.49 rows=2603713 width=157)
Filter: ((inventory.inventorystatus)::text = ANY ('{Received,Returned}'::text[]))
Not sure what I'm missing here, any suggestions?
650MB
where INVENTORYSTATUS in ('Received','Returned')