pgweb | Mirror of the code behind www.postgresql.org

Would the to_tsvector function used in the WHERE clause have to be in precisely the same format as what was used in the index?

Yes. It will ignore things like whitespace differences and extraneous parentheses. But other than that, they must be the same.

Would the following queries still make use of the index?:

No.

Note that what you have here is expression index, where the expression references multiple columns. It is not the same thing as a multi-column index.

I am surprised that your query was running anywhere. If you want to subtract 7 days from a timestamp, you should be using:

Both strategies will work. If you just have the index, you need to make sure that queries use the exact same expression in the where clause as the one in the index, otherwise the index will not be used. E.g. where ts_vector('english', title) .... will not use that index.

The computed column with the ts_vector() will require triggers to be updated automatically because Postgres does not (yet) have persisted calculated columns. So the index "only" solution might be faster when it comes to updating the able as it removes the overhead of the trigger.

To access postgres in the container, run the cli in the scope of the container

I managed to track down this fishy behavior. The problem was indeed with the preprocessing.

Shortly, I was aggregating Artist.songs from many artists in a list and later on I was popping from this list, however what I forgot is that this list is special, i.e. it's an Instrumented Collection in SQLAlchemy, which according to the docs:

"Instrumentation means that normal operations on the collection are tracked and result in changes being written to the database at flush time. "

by popping from this collection I was actually deleting Artist-Song relations, therefore the foreign key was becoming None.

My sample code looked something like that: