An alternative to querying with multiple JOIN over the same table?

Question

I have a Postgresql 11 database. Let's say I have a table called houses. It should have hundreds of thousands of records.

CREATE TABLE houses (
  pkid serial primary key,
  address varchar(255) NOT NULL,
  rent float NOT NULL
);

Now, my houses have features I want to register in the database. As the list of possible features will be quite long (several dozens) and will evolve over time, as I don't want add a long list of columns to the table houses and change the table constantly with 'ALTER TABLE', I thought of having a separate table for these features :

CREATE TABLE house_features (
   pkid serial primary key,
   house_pkid integer NOT NULL,
   feature_name varchar(255) NOT NULL,
   feature_value varchar(255)
);
CREATE INDEX ON house_features (feature_name, feature_value);
ALTER TABLE house_features ADD CONSTRAINT features_fk FOREIGN KEY (house_pkid) REFERENCES houses (pkid) ON DELETE CASCADE;

In average, each house record will have 10-20 records in the house_features table.

So far, this seems a simple efficient model : I can add as many different features, controlling the possible values of feature_name and feature_value in the upper layers (the applicative layer and/or the GUI). I don't have to alter the database each time the application evolves and I need a new type of feature.

For the example, let's say I have the following features :

• feature_name : 'rooftype' with possible feature_value : 'flat' or 'inclined'
• feature_name : 'wallcolors' with possible feature_value : 'white', 'beige', 'blue', 'green', etc.. (15 different possible values)
• feature_name : 'has_basement' with possible feature_value : 'True' or 'False'.
• feature_name : 'number_of_doors' with possible feature_value any integer coded as a string (so '0', '1', '2', ...).
• feature_name : 'floor_surface' with possible feature_value any given float coded as a string (e.g.: '155.2')

Obviously, storing booleans, integers and floats as strings is not very efficient and this is also something I will need to take care of. I was thinking of having a separate house_features_XXX table for each XXX type (string, boolean, float, integer).

But that is not even my problem.

My problem is : how do I search for houses that have certain features ?

For the example, let's say I want to search the houses with a basement, white walls and an inclined rooftype. I could dynamically create in the application layer a query like :

SELECT sq1.* FROM 
( SELECT house_pkid FROM house_features WHERE feature_name = 'has_basement' AND feature_value = 'True' ) AS sq1
JOIN
( SELECT house_pkid FROM house_features WHERE feature_name = 'wallcolors' AND feature_value = 'white' ) AS sq2
ON sq1.house_pkid = sq2.house_pkid
JOIN
( SELECT house_pkid FROM house_features WHERE feature_name = 'rooftype' AND feature_value = 'inclined' ) AS sq3
ON sq1.house_pkid = sq3.house_pkid
;

But that seems not so efficient, especially considering that there may be several dozens of conditions on house_features.

Is there a better way to do this ?

Answer 1

You could try to aggregate the features into a JSON value, then searching for a combination of multiple features is quite easy:

select h.*, hf.features
from houses
  join (
    select house_id, jsonb_object_agg(feature_name, feature_value) as features
    from house_features
    group by house_id
  ) hf on hf.house_pkid = h.pkid 
where hf.features @> '{"rooftype": "flat", "has_basement", "true", "wallcolors": "white"}';

Performance can be improved by adding a WHERE clause to the sub-select which repeats the feature names, e.g:

where feature_name in ('rooftype', 'has_basement', 'wallcolors')

or even

where (feature_name, feature_value) in (('rooftype', 'flat') ('has_basement', 'true'), ('wallcolors', 'white'))

The outer condition is still necessary, because the inner where will include houses that don't have all the features.

This also has the advantage (in my eyes) that you only get one row with all the features, rather then one row for each feature.

---

Unless you remove, add and change features for a house very frequently, storing them as a single JSONB column on the house table (features) and getting rid of the house_features table, might be an alternative. In that case you could create an index on the column to speed up the search.

Answer 2

So, I followed the lead of using the crosstab function in Postgresql. This is where I got :

The crosstab function enables me to obtain a set of records with one record for each house and for each feature_name a column with the feature_value :

SELECT * FROM crosstab (
' SELECT house_pkid, feature_name, feature_value 
  FROM house_features
  WHERE feature_name IN (''rooftype'',''wallcolors'',''has_basement'',''number_of_doors'',''floor_surface'')
  ORDER BY house_pkid, feature_name, feature_value '
,
$$VALUES ('rooftype'), ('wallcolors'), ('has_basement'), ('number_of_doors'), ('floor_surface') $$
) 
AS ct (house_pkid int, "rooftype" varchar, "wallcolors" varchar, "has_basement" varchar, "number_of_doors" varchar, "floor_surface" varchar) ;

This query enables us to obtain a set of records like :

house_pkid | rooftype | wallcolors | has_basement | number_of_doors | floor_surface 
-------------------------------------------------------------------------------------
    232    | inclined |   beige    |   False      |         2       |       90
    234    | flat     |   white    |   False      |         1       |       70

And I can do SELECT on this set of records.

Please note two things :

• That the WHERE clause is only necessary if I have also other values for feature_name that must not appear in the final search criteria (which is my case, although I did not mention it in my original message).
• That, except from house_pkid, all other columns are returned as varchar since feature_value is varchar.

Now, if this works and wasn't too slow, in terms of optimization, I realized I still could improve things :

