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!


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


    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 (fkOfferorsCompanies) REFERENCES offerors_companies (pk_offerors_companies) ON DELETE NO ACTION


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:

 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
  • 1
    How long would the query take if the index wasn't used at all? Jul 20, 2021 at 9:06
  • Please post the table defintions, indexes and query in question. And the executon plan. Jul 20, 2021 at 9:26
  • 1
    Welcome to DBA.SE. Please have a look at out meta Q&A Asking query performance questions. Have a look at the meta question and edit your question to include as much information as possible. E.g. how big is/are the table/s being queried? What does an EXPLAIN SELECT ... return? (Add to question). Thanks.
    – John K. N.
    Jul 20, 2021 at 9:26
  • "by telling Postgres to keep specific indices in cache all the time" - not possible. The only thing that would be remotely similar is to use pg_prewarm to 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
    – user1822
    Jul 20, 2021 at 9:28
  • @GerardH.Pille Much longer - 30 min or even longer
    – glowfish
    Jul 20, 2021 at 11:36


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.