0

I have a table like..

CREATE TABLE msgs_msg (
  id serial primary key,
  ...
  visibility char(1), -- 'V' or 'A'
  flow_id integer NULL REFERENCES flows_flow(id) -- 100s of different possible values
)

And I'm trying to figure out the best way to index for 3 different ordered views of that table (i.e. getting pages of 25, ordered by id DESC)

  1. visibility = 'V' AND flow_id IS NULL
  2. visibility = 'V' AND flow_id IS NOT NULL
  3. visibility = 'A'

Option 1 is just make 3 perfect partial indexes, but this table already has quite a few indexes, and I keep reading that that lots of partial indexes is bad idea.

Option 2 is a single index on (visibility, flow_id, id DESC) but I'm only interested in whether flow_id is null or not, so surely that makes for inefficient index reading?

Option 3 I thought was an index on (visibility, (flow_id IS NULL), id DESC) which works fine for querying on visibility = 'V' AND flow_id IS NULL.. but doesn't seem to work for visibility = 'V' AND flow_id IS NOT NULL no matter how I tweak the query, e.g. visibility = 'V' AND (flow_id IS NULL) = FALSE

Option 4 a single index on a more complicated expression? Maybe CASE WHEN flow_id IS NULL THEN 0 ELSE 1 THEN but I'm not even sure how to query on that.

3
  • The answer will depend on 1) how nany percent of the rows have which visibility 2) how many percent of the rows have flow_id IS NULL 3) how will the queries using the views look like? Feb 24, 2023 at 17:15
  • visibility = 'V' is about 99%, flow_id IS NULL about 20%. Queries would look something like.. SELECT * FROM msgs_msg WHERE visibility = 'V' AND flow_id IS NULL ORDER BY id DESC LIMIT 25
    – Rowan
    Feb 24, 2023 at 18:26
  • A lot of partial indexes is a bad idea, but 3 is not a lot. If you have a lot of other indexes, maybe you can drop some of them. Surely not something we can advice on given the lack of info. You shouldn't compensate for having too many indexes of one kind by having too few of another kind.
    – jjanes
    Feb 24, 2023 at 19:51

1 Answer 1

1

There is no point in indexing visibility = 'V' or flow_id IS NOT NULL.

If your other query criteria are selective (they filter out almost all rows), you don't need an additional index.

If your filter criteria are not selective and you have ORDER BY id and LIMIT, then an index on id is enough.

For the other cases, I would go with two partial indexes:

CREATE INDEX ON msgs_msg (id) WHERE flow_id IS NULL;
CREATE INDEX ON msgs_msg (id) WHERE visibility = 'A';

These indexes are comparatively small, they support ORDER BY id and they can be combined with other indexes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.