• First, my data does not change a lot, only 3-4 times a year, when an ETL process feeds the database. The rest of the time, the data in tables houses and house_features stays the same. So, I decided it was better to turn the query into a Posgresql MATERIALIZED VIEW. This way, I only have to rebuild the MATERIALIZED VIEW (and call the crosstab function) once each time the houses and house_features tables are reloaded through the ETL. Between two ETL, the MATERIALIZED VIEW gives access to the result without having to process the crosstab function at every call. I even can add indexes to the MATERIALIZED VIEW like to any regular table to make SELECT queries faster.
• The crosstab call returns columns of varchar type for everything, save for house_pkid, but it is possible to trans-type them so that we have more adequate and more efficient data types : instead of having the string 'True' or 'False, to have a boolean ; instead if having the string '90', to have an integer of value 90.
• The list of possible values that the column house_features.feature_name will change over time as mentioned in my initial message, but in my case, only when a new version of the applicative layer is delivered, i.e. when I also have an ETL and will rebuild the MATERIALIZED VIEW. So I coded inside my Python applicative layer (which does the ETL) a function that creates the PSQL code for the MATERIALIZED VIEW based on a list of tuples containing the names and PSQL types for each value that feature_name may take and is of one of my search criteria.

This gives :

from collections import namedtuple
hf_tuple = namedtuple('house_searchable_features', ['fieldname', 'fieldtype'])
searchablefeatures = [
    hf_tuple(fieldname='rooftype', fieldtype='varchar'),
    hf_tuple(fieldname='wallcolors', fieldtype='varchar'),
    hf_tuple(fieldname='has_basement', fieldtype='boolean'),
    hf_tuple(fieldname='number_of_doors', fieldtype='integer'),
    hf_tuple(fieldname='floor_surface', fieldtype='float'),
]

def create_searchablefeatures_query():
    """ Creates the SQL query for re-creating the MATERIALIZED VIEW. """
    query_sourcesql = 'SELECT house_pkid, feature_name, feature_value FROM house_features WHERE feature_name IN ( \n'
    query_sourcesql += ",\n".join(f" \t''{sf.fieldname}'' " for sf in searchablefeatures)
    query_sourcesql += ')\n ORDER BY house_pkid, feature_name, feature_value'

    query_categories = "$$VALUES \n"
    query_categories += ",\n".join(f"\t('{sf.fieldname}')" for sf in searchablefeatures)
    query_categories += "\n$$"

    query_output = ''
    query_output += ",\n".join(f'\t"{sf.fieldname}" varchar' for sf in searchablefeatures)

    query_transtyping = ''
    for sf in searchablefeatures:
        if sf.fieldtype == 'boolean':
            query_transtyping += f',\n\t("{sf.fieldname}" IS NOT NULL AND "{sf.fieldname}" != \'False\')  AS "{sf.fieldname}"'
        elif sf.fieldtype == 'int' or sf.fieldtype == 'float':
            query_transtyping += f',\n\t"{sf.fieldname}"::{sf.fieldtype}'
        elif sf.fieldtype == 'varchar':
            query_transtyping += f',\n\t"{sf.fieldname}"'
        else:
            raise ValueError(f"unknown PSQL data type: {sf.fieldname}, {sf.fieldtype}")

    sql_def = f"""
DROP MATERIALIZED VIEW IF EXISTS house_searchablefeatures CASCADE ;
CREATE MATERIALIZED VIEW house_searchablefeatures AS
    SELECT house_pkid {query_transtyping} FROM
    (   SELECT * FROM crosstab( '\n{query_sourcesql}',\n {query_categories} \n)
        AS ct ( house_pkid int, \n{query_output} \n) 
    ) AS b4transtyping ; """

    return sql_def

Please note that in hf_tuple, fieldtype is the Postgresql data type wanted in the MATERIALIZED VIEW, not a Python data type. Also note you might have to adjust the logic of query_transtyping depending on your database content.

That was not an easy piece and some testing will confirm it works well but it seems robust and efficient. In terms of maintenance, just updating the list searchablefeatures and running the query once every ETL seems acceptable.

The function runs with Python 3.8.

Answer 3

Especially in cases where feature count to search for is larger, in order to avoid constructing mega query statements, you may consider instead building a temporary table to hold searched for features, and do a simple INNER JOIN with, as previously noted, GROUP BY counts.

This is exactly a replacement for building a long query with SELECT ... feature IN ( feat1, feat2, feat3...) where you would concatenate features in Python.

Performance wise it would seem to me that this should be much better, although I don't have time at the moment, to test it.

This is what you do for each query where you have arbitrary number of features to search for.

For example, your user wants all houses with white walls, basement and inclined roof:

CREATE TEMPORARY TABLE search_features ( FEAT_NAME VARCHAR(255), FEAT_VALUE VARCHAR(255));

Then do (probably better a batch) insert into it of parameters to search for, via Python. This is the only thing that changes, depending on the features user selected:

INSERT INTO search_features ('has_basement','True');
INSERT INTO search_features ('wallcolors','white');
INSERT INTO search_features ('rooftype','inclined');

...

It is probably easiest to set number of total features to match from Python (in this case then FEAT_COUNT will be 3), although you could do extra SELECT COUNT(*) FROM search_features with every query.

and then run the query:

SELECT DISTINT house_pkid,count(HF.feature_name)
FROM house_features HF 
     INNER JOIN search_features SF 
     ON SF.FEAT_NAME=HF.feature_name AND SF.FEAT_VALUE=HF.feature_value
GROUP BY house_pkid
HAVING count(HF.feature_name) = %FEAT_COUNT

Bonus is that you don't have to touch anything if the list of features change.