How to prioritize indices in Postgres cache?

The **server config**: - RAM = 16 GB - #CPUs = 6 The **Postgres config**: - There is only 1 Postgres instance. I.e. no partioning, no clustering, ... - shared_buffers = 5GB - work_mem = 10MB - maintenance_work_mem = 800MB - wal_buffers = 16MB - effective_cache_size = 10GB My users are experiencing bad user experience while querying Postgres (v13) due to bad query performance. An index exists and is used. However, there are many READs and not so many HITs while the index is used. I.e. larger parts of the index need to be loaded from disc into the cache. Immediately executing the same query again leads to expected results: The relevant parts of the index were loaded into the cache due to the first query and are now available leading to no READs at all with a performance much less than 1 sec, while the first query took approx. 120 sec. The reason why Postgres does not keep my index in cache also makes sense: There are other indices which are used much more often and thus "push out" of the cache the not so often used indices. In fact only 10% of the cache is used for indices, the remaining 90% for parts of the actual main table. There are many background jobs querying the Postgres instance which are using other indices quite frequently. The indices which are used when users interact with the system (on a web frontend via an API) are much more seldom used - which is leading to really bad user experience as described above. **Is there a way/configuration/best_practice_architecture which somehow tackles this problem by telling Postgres to keep specific indices in cache all the time?** I also have the feeling that the load on the Postgres instance might lead to the very bad performance where READs on the disc take "ages". Thanks upfront to everyone considering this question! **EDIT:** This is an examplary query. In fact there are 9 indices (all on the same table offers) all having the same issue. Table size: ~ 24,000,000 rows / 36GB Indices size: 22 indices / 31GB Table:

