Postgres indexes for 75 columns

Question

I have a table with about 5 million records in my Postgres database for real estate listings in my area. The table has 95 columns, ~75 which are used for filtering and searching for listings the user may be interested in.

I'm having trouble speeding up the query. A query using 5 filters that returns ~7k results takes about 7 seconds to complete.

I've searched around and the common advice is to create indexes on the most filtered columns, however in my case they seem to be used pretty much evenly, with only a few used less than others.

My question is, how should I create the indexes? Should I create indexes on each filterable column? Or 2 multi-column indexes with all the columns?

Any advice would be appreciated, thanks.

EDIT:

Listing table Schema:

"MlsId" text NOT NULL,
"StreetNumber" text NOT NULL,
"StreetDirPrefix" text NULL,
"StreetName" text NOT NULL,
"StreetSuffix" text NULL,
"Unit" text NOT NULL,
"Zip" int4 NOT NULL,
"City" text NOT NULL,
"State" text NOT NULL,
"Status" text NOT NULL,
"Beds" int2 NULL,
"BathsFull" int2 NULL,
"BathsHalf" int2 NULL,
"Sqft" int4 NULL,
"Price" money NOT NULL,
"LastUpdated" timestamp NOT NULL,
"PropertyType" text NOT NULL,
"PreviewMedia" text NULL,
"CondoBuildingId" int4 NULL,
"PIN" text NULL,
"AttachedType" text NOT NULL,
"Coords" public.geometry(point, 4326) NOT NULL,
"Data" jsonb NOT NULL,
"ActionDate" timestamptz NULL,
"ParkingPrice" int4 NOT NULL,
"ShortSale" text NULL,
"ParkingGarage" int2 NOT NULL DEFAULT 0,
"ParkingTotal" int2 NOT NULL DEFAULT 0,
"Address" public."citext" NULL,
"LaundryTypes" _text NULL,
"Basement" _text NULL,
"AssociationAmenities" _text NULL,
"UnitFloorLevel" varchar NULL,
"AssociationFee" numeric NULL DEFAULT 0,
"TaxAnnualAmount" numeric NULL DEFAULT 0,
"BasementDescript" text NULL,
"PetWeight" int2 NULL,
"Appliances" _text NULL,
"Cooling" _text NULL,
"Heat" _text NULL,
"Garage" text NULL,
"GarageSpaces" numeric NULL,
"ParkingIncluded" bool NULL,
"Waterfront" bool NULL,
"TotalUnits" int2 NULL,
"UnitNumber" text NULL,
"MasterBedroomLevel" text NULL,
"MasterBedroomBath" bool NULL,
"Ownership" text NULL,
"BuyerFinancing" _text NULL,
"NewConstruction" bool NULL,
"ExteriorType" text NULL,
"ExteriorFeatures" _text NULL,
"InteriorFeatures" _text NULL,
"FireplacesTotal" numeric NULL,
"FireplaceFeatures" _text NULL,
"FireplaceLocation" text NULL,
"WaterSource" _text NULL,
"Furnished" bool NULL,
"LotSizeAcres" numeric NULL,
"ListingContractDate" date NULL,
"PurchaseContractDate" date NULL,
"ListAgentFirstName" text NULL,
"ListAgentLastName" text NULL,
"ListAgentKey" text NULL,
"ListOfficeKey" text NULL,
"ListOfficeName" text NULL,
"BuyerAgentFirstName" text NULL,
"BuyerAgentLastName" text NULL,
"BuyerAgentKey" text NULL,
"BuyerOfficeKey" text NULL,
"BuyerOfficeName" text NULL,
"InvestoryFriendly" int2 NULL,
"OwnerOccupancy" int2 NULL,
"Stories" numeric NULL,
"PublicRemarks" text NULL,
"YearBuilt" int2 NULL,
"B78" bool NULL,
"AssociationFeeIncludes" _text NULL,
"Pets" _text NULL,
"Tags" _text NULL,
"LastCoordUpdated" date NULL,
county text NULL,

