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
isabout 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!
(whichpartially 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
thequeries, 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
downor something has happen to the ANALYZE.
Is the autovacuum 100% reliable in relation to VACUUM ANALYZE?
Tank you and all the best,
T.