Re: reducing number of ANDs speeds up query RESOLVED

RESOLVED
--
Dear all,

Thank you for your great help and multiple advices.

I discovered the problem and I have to say that it is very stupid and strange.

Here is what happened.

From all advices I tried first partial index. The index was built and there was no change in the speed of the slow query. Which depressed me greatly. In the midst of my depression I ran VACUUM ANALYZE which took about 10 hours (the db is about 170 GB and has more than 500m rows in some tables, running on a 4 core, 8 GB RAM dedicated PostgreSQL cloud server). Towards the end of VACUUM ANALYZE I was playing with some queries and suddenly the slow query became fast! (which partially defeated the notion that one does not need ANALYZE upon CREATE INDEX) And I said "Aha!".

So I decided to try the whole thing properly from the beginning. Dropped the index, did again VACUUM ANALYZE and tried the queries, in order to measure them without and with index. Surprise! - the slow query was blazing fast. The previous indexes (not the dropped partial index) were properly used. All was fine.

Which makes me think that, as we grew the database more than 250 times in size over a 2-3 months period, relying on autovacuum (some tables grew from 200k to 50m records, other from 1m to 500m records), the autovacuum has either let us down or something has happen to the ANALYZE.

Is the autovacuum 100% reliable in relation to VACUUM ANALYZE?

Tank you and all the best,
T.