0

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.

7
  • 1
    "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?" - Unfortunately this isn't really answerable without seeing some common example queries. But you definitely don't want to go creating 75 different indexes, and only 2 indexes is likely too little too. Please provide your table definition, and some sample queries against it.
    – J.D.
    Oct 19, 2023 at 0:02
  • This kind of querying is often referred to as "Business Analytics" and is better executed with column-oriented storage. You may want to look at the citusdata extension. Oct 19, 2023 at 10:11
  • Please share the 7 second query and it's plan. Even if it is not the only query, it's still a starting point, which is better than no starting point.
    – jjanes
    Oct 19, 2023 at 18:05
  • You need to normalize that table! Every text column with a name, for example, should be changed to an integer reference to a “person” table. Not only will that speed things up, but a name change won’t require hundreds of updates. And, of course, the index will be much smaller. (You will have to join on that table, though that is standard practice.)
    – RonJohn
    Oct 19, 2023 at 22:12
  • @RonJohn Unfortunately it's not really possible to normalize it since all the data comes from the MLS (mls.com) and their system is a dumpster fire but it's the standard across the US. Almost all their fields are text fields and can be entered manually by agents and a lot of the times there are many many agent errors and it's just a mess.
    – Exrotz
    Oct 20, 2023 at 18:41

2 Answers 2

2

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.

12
  • 2
    I agree with everything you wrote and I've given the same advice. But the response I universally get is "but they want it." The decision-makers won't accept such restrictions, and the developers/DBAs don't have the authority to enforce the restrictions. They are often junior, and don't feel secure in their employment. That's not a technology problem, and no technology can solve it. It's a problem with management and culture. Oct 19, 2023 at 7:08
  • 1
    @BillKarwin I totally agree. It is neither the fault of the customer (they don't know better) nor of the developer (the decision has already been made, and they have no say). Really, it is the fault of the application designer (or the saleswoman, who promised something impossible). The best the developer can do is to protest, and if you have a good culture and a good rapport with the customer, you can contact them and sort out the problem together. Oct 19, 2023 at 8:23
  • 2
    A key problem with this, while it would work in an ideal world, is that getting access to the right people to talk to can be impossible. There might not even be right people to talk to because no one really knows as the client hasn't fully thought through the outputs yet. They know they'll need to report on things, but can't predict exactly what things. Oct 19, 2023 at 10:26
  • 2
    @DavidSpillett Sure. But even if you get bitten once by that in the real world, and you get bitten badly enough, so that the salesmen and designers feel some of the fallout, everybody may feel inclined to do it better next time. Then it could be useful to know how you can do it better. Oct 19, 2023 at 10:47
  • 1
    Many (all?) real estate web sites let you filter on dozens of facts.
    – RonJohn
    Oct 19, 2023 at 22:14
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.

1
  • Good user Interface design goes a long way. Oct 20, 2023 at 5:31

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.