As for a typical query, like I said there really isn't a typical query. Users can filter by any combination of the columns. I guess every query will most likely include "Status" and "LastUpdated" but other than that, everyone searches differently.

EDIT 2:

So I read all the replies and messed around some. So in the end, I will require the Status and LastUpdated filter for every search. Created a multi index for status,lastupdated.

That reduced the same query ~150ms. Huge difference:

QUERY PLAN                                                                                                                                                                                                          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Sort  (cost=3004.15..3004.15 rows=1 width=201) (actual time=54.283..54.314 rows=369 loops=1)                                                                                                                        |
  Sort Key: "LastUpdated" DESC                                                                                                                                                                                      |
  Sort Method: quicksort  Memory: 123kB                                                                                                                                                                             |
  ->  Index Scan using ah_mlslisting_status_lu_q_idx on "ah_MlsListing" a  (cost=0.43..3004.14 rows=1 width=201) (actual time=2.118..53.899 rows=369 loops=1)                                                       |
        Index Cond: (("Status" = ANY ('{Active,New,PriceChange}'::text[])) AND ("LastUpdated" >= '2023-04-20 00:00:00'::timestamp without time zone))                                                               |
        Filter: (("Sqft" > 900) AND ("GarageSpaces" > '0'::numeric) AND ("City" ~~* 'Chicago'::text) AND ("PropertyType" = 'Residential Lease'::text) AND ("Price" >= (3000)::money) AND ("Price" <= (5000)::money))|
        Rows Removed by Filter: 25121                                                                                                                                                                               |
Planning Time: 0.529 ms                                                                                                                                                                                             |
Execution Time: 54.448 ms                                                                                                                                                                                           |

Thanks for all the input.

Answer 1

Creating 75 indexes is madness, and multi-column indexes won't do. Even a bloom index cannot have more than 32 columns.

This is a frequent problem, so let me answer this is some detail.

If you promise your users that they can compose arbitrary search conditions on a table like this, you have made a mistake. It sounds nice, but there is no way you can make it perform well. There is always a combination of search conditions (or perhaps a user specifies no condition at all) that either cannot use any existing index or returns way too many result rows, both of which are disastrous for performance if the table contains sufficiently many rows.

The way to overall happiness is as follows: rather than allowing arbitrary search conditions, consult with the future users of the application and figure out a few criteria, one of which must always be present in the search (enforced by the user interface). For example, you must specify at least zip or a lastupdated that is not more than a week ago. In addition to that, they can specify arbitrary search criteria. Then you create an index on zip and lastupdated, and all searches are guaranteed to perform well and return a limited number of rows.

At first, the users may complain about that restriction. But they would be much more unhappy if they had to wait forever for results (because their query is bad, or because too many bad queries run by others bog down the database machine). They would also be unhappy when confronted with a million results. If they ran a bad query by accident, they would be likely to refine the search criteria in the next attempt. Eventually, they would learn by painful experience that they have to avoid certain searches. So the restriction you impose on them (after having consulted with them to reach a consensus) actually saves them that painful learning process, and it makes the DBA's job easy to boot.

Answer 2

If laurenz's suggestion of enforcing limitations is not possible for your product, because users will kick back or other stake-holders within the client (as you are not likely talking directly to the end users) will assume that users will kick back, then an alternative might be to add those restrictions in by default but allow the user to turn them off (or extend them) as needed until they get what they want.

Basically: give the user the full flexibility that is being demanded, but guide them away from starting with a query that has no choice but to scan that whole table (and any others involved). Make it so they add in what they want in the results, instead of slowly chipping out what they don't.

You'll still end up with some queries scanning the entire table, but less of them. Perhaps warn the user when turning off one of these default filters instead of changing it (“are you sure you want records for all time, this may take minutes to return, you could instead extend the date filter just one year?”) and/or log when it happens.

In fact, log the queries anyway: this information may be useful for future optimisation work (changing the indexes present, altering those defaults so they get in the user's way as little as possible while still being useful, etc.). Watching what the users actually try to do is often more accurate than asking them what they think they will want to do.

Of course this all presumes that you have at least some input into the application design, rather than just being handed the DB and having no control beyond that as is often the case.