0

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?

5
  • what is the total number of rows in table INVENTORY?
    – dbilid
    May 7, 2020 at 19:53
  • 3329176 rows and table is size 650MB
    – Rj_N
    May 7, 2020 at 19:56
  • A better question would be, what's the number of rows matching where INVENTORYSTATUS in ('Received','Returned')
    – mustaccio
    May 7, 2020 at 19:56
  • @mustaccio, it's about 2597609 records.
    – Rj_N
    May 7, 2020 at 20:00
  • 1
    Not about Postgres but the principle is still applicable. I'd even go as far as declaring it a dupe.
    – mustaccio
    May 7, 2020 at 20:01

0

Browse other questions tagged or ask your own question.