Using Postgresql 9.5, wanting to know the best way to index a table so that the following query will not use a sequential scan.
SELECT *
FROM MYTABLE
WHERE (
COL1 NOT IN ('980','982','983','984','985','986','987','988','989','990','991','996','997')
OR
COL2 <> '999'
)
Just define a partial index with exactly the same where clause as used in your select statement:
CREATE INDEX ON mytable ((col1 IS NULL)) WHERE (
COL1 NOT IN ('980','982','983','984','985','986','987','988','989','990','991','996','997')
OR
COL2 <> '999'
)
The expression col1 IS NULL is just a fill-in which yields a single boolean value that is stored in the index because it's compact (just one byte per row) and it's not clear which columns would make sense to be included. If you keep querying all columns and you don't care about occupying disk space by virtually duplicating all data then including all columns in the index could make sense performance-wise.
Edit: Sequential scans are not bad per se. Generally when PostgreSQL favours a sequential scan over using an existing index there's a good reason for it. If not, the configuration could be tuned to reflect its server environment more accurately.
explain (analyze, verbose). Formatted text please, no screen shots