1

Short version:

I'm looking for a way to give Postgres a value just for an index (as opposed to saving that value to a column and then indexing it). Similar to an index on an expression, but the expression will be computed outside of Postgres. If this is possible, how can it be done?

Long version:

I've created an index on a plpgsql function that normalizes an email address, however using it is proving to be very clunky - either I have to remember to always call the function whenever I use the index, or I have to create a view and remember to use that for reads but not for writes... On top of that, the plpgsql implementation is 3x slower than the python implementation (and 30x uglier imo), and RDS doesn't support plpythonu. So I might as well go back to go back to doing it in the application, but I don't want to go back to saving a computed column and indexing on that; I still want to have the normalized email only in the index.

The way to do this I guess would be to somehow create a fake column name, and the input for this fake column would be used for the index. If this is possible, how can it be done?

closed as unclear what you're asking by mustaccio, Erik Darling, hot2use, SqlWorldWide, Marco Oct 23 '17 at 9:21

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • or I have to create a view and remember to use that for reads but not for writes - you can use "INSTEAD OF" triggers to make views like this appear to be directly writable (though for write-heavy workloads be careful to adequately test the performance of any such solution) – David Spillett Oct 19 '17 at 9:59
  • @DavidSpillett Yeah found that in the docs after posting the question but didn't want to write so much verbose code, so I edited my question to remove that part but then the edit got rolled back lol – dvtan Oct 19 '17 at 11:10
2

Postgres supports indexes on expressions so if the value you want to index can be computed from other values in the row this will work (assuming the queries you are using a written in a way that the query planner can see the use for the index). See https://www.postgresql.org/docs/10/static/indexes-expressional.html

For more complex requirements a materialised view might be required, though these appear to need "manual" updates via REFRESH MATERIALIZED VIEW which might make them imprctical depending on your need.


From your later edit:

or I have to create a view and remember to use that for reads but not for writes

You can use "INSTEAD OF" triggers to make views like this appear to be directly writable, though of course you need to be careful of performance issues if the data is written to often.

1

I'm not sure i understood your needs but i think materialized views is what you are looking for.

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