What kind of index should I use in postgresql for a column with 3 values

Question

I have a table with 100Mil+ records and 237 fields. One of the fields is a varchar 1 field with three possible values (Y,N,I) I need to find all of the records with N. Right now I have a b-tree index built and the query below takes about 20 min to run. Is there another index I can use to get better performance?

SELECT * FROM tableone WHERE export_value='N';

Answer 1

Assuming your values are roughly equally distributed (say at least 15% of each value) and roughly equally distributed throughout the table (some physically at the beginning, some in the middle, some at the end) then no.

If you think about it you'll see why. You'll have to look up tens of millions of disk blocks in the index and then fetch them from the disk one by one. By the time you have done that, it would have been quicker to just scan the whole table and pick out the values as they match. The planner knows this and would probably not use the index at all.

However - if you only have 17 rows with "N" or they are all very recently added to the table and so physically happen to be close to each other then yes, and index can help.

If you only had a few rows with "N" you would have mentioned it, so we can ignore that one.

If however you mostly insert to this table you might find a BRIN index helpful. That can let the planner see that e.g. the first 80% of your table doesn't have any "N" blocks and so it just needs to look at the last bit.