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
EXPLAIN SELECT ...return? (Add to question). Thanks.pg_prewarmto load things into the cache right after startup. If the queries are run frequently, it's highly likely that those indexes then stay in the cache