CREATE TABLE IF NOT EXISTS offers (
	pk_offers						serial not null,
	crawler_id						varchar not null,
	source							varchar not null,
	
	fk_objects						int null, 
	masteroffer						boolean not null default true,
	fk_offers_parent				bigint null,
	
	id_source						varchar null,
	offer_type						varchar null,
	object_type						varchar null, 
	deal_type						varchar not null, 
	
	imgs							jsonb null,
	
	geoCoordLat						double precision null,
	geoCoordLon						double precision null,
	
	offerorType						varchar null,
	offerorName						varchar null,
	offerorDetails					varchar null,
	offerorData						jsonb null,
	fkOfferorsCompanies				bigint null,
	
	street							varchar null,
	nr								varchar null,
	zip_code						varchar null,
	city							varchar null,
	country							varchar null,
	construction_year				int null,
	renovation_year					int null,
	object_condition				varchar null,
	construction_phase 				varchar null,
	heating_type					varchar null,
	interior_niveau					varchar null,
	main_energy_source				varchar null,
	object_type_spec				varchar null,
	nr_rooms						double precision null,
	nrLevelOfObject					int null,
	nrlevelofobject_text 			varchar null,
	nr_parking_space				int null,
	parking_space 					boolean null,
	sqm_living						double precision null,
	sqm_use							double precision null,
	sqm_estate						double precision null,
	nr_levels						int null,
	currency						varchar not null,
	energy_consumption				double precision null,
	erbpacht						boolean null,
	boardinghouse					boolean null,
	moebliert						boolean null,
	mehrfamilien					boolean null,
	dachgeschoss					boolean null,
	penthouse 						boolean null,
	kitchen_builtin 				boolean null,
	elevator 						boolean null,
	senior_friendly 				boolean null,
	balcony 						boolean null,
	terrace 						boolean null,
	cellar 							boolean null,
	garden 							boolean null,
	rented_out 						boolean null,
	monumental_protection 			boolean null,
	new_building					boolean null,
	nrSeatsGuestroom				int null,
	heightRooms						double precision null,
	location 						varchar null,
	
	housemoney						double precision null,
	selling_price					double precision null,
	selling_price_per_sqm_living	double precision null,
	
	minZipPricePerSqm				double precision null,
	lowQuartileZipPricePerSqm		double precision null,
	medianZipPricePerSqm			double precision null,
	highQuartileZipPricePerSqm		double precision null,
	maxZipPricePerSqm				double precision null,
	countZipPricePerSqm				int null,
	
	selling_price_parking_space		double precision null,
	selling_rent_per_month			double precision null,
	selling_rent_per_month_estimated	double precision null,
	selling_rent_per_month_per_sqm_living			double precision null,
	selling_rent_per_month_per_sqm_living_estimated	double precision null,
	grossMargin						double precision null,
	grossMarginEstimated			double precision null,
	grossMarginMerged				double precision null,
	taxRealEstateTransfer			double precision null,
	netMargin						double precision null,
	netMarginEstimated				double precision null,
	netNetMargin					double precision null,
	netNetMarginEstimated			double precision null,	
	provision						varchar null,
	provision_rel					double precision null,
	provision_abs					double precision null,
	provision_vatincl				boolean null,
	provisionParsed					boolean not null default false,
	provision_exists				boolean null,
	zwangsversteigerung				boolean null,
	bieterverfahren					boolean null,
	renovierungsbeduerftig			boolean null,
	rentDuration					varchar null,
	
	net_cold_rent_per_month			double precision null,
	net_cold_rent_per_month_per_sqm_living			double precision null,
	
	minZipRentPerSqm				double precision null,
	lowQuartileZipRentPerSqm		double precision null,
	medianZipRentPerSqm				double precision null,
	highQuartileZipRentPerSqm		double precision null,
	maxZipRentPerSqm				double precision null,
	countZipRentPerSqm				int null,
	
	priceChangeRelInLast6Months		double precision null,
	rentChangeRelInLast6Months		double precision null,
	
	net_utilities_per_month			double precision null,
	net_utilities_per_month_and_sqm double precision null,
	net_warm_rent_per_month			double precision null,
	net_parkingspace_per_month		double precision null,
	heating_costs_handling			varchar null,
	deposit							double precision null,
	deposit_text 					varchar null,
	netColdRentPerYear				double precision null,
	
	building_exists					boolean null,
	building_land					boolean null,
	exploitation					varchar null,
	construction_following			varchar null,
	suggested_use					varchar null,
	grz								double precision null,
	gfz								double precision null,
	date_available_as_of			varchar null,
	area_separable_as_of			varchar null,
	
	additional_attributes_json 		varchar null,
	count_offers_last_90_days		int null,
	population_zip_area				int null,
	zip_area_km2					double precision null,
	population_per_km2				double precision null,
	
	email_was_sent					boolean not null default false,
	html_extracted					boolean not null default false,
	htmlExtractionFailed			boolean not null default false,
	timestamp_html_extracted 		timestamptz default null,
	calculationsDone				boolean not null default false,
	offerActive						boolean not null default true,
	outlier							boolean not null default false,
	outdatedoffer					boolean not null default false,
	is_duplicate					boolean not null default false,
	modernisation_score				int null,
	
	manually_added					boolean not null default false,
	inserted_by						varchar null,
	changed_cols_vs_parent			jsonb null,
	
	timestamp_available_since 		timestamptz default current_timestamp,
	timestamp_last_active_check		timestamptz default null,
	timestamp_last_appearance		timestamptz default current_timestamp,
	timestamp_deactivated			timestamptz default null,
	
	timestamp_creation				timestamptz default current_timestamp,
	timestamp_modified				timestamptz default current_timestamp,
	
	PRIMARY KEY (pk_offers),
	FOREIGN KEY (fk_objects) REFERENCES objects (pk_objects) ON DELETE NO ACTION,
	FOREIGN KEY (fk_offers_parent) REFERENCES offers (pk_offers) ON DELETE NO ACTION, NOT YET IMPLEMENTED
	FOREIGN KEY (fkOfferorsCompanies) REFERENCES offerors_companies (pk_offerors_companies) ON DELETE NO ACTION
);

Query:

EXPLAIN (ANALYZE, BUFFERS, COSTS, BUFFERS, TIMING)
SELECT id, fk_objects 
FROM offers_valid_renames  
WHERE true 
	AND deal_type IN ('LIVING_APARTMENT_BUY') 
	AND country='DE' 
	AND offerActive=true 
order by selling_price_per_sqm_living asc limit 2000

Used index:

CREATE INDEX IF NOT EXISTS offers__identify_sortby_sellingpricepersqmliving ON offers (deal_type, country, selling_price_per_sqm_living)

Query plan:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..54777.59 rows=2000 width=16) (actual time=37.753..132250.683 rows=2000 loops=1)
   Buffers: shared hit=19364 read=26722 dirtied=12 written=39
   ->  Index Scan using offers__identify_sortby_sellingpricepersqmliving on offers  (cost=0.56..5331010.53 rows=194644 width=16) (actual time=37.750..132248.798 rows=2000 loops=1)
         Index Cond: (((deal_type)::text = 'LIVING_APARTMENT_BUY'::text) AND ((country)::text = 'DE'::text))
         Filter: offeractive
         Rows Removed by Filter: 47727
         Buffers: shared hit=19364 read=26722 dirtied=12 written=39
 Planning Time: 1.188 ms
 Execution Time: 132254.758 ms