One of the Postgres table that I am working on has more 300 million rows. I am trying to select rows from that table based on a column (status_code) having possible values as 'Complete', 'In_Progress', or 'New'.
Select query where clause: status_code != 'Complete'. This query is running for more than 20 minutes. I am not sure if creating an index on this table on column 'status_code' will help or it will further degrade the performance.
Also I am thinking in this scenario if creating a partitioned table based on status_code will work better instead of an index.
I ran EXPLAIN ANALYZE for the Select query:
EXPLAIN ANALYZE select name_key from role_player_name where status_code != 'Complete'
Result: Seq Scan on role_player_name (cost=0.00..13696237.01 rows=238537921 width=34)
(actual time=0.096..348576.907 rows=239484245 loops=1)
Filter: ((status_code)::text <> 'COMPLETED'::text)
Planning Time: 0.792 ms
Execution Time: 358239.975 ms
Look forward for some suggestions.
Regards